suo suo - 3 months ago 23
JSON Question

Dynamically populate Google line chart with data from MYSQL

My end goal is to get a graph that displays multiple line charts(depending on the number of years present) with all the months displaying on the X-axis with the amount displaying on the Y-axis. This is the closest thing I've seen that resembles what I want to achieve:

google line chart

The above image shows what I want to achieve. Like stated before, the months on the X-axis with the amount on the Y-axis. then the 'Values' would be the years that have been returned from the query i.e. every year will have its own line chart

This is the the php bit that returns the json:

<?php
$results = array('cols' => array (array('label' => 'Date', 'type' => date'),
array('label' => 'Amount', 'type' => 'number')
),
'rows' => array()
);

$query = $db->prepare('SELECT * FROM Claims GROUP BY EXTRACT(MONTH FROM ClaimDate ) , EXTRACT( YEAR FROM ClaimDate ) ');

$query->execute();
$rows1 = $query->fetchAll(PDO::FETCH_ASSOC);

foreach($rows1 as $row)
{
$ClaimDate = DateTime::createFromFormat('Y-m-d H:i:s', $row['ClaimDate'])->format('Y-m-d');

$dateArr = explode('-', $ClaimDate);
$year = (int) $dateArr[0];
$month = (int) $dateArr[1] - 1;
$day = (int) $dateArr[2];

$results['rows'][] = array('c' => array(array('v' => "Date($year, $month, $day)"), array('v' => $row['amount'])
));
}
$json = json_encode($results, JSON_NUMERIC_CHECK);
// print_r($json);exit;
?>


This is the json returned:

{"cols":[{"label":"Date","type":"date"},{"label":"Amount","type":"number"}],"rows":[{"c":[{"v":"Date(2015, 5, 23)"},{"v":6000}]},{"c":[{"v":"Date(2016, 5, 23)"},{"v":16000}]},{"c":[{"v":"Date(2015, 6, 23)"},{"v":10000}]},{"c":[{"v":"Date(2016, 6, 23)"},{"v":10000}]},{"c":[{"v":"Date(2015, 7, 23)"},{"v":5000}]},{"c":[{"v":"Date(2016, 7, 23)"},{"v":60000}]}]}


And finally, this is the function that renders the chart:

<script type="text/javascript">
google.load("visualization", "1", { packages: ["corechart"]});
google.setOnLoadCallback(drawChart);

function drawChart()
{
var data = new google.visualization.DataTable(<?php echo json_encode($json); ?>);
var chart = new google.visualization.LineChart(document.getElementById('line_chart'));
chart.draw(data, {width: 400, height: 240});
}
</script>
<div id="line_chart"></div>


The output from all this looks like this:

chart rendered by the above code

As shown above, I'm quite some distance into achieving what i want. I'm stuck on this and any help would be much appreciated

EDIT

Based on Ram Kannan Raj's answer, I've added the ORDER BY ClaimDate ASC to my query and the line chart does look a bit more presentable:

improved linechart

but still not my desired output. Each year should have its own line graph and the X-axis to be all the months

Answer

in order to get the chart you describe...

1) separate lines for each year requires separate columns (series) for each
2) all the months across the hAxis requires a discrete axis (string values)
3) the chart also needs to be wide enough to show all the labels

producing a DataTable as follows, with 12 rows, one for each month

{
  "cols":[
    {"label":"Month","type":"string"},
    {"label":"2014","type":"number"},
    {"label":"2015","type":"number"},
    {"label":"2016","type":"number"}
  ],
  "rows":[
    {"c":[{"v":"January"},{"v":1000},{"v":1200},{"v":1400}]},
    {"c":[{"v":"February"},{"v":1600},{"v":1800},{"v":2000}]},
    {"c":[{"v":"March"},{"v":2200},{"v":2400},{"v":2600}]},
    ...
  ]
}

to create this dynamically, see comments in following php

<?php
    $query = $db->prepare('SELECT * FROM Claims GROUP BY EXTRACT(MONTH FROM ClaimDate ) , EXTRACT( YEAR FROM ClaimDate ) ORDER BY ClaimDate ASC');

    $query->execute();
    $rows1 = $query->fetchAll(PDO::FETCH_ASSOC);

    $dates = array();

    // create data table with initial month column
    $results = array(
      'cols' => array(
        array('label' => 'Month', 'type' => 'string')
      ),
      'rows' => array()
    );

    // create a column for each year in the data
    $colYear = "";
    foreach($rows1 as $row) {
      $year = DateTime::createFromFormat('Y-m-d H:i:s', $row['ClaimDate'])->format('Y');
      $year = strval($year);

      if ($year != $colYear) {
        $results['cols'][] = array('label' => $year, 'type' => 'number');
        $colYear = $year;
      }
    }

    // create hAxis ticks
    $hTicks = array('January', 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December');

    // create 12 blank rows for each month
    $chartRows = array();
    foreach($hTicks as $hMonth) {
      $blankRow = array(array('v' => $hMonth));
      for ($x = 1; $x < count($results['cols']); ++$x) {
        $blankRow[] = array('v' => null);
      }
      $chartRows[] = array('c' => $blankRow);
    }

    // fill in the rows from the data
    foreach($rows1 as $row) {
      // get year and month for current row
      $year = DateTime::createFromFormat('Y-m-d H:i:s', $row['ClaimDate'])->format('Y');
      $month = DateTime::createFromFormat('Y-m-d H:i:s', $row['ClaimDate'])->format('F');
      $colYear = strval($year);

      // find month row
      for ($i = 0; $i < count($hTicks); ++$i) {
        if ($hTicks[$i] == $month) {
          // find year column
          for ($x = 0; $x < count($results['cols']); ++$x) {
            if ($results['cols'][$x]['label'] == $colYear) {
              $chartRows[$i]['c'][$x]['v'] = $row['amount'];
            }
          }
        }
      }
    }

    $results['rows'] = $chartRows;

    $json = json_encode($results);

?>

and the client side...

note the option for hAxis.ticks
and removed option for width

<script src="https://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load("visualization", "1", { packages: ["corechart"]});
google.setOnLoadCallback(drawChart);

function drawChart()
{
  var data = new google.visualization.DataTable(<?php echo json_encode($json); ?>);
    var chart = new google.visualization.LineChart(document.getElementById('line_chart'));
    chart.draw(data, {
      hAxis: {
        ticks: <?php echo json_encode($hTicks); ?>
      },
      height: 400
    });
}
</script>
<div id="line_chart"></div>
Comments