Google Chart Calendar

Raphael Saldanha

unread,

Feb 4, 2015, 7:01:32 AM2/4/15

to google-visua...@googlegroups.com

Hi everyone,

I have a php file that returns an array and I'm trying to use those data to create a Google Chart Calendar visualization.

The php file is here: http://ebib.com.br/plotsus/data/dados.php

And below is my html code. If I just paste the results from PHP inside

dataTable.addRows([ ... ]);

everything goes ok, but not happens when I use the code below. Please help!

<html>
  <head>
    <script language="javascript" type="text/javascript" 
        src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js">
    </script>
    <script type="text/javascript" src="https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1.1", {packages:["calendar"]});
      google.setOnLoadCallback(drawChart);
      
   function drawChart() {
            var jsonData = $.ajax({
                url: "dados.php",
                dataType: "json",
                async: false
            }).responseText;
        
                
       var dataTable = new google.visualization.DataTable();
       dataTable.addColumn({ type: 'date', id: 'Date' });
       dataTable.addColumn({ type: 'number', id: 'Won/Loss' });
       
       dataTable.addRows([ jsonData ]);
       
       

       var chart = new google.visualization.Calendar(document.getElementById('calendar_basic'));

       var options = {
         title: "Red Sox Attendance",
         height: 350,
       };

       chart.draw(dataTable, options);
   }
    </script>
  </head>
  <body>
    <div id="calendar_basic" style="width: 1000px; height: 350px;"></div>
  </body>
</html>

Sergey Grabkovsky

unread,

Feb 4, 2015, 10:10:44 AM2/4/15

to google-visua...@googlegroups.com

Hi Raphael,

You have an extra set of brackets in your dataTable.addRows call. You JSON data already returns an array of arrays, and putting brackets around it causes it to be an array of arrays of arrays, which is not the format that addRows takes. Simply changing your line to "dataTable.addRows(jsonData)" should fix your issues.

--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api.
For more options, visit https://groups.google.com/d/optout.

Raphael Saldanha

unread,

Feb 4, 2015, 11:15:53 AM2/4/15

to google-visua...@googlegroups.com

Hi! Really thanks for your attention. I had changed the line, but still no plot... You can check here: http://ebib.com.br/plotsus/data/index.html

--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/goOPKt0-hYw/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.

Sergey Grabkovsky

unread,

Feb 4, 2015, 11:32:38 AM2/4/15

to google-visua...@googlegroups.com

