MySQL to JSON results in empty Google visualisation chart

Question

I'm using the following code to create a JSON string from data extracted from a database:

//Build table
$table = array();
$table['cols'] = array(
    array('label'=>'DateTime', 'type'=>'datetime'),
    array('label'=>'Temperature','type'=>'number')
);

while($row=mysql_fetch_assoc($result)){
    array_push($table,$row);
}
echo json_encode($table);

The 'validated' JSON output is:

{
"0": {
    "datetime": "2015-08-21 16:32:00",
    "temp": "19.062"
},
"1": {
    "datetime": "2015-08-21 16:40:00",
    "temp": "19.062"
},
"2": {
    "datetime": "2015-08-21 16:47:00",
    "temp": "19.062"
},
"3": {
    "datetime": "2015-08-21 17:00:00",
    "temp": "19.062"
},
"4": {
    "datetime": "2015-08-21 18:00:00",
    "temp": "19.062"
},
"5": {
    "datetime": "2015-08-21 18:26:00",
    "temp": "19"
},
"6": {
    "datetime": "2015-08-21 19:00:00",
    "temp": "19.062"
},
"7": {
    "datetime": "2015-08-21 20:00:00",
    "temp": "19"
},
"8": {
    "datetime": "2015-08-21 21:00:00",
    "temp": "19"
},
"9": {
    "datetime": "2015-08-21 22:00:00",
    "temp": "18.937"
},
"10": {
    "datetime": "2015-08-21 23:00:00",
    "temp": "18.875"
},
"11": {
    "datetime": "2015-08-22 00:00:00",
    "temp": "18.875"
},
"12": {
    "datetime": "2015-08-22 01:00:00",
    "temp": "18.812"
},
"13": {
    "datetime": "2015-08-22 02:00:00",
    "temp": "18.812"
},
"14": {
    "datetime": "2015-08-22 03:00:00",
    "temp": "18.75"
},
"15": {
    "datetime": "2015-08-22 04:00:00",
    "temp": "18.687"
},
"16": {
    "datetime": "2015-08-22 05:00:00",
    "temp": "18.687"
},
"17": {
    "datetime": "2015-08-22 06:00:00",
    "temp": "18.687"
},
"18": {
    "datetime": "2015-08-22 07:00:00",
    "temp": "18.625"
},
"19": {
    "datetime": "2015-08-22 08:00:00",
    "temp": "18.625"
},
"20": {
    "datetime": "2015-08-22 09:00:00",
    "temp": "18.625"
},
"21": {
    "datetime": "2015-08-22 10:00:00",
    "temp": "18.625"
},
"22": {
    "datetime": "2015-08-22 11:00:00",
    "temp": "18.625"
},
"23": {
    "datetime": "2015-08-22 12:00:00",
    "temp": "18.625"
},
"24": {
    "datetime": "2015-08-22 13:00:00",
    "temp": "18.625"
},
"25": {
    "datetime": "2015-08-22 14:00:00",
    "temp": "18.625"
},
"26": {
    "datetime": "2015-08-22 15:00:00",
    "temp": "18.625"
},
"27": {
    "datetime": "2015-08-22 16:00:00",
    "temp": "18.625"
},
"cols": [
    {
        "label": "DateTime",
        "type": "datetime"
    },
    {
        "label": "Temperature",
        "type": "number"
    }
]
}

When I try to plot this data in a Google Visualisation LineChart, the chart is empty. I'm using the following for the page on which the chart should be plotted:

