[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 562
  • Last Modified:

Changes to Google Chart PHP

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
TLN_CANADA
Asked:
TLN_CANADA
  • 7
  • 5
1 Solution
 
Marco GasiFreelancerCommented:
The question makes sense: let me work on.... :)
0
 
TLN_CANADAAuthor Commented:
Thank you very much mate!
0
 
Marco GasiFreelancerCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
TLN_CANADAAuthor Commented:
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
 
Marco GasiFreelancerCommented:
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
 
TLN_CANADAAuthor Commented:
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
 
TLN_CANADAAuthor Commented:
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
 
Marco GasiFreelancerCommented:
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
 
TLN_CANADAAuthor Commented:
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
 
Marco GasiFreelancerCommented:
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
 
TLN_CANADAAuthor Commented:
That's it! It worked perfectly! Thank you so much!

I just responded to your other solution now :)
0
 
TLN_CANADAAuthor Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now