PHP - Converting Data for Google Charts
I find it annoying that for a line graph, Google wants the data in this format (javascript):
var data = google.visualization.arrayToDataTable([
['Year', 'Sales', 'Expenses'],
['2004', 1000, 400],
['2005', 1170, 460],
['2006', 660, 1120],
['2007', 1030, 540]
]);
In PHP form this is equal to the following (before we run JSON encoding):
$data = array(
array('Year', 'Sales','Expenses'),
array(2004, 1000, 400),
array(2005, 800, 300),
array(2006, 660, 1120)
);
If you're like me, then you prefer to keep your data in this form:
$data = array(
array(
'Year' => 2004,
'Sales' => 1000,
'Expenses' => 400,
),
array(
'Year' => 2005,
'Sales' => 1170,
'Expenses' => 460,
),
array(
'Year' => 2006,
'Sales' => 660,
'Expenses' => 1120,
)
);
This latter form is also what you would get if you were to use a while loop to pump your $mysqli_result->fetch_assoc() rows into an array.
Workaround
Below is a conversion function you can run to transform the latter form into the form that Google charts expects your data:
function convertDataToChartForm($data)
{
$newData = array();
$firstLine = true;
foreach ($data as $dataRow)
{
if ($firstLine)
{
$newData[] = array_keys($dataRow);
$firstLine = false;
}
$newData[] = array_values($dataRow);
}
return $newData;
}
Example Usage
<?php
# Grab the data from the database
$query = "SELECT `year`, `sales`, `expenses` FROM `my_awesome_table`";
$mysqli_result = $mysqli->query($query);
$data = array();
while (($row = $mysqli_result->fetch_assoc()) !== null)
{
$data[] = $row;
}
# our converstion function given above.
function convertDataToChartForm($data)
{
$newData = array();
$firstLine = true;
foreach ($data as $dataRow)
{
if ($firstLine)
{
$newData[] = array_keys($dataRow);
$firstLine = false;
}
$newData[] = array_values($dataRow);
}
return $newData;
}
?>
<html>
<head>
<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
<script type="text/javascript">
google.charts.load('current', {'packages':['corechart']});
google.charts.setOnLoadCallback(drawChart);
function drawChart() {
var data = google.visualization.arrayToDataTable((<?= json_encode(convertDataToChartForm($data)); ?>));
var options = {
title: 'Company Performance',
curveType: 'function',
legend: { position: 'bottom' }
};
var chart = new google.visualization.LineChart(document.getElementById('curve_chart'));
chart.draw(data, options);
}
</script>
</head>
<body>
<div id="curve_chart" style="width: 900px; height: 500px"></div>
</body>
</html>
</code></pre>
</body></html>
Last updated: 16th September 2021
First published: 16th August 2018
First published: 16th August 2018
