Solved

Changes to Google Chart PHP

Posted on 2013-01-23
12
546 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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: 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.

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 how to create an extensible mechanism for linked drop downs.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

635 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