function drawChart() {
    var jsonData = $.ajax({
    url: "data.php",
    dataType:"json",
    async: false
    }).responseText;

    // Create the data table.
    var data = new google.visualization.DataTable(jsonData);

The page shows the chart but there is no data plotted.

A typical data structure for the Google Visualisation API would be:

{
  "cols": [
        {"id":"","label":"DateTime","pattern":"","type":"datetime"},
        {"id":"","label":"Temperature","pattern":"","type":"number"}
      ],
  "rows": [
        {"c":[{"v":"2015-08-18 13:00:00","f":null},{"v":21,"f":null}]},-
        {"c":[{"v":"2015-08-18 14:00:00","f":null},{"v":20,"f":null}]},
        {"c":[{"v":"2015-08-18 15:00:00","f":null},{"v":20.2,"f":null}]},
        {"c":[{"v":"2015-08-18 16:00:00","f":null},{"v":20.3,"f":null}]},
        {"c":[{"v":"2015-08-18 17:00:00","f":null},{"v":20.5,"f":null}]}
      ]
}

This is my first time using these methods so I'm probably missing something really obvious. Any help would be appreciated though.

What do you want the json structure to look like, please show a small sample, edit your question to show this, dont add a comment — RiggsFolly
– RiggsFolly, Commented Aug 22, 2015 at 16:09

Dr.Molle · Accepted Answer · 2015-08-22 19:07:38Z

A datetime-value can't be provided via a string like e.g. a mysql-timestamp, the API requires a JS-Date-object.

Such an object may not be transported via JSON, but the API supports a particular string-format(see: https://developers.google.com/chart/interactive/docs/datesandtimes#dates-and-times-using-the-date-string-representation)

e.g. the Mysql-Date:

 2015-08-21 19:00:00

must become the string

Date(2015,7,21,19,0,0,0)

(note that JS starts counting the months with 0, so e.g. August is 7 not 8)

You may build the string directly in your SELECT-statement.

Other issues:

you should use the mysqli-functions instead of mysql(they are deprecated/outdated)
you should not run $.ajax synchronously
you didn't populate a rows-array with the columns

Try it like this:

data.php

<?php
ob_start();
header('Content-Type:application/json');

//use your custom data
$dbhost   ='localhost';
$dbuser   ='username';
$dbpass   ='password';
$dbname   ='db-name';
$dbtable  ='table-name';


//db-connection
$mysqli = new mysqli($dbhost,$dbuser,$dbpass,$dbname);


if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

//prepare the datatable
$table = array('cols'=>array(
                              array('label' => 'DateTime', 
                                    'type'  => 'datetime'),
                              array('label' => 'Temperature',
                                    'type'  => 'number')),
                'rows'=>array()
);


//build the query
$sql="SELECT 
              CONCAT(
                     'Date(',
                     YEAR(datetime),
                     ',',
                     Month(datetime)-1,
                     DATE_FORMAT(datetime,',%e,%k,%i,'),
                     '0)'
                    ) 
                        as `datetime`,
                            `temp` 
              FROM `{$dbtable}`";

//run the query  
if ($result = $mysqli->query($sql)) {
    while ($row = $result->fetch_assoc()) {

        //populate the rows-array
        $table['rows'][]=array('c'=>array(
                                            array('v'=>$row['datetime']),
                                            array('v'=>$row['temp'])
                                         )
                              );
    }
    $result->close();
}
ob_end_clean();
die(json_encode($table));
?>

HTML-File:

<body>
<div id="chart_div" style="height:300px;"></div>

<script src="http://code.jquery.com/jquery-latest.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<script  type="text/javascript">
/*<![CDATA[*/
google.load('visualization', '1', {packages: ['corechart']});
google.setOnLoadCallback(drawChart);

function drawChart() {
   $.getJSON('data.php',function(data){

      var data = new google.visualization.DataTable(data);

      var chart = new google.visualization
                     .LineChart(document.getElementById('chart_div'));

      chart.draw(data, {});
   });   
}
/*]]>*/
</script>
</body>

Molle. Thank you so much. Not only does this work perfectly, but I've learned a huge amount. You've given me some really good pointers to address the things I didn't that I didn't know! Thanks again.

Collectives™ on Stack Overflow

MySQL to JSON results in empty Google visualisation chart

1 Answer 1

data.php

HTML-File:

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

data.php

HTML-File:

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related