Alqio Alqio - 7 months ago 176
SQL Question

Google Chart API invalid JSON string on Line Chart

I'm trying to use Google Charts API to create a line chart based on a MySQL database. The database contains temperatures and timestamps.

I have getData.php to get the data and turn it into a JSON.

<?php
$servername = "xxx";
$username = "xxx";
$password = "xxx";
$dbname = "xxx";


$conn = mysqli_connect($servername, $username, $password, $dbname);

// Check connection
if (!$conn) {
die("Connection failed: " . mysqli_connect_error());
}

$qry = "SELECT * FROM temp";

$result = mysqli_query($conn,$qry);

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

array('label' => 'Time', 'type' => 'datetime'),
array('label' => 'Temperature', 'type' => 'number')
);

$rows = array();
foreach($result as $row){

$temp = array();

//$temp[] = array('v' => 'Date(' . $row['time'] . ')'); OLD

//turn timestamps into correct datetime form Date(Y,n,d,H,i,s)
$temp[] = array('v' => 'Date('.date('Y',strtotime($row['time'])).',' .
(date('n',strtotime($row['time'])) - 1).','.
date('d',strtotime($row['time'])).','.
date('H',strtotime($row['time'])).','.
date('i',strtotime($row['time'])).','.
date('s',strtotime($row['time'])).')');


$temp[] = array('v' => (float) $row['temperature']);
$rows[] = array('c' => $temp);
}

$table['rows'] = $rows;

$jsonTable = json_encode($table, true);
echo $jsonTable;
?>


The timestamps are transformed to "Date(Year, Month, Day, Hours, Minutes, Seconds, Milliseconds)" format as per Google's instructions here: https://developers.google.com/chart/interactive/docs/datesandtimes#dates-times-and-timezones

Here is my main.html. It's based on the Google's example( https://developers.google.com/chart/interactive/docs/php_example#exampleusingphphtml-file )

<html>
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script type="text/javascript">

// Load the Visualization API and the corechart package.
google.charts.load('current', {'packages':['line']});

google.charts.setOnLoadCallback(drawChart);

function drawChart() {

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

var data = new google.visualization.DataTable(jsonData); //Line 27


var options = {'title':'Temperature',
'width':720,
'height':480};

var chart = new google.charts.Line(document.getElementById('chart_div'));


//chart.draw(data, options);
//chart.draw(data, {width: 400, height: 240});
chart.draw(data, google.charts.Line.convertOptions(options));



}
</script>

</head>

<body>
<div id="chart_div"><p id="test"></p></div>
</body>
<html>


The website is blank and Chrome debugger shows this error:

Uncaught Error: Invalid JSON string:

<body>
{"cols":[{"label":"Time","type":"datetime"},{"label":"Temperature","type":"number"}],"rows":[{"c":[{"v":"Date(2016,3,14,10,36,30)"},{"v":22}]},{"c":[{"v":"Date(2016,3,14,10,37,31)"},{"v":25}]},{"c":[{"v":"Date(2016,3,14,10,37,53)"},{"v":21}]},{"c":[{"v":"Date(2016,3,15,01,23,37)"},{"v":21}]}]}
</body>

yl @ VM1981:170
Ol @ VM1981:174
Sp @ VM1981:234
drawChart @ main.html:27
google.a.b.Na @ loader.js:147
g @ loader.js:145


The main.html:27 is the var data = new google.visualization.DataTable(jsonData); line.

Here is the JSON formatted with jsonlint

{
"cols": [{
"label": "Time",
"type": "datetime"
}, {
"label": "Temperature",
"type": "number"
}],
"rows": [{
"c": [{
"v": "Date(2016,3,14,10,36,30)"
}, {
"v": 22
}]
}, {
"c": [{
"v": "Date(2016,3,14,10,37,31)"
}, {
"v": 25
}]
}, {
"c": [{
"v": "Date(2016,3,14,10,37,53)"
}, {
"v": 21
}]
}, {
"c": [{
"v": "Date(2016,3,15,01,23,37)"
}, {
"v": 21
}]
}]
}


I'm completely at loss here. The JSON string should be fine and it's validated by jsonlint.com too. Any help would be greatly appreciated.

Answer

Copying your code and returning your pasted json string in getData.php I can't reproduce your error.

Please check your ajax response in main.html:

console.log(jsonData);

If I add additional output in getData.php (for example var_dump something), I can create the invalid JSON string error. The

<body>

tag around your JSON string is suspicious though, because in the error message the malformed JSON string is printed, so the tag seems to be part of your JSON-string. Is your php code in getData.php embedded in body-tags?

In main.html you could try:

jsonData=jsonData.replace("<body>", "").replace("</body>", "");

to check, if this might be the problem.