I have studied with some program and now I need to take data from MySQL and show it with HTML/PHP. I take the data from MySQL with this mysql.php file:
<?php
$hostname = "localhost";
$database = "database";
$username = "username";
$password = "password";
$connect = mysql_connect($hostname, $username, $password)
or die('Could not connect: ' . mysql_error());
$bool = mysql_select_db($database, $connect);
if ($bool === False){
print "can't find $database";
}
$query = "SELECT * FROM `table` ORDER BY timestamp LIMIT 0 , 100";
$result = mysql_query($query) or die("SQL Error 1: " . mysql_error());
// get data and store in a json array
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
$values[] = array(
'timestamp' => $row['timestamp'],
'temperature' => $row['temperature'],
);
}
echo json_encode($values);
?>
then I try to display it to google chart:
<html>
<body>
<script type="text/javascript" src="https://www.google.com/jsapi"></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 piechart package.
google.load('visualization', '1', {'packages':['corechart']});
// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);
function drawChart() {
var jsonData = $.ajax({
url: "http://localhost:8081/xampp/testi/mysql.php",
dataType:"json",
async: false
}).responseText;
// Create our data table out of JSON data loaded from server.
var data = new google.visualization.DataTable(jsonData);
// Instantiate and draw our chart, passing in some options.
var chart = new google.visualization.PieChart(document.getElementById('chart_div'));
chart.draw(data, {width: 400, height: 240});
}
</script>
<div id="chart_div"></div>
</body>
</html>
but it just gives error:
The JSON format is somehow frong, but how?
Your JSON should be formatted like this
{
"cols": [
{"id":"","label":"Topping","pattern":"","type":"string"},
{"id":"","label":"Slices","pattern":"","type":"number"}
],
"rows": [
{"c":[{"v":"Mushrooms","f":null},{"v":3,"f":null}]},
{"c":[{"v":"Onions","f":null},{"v":1,"f":null}]},
{"c":[{"v":"Olives","f":null},{"v":1,"f":null}]},
{"c":[{"v":"Zucchini","f":null},{"v":1,"f":null}]},
{"c":[{"v":"Pepperoni","f":null},{"v":2,"f":null}]}
]
}
So when you're trying to convert your JSON to a DataTable you're not getting any returns as it isn't correctly formatted. More to read here.
Try formatting your JSON like
var jsonData = {}
jsonData.cols = [
{"label":"Date w/ time", "type":"date"},
{"label":"Temp", "type":"number"}
]
jsonData.rows = [
{"c":[{"v":new Date(2015, 11, 1, 12, 23)}, {"v":50}]},
{"c":[{"v":new Date(2015, 11, 1, 12, 24)}, {"v":75}]}
]
You have not defined DataTable columns.Try to define it as below or check DataTable Class Reference
// Create data table
var data = new google.visualization.DataTable();
data.addColumn('string', 'Timestamp');
data.addColumn('string', 'Temperature');