[visualization-api] Creating a Google Line Chart from MYSQL data

[joseph mutisya]

My aim is to create multiple line charts on the same graph using data i
pull from mysql database.


I have the code in place but I'm missing a step therefore not getting the
output I expect. Here's my code:


<?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;?>
<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>

So that's my code. This is the json that is passed to the chart from the
database:

{"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 below is the line chart that is output:


line chart output from above code <Loading Image...>


This is not what I want. My end goal is to get 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:


linechart <Loading Image...>


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


I'm a bit stuck on this and would like to request for guidance on how to
accomplish this

['Daniel LaLiberte' via Google Visualization API]

The most straightforward way to implement your two-line chart is to put
each year of data in a separate column. Just use the month name (or
number) for the domain values rather than the full dates, and label the
columns according to the year.

Post by joseph mutisya
My aim is to create multiple line charts on the same graph using data i
pull from mysql database.
I have the code in place but I'm missing a step therefore not getting the
<?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;?>
<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>
So that's my code. This is the json that is passed to the chart from the
{"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}]}]}
line chart output from above code <http://i.stack.imgur.com/p2Mn4.png>
This is not what I want. My end goal is to get 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.
linechart <http://i.stack.imgur.com/II4gB.png>
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
I'm a bit stuck on this and would like to request for guidance on how to
accomplish this