Solved

Changes to Google Chart PHP

Posted on 2013-01-23
12
531 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 30

Expert Comment

by:Marco Gasi
Comment Utility
The question makes sense: let me work on.... :)
0
 

Author Comment

by:TLN_CANADA
Comment Utility
Thank you very much mate!
0
 
LVL 30

Expert Comment

by:Marco Gasi
Comment Utility
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
 

Author Comment

by:TLN_CANADA
Comment Utility
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 30

Expert Comment

by:Marco Gasi
Comment Utility
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
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:TLN_CANADA
Comment Utility
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 30

Expert Comment

by:Marco Gasi
Comment Utility
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
Comment Utility
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 30

Accepted Solution

by:
Marco Gasi earned 500 total points
Comment Utility
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
Comment Utility
That's it! It worked perfectly! Thank you so much!

I just responded to your other solution now :)
0
 

Author Comment

by:TLN_CANADA
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
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 look for a specific file type in a local or remote server directory using PHP.

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now