Solved

Changes to Google Chart PHP

Posted on 2013-01-23
12
538 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
  • 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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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.

 

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 500 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Currency in SQL? 2 32
php56-php-mcrypt for rhel7 php56 1 55
ajax to record click 3 16
php error 27 30
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
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.

828 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