datatable newb

shown

unread,

Jul 15, 2012, 2:41:31 AM7/15/12

to google-visua...@googlegroups.com

Nothing works:(

You can view results of php @ sunminer.co/getData.php

//connection successful...
$sth = mysql_query("SELECT * FROM testpower WHERE datetime > 0");
$rows = array();
$flag = true;

while($r = mysql_fetch_assoc($sth)) {
    $rows[] = $r;
}

$tablej = '{cols:[{label: \'date\', type: \'datetime\'}, {label: \'power\', type: \'number\'}], rows: [';

foreach($rows as $i){
	if ($flag){
		$tablej .= '{c: [{v:'.$i["datetime"].'},{v:'.$i["power"].'}]}';
		$flag = false;
		}
	else	
		$tablej .= ', {c: [{v:'.$i["datetime"].'},{v:'.$i["power"].'}]}';
}
$tablej .= ']}';
//__________________________________________________________________________________________________________
header("Content-type: application/json");
echo $tablej;
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
  <meta http-equiv="content-type" content="text/html; charset=utf-8" />
  <title>Google Visualization API Sample</title>
  <script type="text/javascript" src="http://www.google.com/jsapi"></script>
    <script type="text/javascript" src="jquery-1.6.2.min.js"></script>
  <script type="text/javascript">
    google.load('visualization', '1', {packages: ['annotatedtimeline']});
    function drawVisualization() {
      var data = new google.visualization.DataTable({cols:[{label: 'date', type: 'datetime'}, 
	  {label: 'power', type: 'number'}], rows: [{c: [{v:'2007-12-01 00:12:00'},{v:0}]}, 
	  {c: [{v:'2007-12-01 01:12:00'},{v:101}]}, {c: [{v:'2007-12-01 02:12:00'},{v:201}]}, 
	  {c: [{v:'2007-12-01 03:12:00'},{v:302}]}]});
    
      var annotatedtimeline = new google.visualization.AnnotatedTimeLine(
          document.getElementById('visualization'));
      annotatedtimeline.draw(data, {'displayAnnotations': true});
    }
    
    google.setOnLoadCallback(drawVisualization);
  </script>
</head>
<body style="font-family: Arial;border: 0 none;">
<div id="visualization" style="width: 800px; height: 400px;"></div>
</body>
</html>

asgallant

unread,

Jul 16, 2012, 3:42:23 PM7/16/12

to google-visua...@googlegroups.com

Your json is missing commas in the "rows" array. Try this instead:

$sth = mysql_query("SELECT * FROM testpower WHERE datetime > 0");
$rows = array();
$flag = true;

$table = array();

$table['cols'] = array(

    array('label' => 'date', 'type' => 'datetime'),

    array('label' => 'power', 'type' => 'number')

);

$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $temp = array();

$temp[] = array('v' => $i['datetime']); // you will probably need to transform this into the Date object format
$temp[] = array('v' => (float) $i['power']); // typecast to int, float, whatever - if you don't, it will be interpreted as a string

    $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;

$jsonTable = json_encode($table);

$jsonTable is a properly formatted json representation of a DataTable.

Message has been deleted

shown

unread,

Jul 16, 2012, 3:50:47 PM7/16/12

to google-visua...@googlegroups.com

sunminer.net/getData.php

shown

unread,

Jul 16, 2012, 3:51:46 PM7/16/12

to google-visua...@googlegroups.com

I don't see where I'm missing commas..

On Monday, July 16, 2012 12:50:47 PM UTC-7, shown wrote:

sunminer.net/getData.php

shown

unread,

Jul 16, 2012, 4:06:15 PM7/16/12

to google-visua...@googlegroups.com

Is the Date/datetime object possible to instantiate with a literal? Did you mean Date() by transform? Literal: "" or '' or ?

asgallant

unread,

Jul 16, 2012, 4:20:34 PM7/16/12

to google-visua...@googlegroups.com

The DataTable constructor expects dates to be passed as a string with this format:

"Date(year, month, day, hour, minute, second)"

note that months are zero-index in js, so you have to subtract 1.

This would probably work:

$sth = mysql_query("SELECT * FROM testpower WHERE datetime > 0");
$rows = array();
$flag = true;

$table = array();

$table['cols'] = array(

    array('label' => 'date', 'type' => 'datetime'),

    array('label' => 'power', 'type' => 'number')

);

$rows = array();
while($r = mysql_fetch_assoc($sth)) {
    $temp = array();

    $dateTimeArr = explode(' ', $i['datetime']);

    $dateArr = explode('-', $dateTimeArr[0]);

    $timeArr = explode(':', $dateTimeArr[1]);

$year = $dateArr[0];

$month = $dateArr[1] - 1; // months are zero-indexed

$day = $dateArr[2];

$hour = $timeArr[0];

$min = $timeArr[1];

$sec = $timeArr[2];

$temp[] = array('v' => "Date($year, $month, $day, $hour, $min, $sec)";
$temp[] = array('v' => (int) $i['power']);

    $rows[] = array('c' => $temp);
}

$table['rows'] = $rows;

$jsonTable = json_encode($table);

shown

unread,

Jul 16, 2012, 4:28:19 PM7/16/12

to google-visua...@googlegroups.com

Fatal error: Allowed memory size of 33554432 bytes exhausted (tried to allocate 20 bytes) in /home/sunminer/public_html/getData.php on line 18

I ran into this on json_encode(). Line 18 is the while loop. This is a server issue?

asgallant

unread,

Jul 16, 2012, 5:09:24 PM7/16/12

to google-visua...@googlegroups.com

Possibly, but not likely, unless your data set is massive.

I noticed that I mashed the code together wrong, the $i's should be $r's. That, in and of itself, shouldn't cause the script to use 32MB of RAM though.

Message has been deleted

shown

unread,

Jul 16, 2012, 5:59:57 PM7/16/12

to google-visua...@googlegroups.com

It's massive..

asgallant

unread,

Jul 16, 2012, 6:42:11 PM7/16/12

to google-visua...@googlegroups.com

Looking at the link you posted, it shows ~18000 rows of data, with the string being a tad bit above 1M characters in length, so a hair more than 2MB. That should be way under the 32MB server limit.

This is really bugging me now, because I have the strangest feeling that I ran into this issue before, many years ago, and the solution is something really simple.

shown

unread,

Jul 16, 2012, 7:37:05 PM7/16/12

to google-visua...@googlegroups.com

I got it to work by bumping the php.ini to 64MB and halving the data.

asgallant

unread,

Jul 17, 2012, 11:20:33 AM7/17/12

to google-visua...@googlegroups.com

I'm glad you got it to work, but I'd like to suggest another option to try that doesn't involve messing with php.ini. Assuming you are using PHP 5.3 or newer, you should have PDO packaged with your install. PDO is a database interface that can access many different kinds of databases (MySQL, SQLite, Oracle, etc) using a common interface, and has some built in features that are worth getting to know (especially if you plan to do a lot of PHP work with databases). Go back to the older setting in php.ini (and the larger data set, if that's feasible) and try this:

try {

$db = new PDO("mysql:dbname=$databaseName", $username, $password);

}

catch (PDOException $e) {

echo $e->getMessage();

}

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = $db->prepare("SELECT * FROM testpower WHERE datetime > 0");

$query->execute();

$results = $query->fetchAll(PDO::FETCH_ASSOC);

$flag = true;

$table = array();

$table['cols'] = array(

array('label' => 'date', 'type' => 'datetime'),

array('label' => 'power', 'type' => 'number')

);

$table['rows'] = array();

foreach ($results as $row) {

$temp = array();

$dateTimeArr = explode(' ', $row['datetime']);

$dateArr = explode('-', $dateTimeArr[0]);

$timeArr = explode(':', $dateTimeArr[1]);

$year = $dateArr[0];

$month = $dateArr[1] - 1; // months are zero-indexed

$day = $dateArr[2];

$hour = $timeArr[0];

$min = $timeArr[1];

$sec = $timeArr[2];

$temp[] = array('v' => "Date($year, $month, $day, $hour, $min, $sec)";

$temp[] = array('v' => (int) $row['power']);

$table['rows'][] = array('c' => $temp);

}

$jsonTable = json_encode($table);

See how that does for you.

shown

unread,

Jul 26, 2012, 6:16:18 PM7/26/12

to google-visua...@googlegroups.com

It does not seem to work for me. We are setting up Ruby on Rails now and may no longer have need for the PHP connection file.

Reply all

Reply to author

Forward