I'm going through a bit of a learning process, in order to create a small database backed reporting system for my company.
The intent is to draw a multi line chart using Google Charts, based on a mysql database.
I've managed to get the data to echo from the mysql database, but it's not generating the chart. All I get is the echo, and a blank space where the chart should be. The echo is shown for debugging purposes.
Here is the code :
<?php include 'confile.php';
$qry = "SELECT time,p1,p2,p3,p4 from $db WHERE date = '2016-03-02' ORDER BY time ASC";
$result = $conn->query($qry);
if($result === FALSE) {
echo mysql_errno($result) .": ". mysql_error($result) ."/n";
die(mysql_error());
}
$rows = array();
$table = array();
$table['cols'] = array(
array('label' => 'Time', 'type' => 'datetime'),
array('label' => 'Probe 1', 'type' => 'number'),
array('label' => 'Probe 2', 'type' => 'number'),
array('label' => 'Probe 3', 'type' => 'number'),
array('label' => 'Probe 4', 'type' => 'number')
);
while($r = mysqli_fetch_assoc($result)) {
$temp = array();
$temp[] = array($r['time']);
$temp[] = array($r['p1']);
$temp[] = array($r['p2']);
$temp[] = array($r['p3']);
$temp[] = array($r['p4']);
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;
?>
<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable(<?$jsonTable?>);
var options = {
title: 'Recorded Temperatures',
legend: {position: 'bottom' },
width: 800,
height: 600
};
var chart = new google.visualization.Table(document.getElementById('curve_chart'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="curve_chart" style="width: 900px; height: 500px"></div>
</body>
</html>
And this is the 'echo' output
{"cols":[{"label":"Time","type":"datetime"},{"label":"Probe 1","type":"number"},{"label":"Probe 2","type":"number"},{"label":"Probe 3","type":"number"},{"label":"Probe 4","type":"number"}],"rows":[{"c":[["03:02:07"],["270.26"],["298.40"],["111.54"],["228.06"]]},{"c":[["03:28:42"],["273.23"],["190.43"],["245.69"],["283.21"]]},{"c":[["07:26:04"],["144.33"],["217.26"],["206.53"],["167.68"]]},{"c":[["12:13:20"],["153.15"],["277.23"],["167.20"],["240.88"]]}]}
This is test data, using a test query on the db. Once I understand the formatting to render the chart, it will be setup to allow the user to select which date to view, etc.
This was the closest existing question I can find, but doesn't seem to answer the question.
Not able to generate a Google Chart using MySQL table data as the data source
Following the answer of @MickMackusa, I managed to hack this together to get it to work, by ensuring the mysql/php array was output in a manner acceptable to Google Charts.
Thanks to @MickMacUSA for his assistance.
The final, working code, is below.
<?php include 'confile.php';
$qry = "SELECT time,p1,p2,p3,p4 from $db WHERE date = '2016-04-16' ORDER BY time ASC";
$result = $conn->query($qry);
if($result === FALSE) {
echo mysqli_errno($result) .": ". mysqli_error($result) ."/n";
die(mysqli_error());
}
$i = 0; //iteration counter - start at 0
$totalRows = mysqli_num_rows($result); // we need this to know when to change the output
$targetRows = $totalRows - 1; //row indies start from 0, not 1.
foreach ($result as $row){
$comTime = str_replace(":",",",$row['time']); // for each row, remove the : and put , in its place
if ($targetRows == $i) { // if the index is the same value as the target (ie, it's the last row)...
$temp = "[[".$comTime."],".($row['p1']).",".($row['p2']).",".($row['p3']).",".($row['p4'])."]". PHP_EOL;
} else {
$temp = "[[".$comTime."],".($row['p1']).",".($row['p2']).",".($row['p3']).",".($row['p4'])."],". PHP_EOL;
}
$i = $i + 1;
$rows[] = $temp;
}
$table = $rows;
$data = implode($table); //format the table as a single string, with line returns
//echo $i;
//echo $data;
?>
<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
</head>
<body>
<div id="chart" style="width: 900px; height: 500px"></div>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart(){
var data = new google.visualization.DataTable();
data.addColumn('timeofday','Time');
data.addColumn('number','Probe 1');
data.addColumn('number','Probe 2');
data.addColumn('number','Probe 3');
data.addColumn('number','Probe 4');
data.addRows([
<?php echo $data; ?> //dump the result into here, as it's correctly formatted
]);
var options = {
title: 'Recorded Temperatures',
legend: { position: 'bottom' },
width: 900,
height: 500,
hAxis: { format: 'hh:mm:ss' }
};
var chart = new google.visualization.LineChart(document.getElementById('chart'));
chart.draw(data, options);
}
</script>
</body>
</html>
