Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Changes to Google Chart PHP

Posted on 2013-01-23
12
Medium Priority
?
558 Views
Last Modified: 2013-01-25
I have a Google chart on a page and would like to make the following changes to it:

If there are two entries in the database for the same day, then add them together so that they are displayed on the chart as one single entry.

If there are more than 12 entries in the database then display the results in months instead rather than each particular date. A new function would add together all of the results of the individual results for a month and then take the average of them and have it displayed on the chart. So the x axis would look like this (Oct 12, Nov 12, Dec 12, Jan 13 etc).

Thank you very much for your assistance,

D
0
Comment
Question by:TLN_CANADA
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38813282
The question makes sense: let me work on.... :)
0
 

Author Comment

by:TLN_CANADA
ID: 38813285
Thank you very much mate!
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38813733
Here my solution: I don't like so mutch teh function groupByMonth() but I can't find anything better now :-)

<?php

function groupPerMonth(&$array, $key) {
  global $monthData;
  $month = 0;
  $dateEls = explode('-', $array[0]);
  switch ($dateEls[1]) {
    case 1:
      if ($month != $dateEls[1]) {
        $month = 'Jan. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 2:
      if ($month != $dateEls[1]) {
        $month = 'Feb. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 3:
      if ($month != $dateEls[1]) {
        $month = 'Mar. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 4:
      if ($month != $dateEls[1]) {
        $month = 'Apr. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 5:
      if ($month != $dateEls[1]) {
        $month = 'May ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 6:
      if ($month != $dateEls[1]) {
        $month = 'Jun. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 7:
      if ($month != $dateEls[1]) {
        $month = 'Jul. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 8:
      if ($month != $dateEls[1]) {
        $month = 'Aug. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 9:
      if ($month != $dateEls[1]) {
        $month = 'Sep. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 10:
      if ($month != $dateEls[1]) {
        $month = 'Oct. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 11:
      if ($month != $dateEls[1]) {
        $month = 'Nov. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 12:
      if ($month != $dateEls[1]) {
        $month = 'Dec. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
  }
}

$dbcnx = @mysql_connect($host, $user, $pwd);
if (!$dbcnx) {
  exit('Error connecting to the site database.');
}

if (!@mysql_select_db($dbname, $dbcnx)) {
  exit('Error opening the site database ' . $dbname);
}

$result = array();

$query = mysql_query("SELECT exercise_timestamp, exercise_time FROM exercise_table");
while ($resultCount[] = mysql_fetch_assoc($query)) {}
$numberOfRecords = count($resultCount);
if ($numberOfRecords > 12){
  $query = mysql_query("SELECT exercise_timestamp, SUM(exercise_time) AS total FROM exercise_table GROUP BY 
    EXTRACT(YEAR FROM exercise_timestamp), EXTRACT(MONTH FROM exercise_timestamp)");
  while ($result[] = mysql_fetch_assoc($query)) {}
  foreach ($result as $r) {
    if (!empty($r) && $r != '') {
      $firstDate = explode(' ', $r["exercise_timestamp"]);
      $result2[] = array($firstDate[0], (integer) $r["total"]);
    }
  }
  $monthData = array();
  array_walk($result2, 'groupPerMonth');
  $elCount = 0;
  $actualMonth = 'Jan. 2012';
  $ex_time = 0;
  foreach ($monthData as $v) {
    if ($v[0] != $actualMonth) {
      $actualMonth = $v[0];
      $elCount = 0;
      $ex_time = 0;
      $elCount++;
      $ex_time = + $ex_time + $v[1];
      $tot[$actualMonth] = $ex_time;
    } else {
      $elCount++;
      $ex_time = + $ex_time + $v[1];
      $tot[$actualMonth] = $ex_time;
    }
  }


  foreach ($tot as $k => $v) {
    $tot2[] = array($k, $v);
  }
  array_unshift($tot2, array("Months", "Exercising Time (Mins)"));
  $finalArray = $tot2;
}else{
  foreach ($result as $r) {
    if (!empty($r) && $r != '') {
      $firstDate = explode(' ', $r["exercise_timestamp"]);
      $result2[] = array($firstDate[0], (integer) $r["exercise_time"]);
    }
  }
  array_unshift($result2, array("Months", "Exercising Time (Mins)"));
  $finalArray = $result2;
}
$data = json_encode($finalArray);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:fb="http://ogp.me/ns/fb#">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages: ["corechart"]});
      google.setOnLoadCallback(drawChart);
      var PhpArray = <?php echo $data; ?>;
      for (var i = 0; i < PhpArray.length; i++) {
        document.write("<b>PhpArray[" + i + "] is </b>=>" + PhpArray[i] + "<br>");
      }
      function drawChart() {
        var data = google.visualization.arrayToDataTable(<?php echo $data; ?>);
        var options = {
          title: 'Meditation and Exercise Times'
        };
        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body onload="drawChart()">
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>

Open in new window

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:TLN_CANADA
ID: 38816357
Thank you so much!

Here is the error it is giving

Warning: mysql_fetch_assoc() expects parameter 1 to be resource, boolean given - chart.php (127

And here is the current code I have on the page with my connection to the db etc

<?php
// For Month change part 

function groupPerMonth(&$array, $key) {
  global $monthData;
  $month = 0;
  $dateEls = explode('-', $array[0]);
  switch ($dateEls[1]) {
    case 1:
      if ($month != $dateEls[1]) {
        $month = 'Jan. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 2:
      if ($month != $dateEls[1]) {
        $month = 'Feb. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 3:
      if ($month != $dateEls[1]) {
        $month = 'Mar. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 4:
      if ($month != $dateEls[1]) {
        $month = 'Apr. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 5:
      if ($month != $dateEls[1]) {
        $month = 'May ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 6:
      if ($month != $dateEls[1]) {
        $month = 'Jun. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 7:
      if ($month != $dateEls[1]) {
        $month = 'Jul. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 8:
      if ($month != $dateEls[1]) {
        $month = 'Aug. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 9:
      if ($month != $dateEls[1]) {
        $month = 'Sep. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 10:
      if ($month != $dateEls[1]) {
        $month = 'Oct. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 11:
      if ($month != $dateEls[1]) {
        $month = 'Nov. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
    case 12:
      if ($month != $dateEls[1]) {
        $month = 'Dec. ' . $dateEls[0];
        $monthData[] = array($month, $array[1]);
      }
      break;
  }
}





	/**
	* Key to include phpFox
	*
	*/
	define('PHPFOX', true);

	/**
	* Directory Seperator
	*
	*/
	define('PHPFOX_DS', DIRECTORY_SEPARATOR);

	/**
	* phpFox Root Directory
	*
	*/
	define('PHPFOX_DIR', dirname(__FILE__) . PHPFOX_DS);

	define('PHPFOX_START_TIME', array_sum(explode(' ', microtime())));

	// Require phpFox Init
	require(PHPFOX_DIR . 'include' . PHPFOX_DS . 'init.inc.php');
	
	mysql_connect('localhost','foxdbmain','xxx!') or die ( mysql_error() );
	mysql_select_db("foxdbmain") or die ( mysql_error() );
	
	date_default_timezone_set('America/Vancouver');
	
	//Chart array part 

$username = Phpfox::getUserBy('full_name'); 
//echo $username;
	
$username=mysql_real_escape_string($username);


$result = array();

$query = mysql_query("SELECT exercise_timestamp, exercise_time FROM exercise_table");
while ($resultCount[] = mysql_fetch_assoc($query)) {}
$numberOfRecords = count($resultCount);
if ($numberOfRecords > 12){
  $query = mysql_query("SELECT exercise_timestamp, SUM(exercise_time) AS total FROM exercise_table GROUP BY 
    EXTRACT(YEAR FROM exercise_timestamp), EXTRACT(MONTH FROM exercise_timestamp)");
  while ($result[] = mysql_fetch_assoc($query)) {}
  foreach ($result as $r) {
    if (!empty($r) && $r != '') {
      $firstDate = explode(' ', $r["exercise_timestamp"]);
      $result2[] = array($firstDate[0], (integer) $r["total"]);
    }
  }
  $monthData = array();
  array_walk($result2, 'groupPerMonth');
  $elCount = 0;
  $actualMonth = 'Jan. 2012';
  $ex_time = 0;
  foreach ($monthData as $v) {
    if ($v[0] != $actualMonth) {
      $actualMonth = $v[0];
      $elCount = 0;
      $ex_time = 0;
      $elCount++;
      $ex_time = + $ex_time + $v[1];
      $tot[$actualMonth] = $ex_time;
    } else {
      $elCount++;
      $ex_time = + $ex_time + $v[1];
      $tot[$actualMonth] = $ex_time;
    }
  }


  foreach ($tot as $k => $v) {
    $tot2[] = array($k, $v);
  }
  array_unshift($tot2, array("Months", "Exercising Time (Mins)"));
  $finalArray = $tot2;
}else{
  foreach ($result as $r) {
    if (!empty($r) && $r != '') {
      $firstDate = explode(' ', $r["exercise_timestamp"]);
      $result2[] = array($firstDate[0], (integer) $r["exercise_time"]);
    }
  }
  array_unshift($result2, array("Months", "Exercising Time (Mins)"));
  $finalArray = $result2;
}
$data = json_encode($finalArray);
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:fb="http://ogp.me/ns/fb#">
  <head>
    <meta http-equiv="content-type" content="text/html; charset=iso-8859-1" />
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages: ["corechart"]});
      google.setOnLoadCallback(drawChart);
      var PhpArray = <?php echo $data; ?>;
      for (var i = 0; i < PhpArray.length; i++) {
        document.write("<b>PhpArray[" + i + "] is </b>=>" + PhpArray[i] + "<br>");
      }
      function drawChart() {
        var data = google.visualization.arrayToDataTable(<?php echo $data; ?>);
        var options = {
          title: 'Meditation and Exercise Times'
        };
        var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      }
    </script>
  </head>
  <body onload="drawChart()">
    <div id="chart_div" style="width: 900px; height: 500px;"></div>
  </body>
</html>

Open in new window


Perhaps I setup something incorrectly?
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38817453
In your database the table is named exercising_table in mine is named exercise_table: please, when you copy and paste be sure all names are what you actual use in your environment. I often use testing names only to check the working flow of the code :-)

Cheers
0
 

Author Comment

by:TLN_CANADA
ID: 38817557
Sorry about that Marco! Thank you it's working now!

One small thing with it, rather than displaying the total per month (as it is now) could we add a part to it so that it shows the daily average per month. So it there was 2000 minutes in January, there are 31 days so the average would be 65 minutes per day (rounded to no decimal places) . It would be helpful for people to see their daily average so this is why I ask for it like so :)

Thank you very much for your incredible help,

Where are you from by the way? You mentioned Obama but I have the feeling you're not American :)

Derek
0
 

Author Comment

by:TLN_CANADA
ID: 38817564
I'm not sure if you'd have some time to take a look at something, but I urgently need to get this part working so I wondered if you could take a look at this question as noone has responded to it for some time. Thank you!

http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28008011.html
0
 
LVL 31

Expert Comment

by:Marco Gasi
ID: 38817595
I'm italian, Derek. And something is telling me you're canadian, uh? What time is it now for you? Here is 7:35 AM.

Let me work on: I understood you wished the daily average, but then I forgot it!

Cheers
0
 

Author Comment

by:TLN_CANADA
ID: 38817625
Fastastico!!! I live with an Italian guy here. Yes, I'm in Canada but originally from Ireland. :)

Thank you so much! The chart looks so great!
0
 
LVL 31

Accepted Solution

by:
Marco Gasi earned 2000 total points
ID: 38817683
Lol, an italian guy lives with you? The worl is really small - and we italians are wherever!

About your question, the simplest way is to use MySql functions ROUND() and AVG():

$query = mysql_query("SELECT exercise_timestamp, ROUND(AVG(exercise_time)) AS total FROM exercising_table GROUP BY 
    EXTRACT(YEAR FROM exercise_timestamp), EXTRACT(MONTH FROM exercise_timestamp)");

Open in new window


This will give you the rounded average value without more complex Php operations.

Cheers

PS: I've posted a solution to the question you posted a link to: see if it makes sense for you
0
 

Author Comment

by:TLN_CANADA
ID: 38817706
That's it! It worked perfectly! Thank you so much!

I just responded to your other solution now :)
0
 

Author Comment

by:TLN_CANADA
ID: 38817803
It's late here but I'm almost finished a website I've been working on for a while and need to get it done soon :)

If you have some time, here is a cron script I've been working on that I could really do with your help in it putting fully together. I have most of it done but my PHP syntax skills are not very high yet so I don't know the final touches :) Here it is:

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_28008334.html

Thanks so much and no worries at all if you're too busy :)
0

Featured Post

Enroll in September's Course of the Month

This month’s featured course covers 16 hours of training in installation, management, and deployment of VMware vSphere virtualization environments. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
This article discusses four methods for overlaying images in a container on a web page
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question