Your issue now is two-fold. The data stored in jsonData is a string, not an array of arrays, so you will need to parse it. You can use $.parseJSON(jsonData) to do that for you. However, the second issue is that that won't work, since "new Date" isn't valid JSON. You will need to use the Date String representation (https://developers.google.com/chart/interactive/docs/datesandtimes#datestring) in order to make that work. Please note that the Date String representation does not work with addRows, so you will either have to use the DataTable object literal notation or arrayToDataTable instead.

Raphael Saldanha

unread,

Feb 4, 2015, 11:35:46 AM2/4/15

to google-visua...@googlegroups.com

Just got! Using dataTable.addRows(eval(jsonData)); works. But which is the "most correct" method?

Sergey Grabkovsky

unread,

Feb 4, 2015, 11:42:45 AM2/4/15

to google-visua...@googlegroups.com

It's generally considered dangerous to use eval to parse JSON. Here's a relevant excerpt from "JavaScript The Good Parts": https://www.safaribooksonline.com/library/view/javascript-the-good/9780596517748/apes02.html

The more correct way would be to encode your Dates into the Date String representation, as I outlined in my previous post, and use either the DataTable object literal notation or arrayToDataTable to get the data into a DataTable. Personally, I prefer (and recommend) to use the arrayToDataTable function.

Assuming that you change your PHP to return Dates in the "correct" way, it would change your code to the following:

var dataArray = [

[{ type: 'date', id: 'Date' }, { type: 'number', id: 'Won/Loss' }]

]; // Set up the headers

// Add the parsed JSON data to the data array.

dataArray.push.apply(dataArray, $.parseJSON(jsonData)); // note that this will fail for large arrays, and you may need to use a for loop instead

var dataTable = google.visualization.arrayToDataTable(dataArray);

Raphael Saldanha

unread,

Feb 4, 2015, 12:07:40 PM2/4/15

to google-visua...@googlegroups.com

Really thanks! I'm trying to follow your steps but I'm failing at some point... please take a look:

PHP JSON: http://ebib.com.br/plotsus/data2/dados.php

HTML: http://ebib.com.br/plotsus/data2/

Sergey Grabkovsky

unread,

Feb 4, 2015, 12:14:52 PM2/4/15

to google-visua...@googlegroups.com

Your PHP needs to surround the Dates with quotes. Hence it being a string representation. So it needs to output data that looks like:
[[ "Date(2013, 12, 2)", 254 ],[ "Date(2013, 12, 3)", 202 ] ...

Daniel LaLiberte

unread,

Feb 4, 2015, 12:21:11 PM2/4/15

to google-visua...@googlegroups.com

One more issue is bound to come up. Your date month numbers will appear to be off by one, but month numbers are based on 0 (in JavaScript and most other languages), so you will actually want e.g. "Date(2013, 11, 2)", for December 2, 2013.

Daniel LaLiberte - 978-394-1058

dlaliberte@Google.com 5CC, Cambridge MA

daniel.laliberte@GMail.com 9 Juniper Ridge Road, Acton MA

Raphael Saldanha

unread,

Feb 4, 2015, 12:29:28 PM2/4/15

to google-visua...@googlegroups.com

Thanks Sergey and Daniel!

I will deal with the month offset next. Changed the quotes on PHP, but no plot yet. The console says: Error: Type mismatch. Value Date(2013, 12, 2) does not match type date in column index 0

Sergey Grabkovsky

unread,

Feb 4, 2015, 2:39:25 PM2/4/15

to google-visua...@googlegroups.com

As I mentioned in one of my previous responses, DataTable.addRows does not support this format. You need to remove the code that creates the DataTable via the addRows method, since you are currently redefining the dataTable variable, making it not pick up the actual working code.

Raphael Saldanha

unread,

Feb 4, 2015, 3:04:08 PM2/4/15

to google-visua...@googlegroups.com

Forgive me, I forgot to remove that part of the code. You had said that dataArray.push.apply may fail with large arrays, what can be my case in the future. Can you show me how to use loop in this case?

Sergey Grabkovsky

unread,

Feb 4, 2015, 3:07:10 PM2/4/15

to google-visua...@googlegroups.com

Sure, here you go:

var dataArray = [[{ type: 'date', id: 'Date' }, { type: 'number', id: 'Won/Loss' }]]; // Set up the header

var rawJSONData = $.parseJSON(jsonData);

for (var i = 0; i < rawJSONData.length; i++) {

dataArray.push(rawJSONData[i]);

}

var dataTable = google.visualization.arrayToDataTable(dataArray);

Raphael Saldanha

unread,

Feb 4, 2015, 4:03:31 PM2/4/15

to google-visua...@googlegroups.com

Great! Working fine. Now, the months issue...

Here are some results direct from phpMyAdmin:

2013-12-02	138
2013-12-03	105
2013-12-04	115
2013-12-05	119

Looking the plot (http://ebib.com.br/plotsus/data2/), seems to me that the months are offset plus one, correct? Googling it, Javascript has 0-11 months index, not 1-12. How can I deal with it? Some answers at Stackoverflow suggests to change the date format, using / as separator, but I'm not sure how to do it (http://stackoverflow.com/questions/1208519/javascript-date-objects-month-index-begins-with-0)

Below is my PHP code:

<?php
    $dbhost="localhost";
    $dblogin="dblogin";
    $dbpwd="psw";
    $dbname="dbname";
       
    $db =  mysql_connect($dbhost,$dblogin,$dbpwd);
    mysql_select_db($dbname);    
	mysql_query('SET CHARACTER SET utf8');    
	$SQLString = "SELECT DT_INTER AS 'Data', COUNT(DT_INTER) AS 'Internações' FROM transito WHERE DT_INTER > 20131201 GROUP BY DT_INTER";
    $result = mysql_query($SQLString);    
    $num = mysql_num_rows($result);   
    $output = Array();
    for ($i=0; $i<($num); $i++)
    {
		$dateStr = strtotime(mysql_result($result, $i, "Data"));
		$value = mysql_result($result, $i, "Internações");
		$year = intval(date('Y', $dateStr));
		$month = intval(date('m', $dateStr));
		$day = intval(date('d', $dateStr));
        #$par[$i] = array(" new Date(" . date('Y', $dateStr) . ", " . date('m', $dateStr) . ", " . date('d', $dateStr) . ")",
		#$par[$i] = array(mysql_result($result, $i-1, "Data"),
		#	(int) mysql_result($result, $i, "Internações"));
		$output[] = "[ \"Date($year, $month, $day)\", $value ]";
		
    }
   
	#$js = json_encode($par);
	#echo str_replace(array('\'', '"'), '', $js); 
	echo "[" . implode(',', $output) . "]";
    mysql_close($db);
?>

Reply all

Reply to author

Forward