Programster's Blog

Tutorials focusing on Linux, programming, and open-source

PHP - Converting Data for Google Charts

PHP Google-charts 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