/ php

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>
Stuart Page

Stuart Page

Stuart is a software developer with a passion for Linux and open source projects.

Read More