This is a question about creating json from MySQL query results. The json will be used in Google Chart.
I have not seen an explicit example of how to create json array from MySQL query results that can be used in Google ColumnChart where isStacked: true.
I have the ColumnChart without stacking working fine. That code is included below. The chart shows card on x axis and values on y axis.
However, now I want to include a date value (year and month string) and put that on x axis instead, and then stack the cards within columns by date.
I am struggling with getting the json array into the pivoted / cross table format that Google appears to want (eg here [https://developers.google.com/chart/interactive/docs/gallery/barchart#stacked-bar-charts])
I suspect that i need to loop through the cards and create column headings from them similarly to how the row values are created
$rows = array();
while($r = mysql_fetch_assoc($result)) {
$temp = array();
$temp[] = array('v' => $r['card']);
$temp[] = array('v' => (int) $r['value']);
// insert the temp array into $rows
$rows[] = array('c' => $temp);
}
And instead of just have a single column for the cards as done for basic unstacked ColumnChart like this:
//define your DataTable columns here
$table['cols'] = array(
array('label' => 'card', 'type' => 'string'),
array('label' => 'value', 'type' => 'number')
);
Maybe do something like this:
//define your DataTable columns here
$table['cols'] = array(
// loop through query results to get cards and make column from each of them
while($r = mysql_fetch_assoc($result)) {
$temp = array();
$temp[] = array('v' => $r['card']);
$cols[] = array('c' => $temp);
}
);
But then still not sure how the value is introduced into array.
Can anyone point me in right direction please?
MySQL/php/json data source:
<?php
//connect to database
$username = "xxx";
$password = "xxx";
$hostname = "127.0.0.1";
//connection to the database
$conn = mysql_connect($hostname, $username, $password) or die("Could not connect to that");
mysql_select_db("sitrucp_ppcc");
//run query
$query = "select
ca.name as 'card',
count(a.id) as 'value'
from activities a
join cards ca
on a.card_id = ca.id
group by
ca.name
order by
ca.name";
$result = mysql_query($query) or die(mysql_error());
$table = array();
//define your DataTable columns here
$table['cols'] = array(
array('label' => 'card', 'type' => 'string'),
array('label' => 'value', 'type' => 'number')
);
// each column needs to have data inserted via the $temp array
// typecast all numbers to the appropriate type (int or float) as needed - otherwise they are input as strings
$rows = array();
while($r = mysql_fetch_assoc($result)) {
$temp = array();
$temp[] = array('v' => $r['card']);
$temp[] = array('v' => (int) $r['value']);
// insert the temp array into $rows
$rows[] = array('c' => $temp);
}
// populate the table with rows of data
$table['rows'] = $rows;
// encode the table as JSON
$jsonTable = json_encode($table);
echo $jsonTable;
mysql_close($conn);
?>
Google chart web page:
<html>
<head>
<!--Load the AJAX API-->
<script type="text/javascript" src="https://www.google.com/jsapi"> </script>
<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
<script type="text/javascript">
// Load the Visualization API and the chart package.
google.load('visualization', '1', {'packages':['corechart']});
// Set a callback to run when the Google Visualization API is loaded.
google.setOnLoadCallback(drawChart);
function drawChart() {
// Create our data table out of JSON data loaded from server
var jsonData = $.ajax({
url: "activities_json_by_card.php",
dataType:"json",
async: false
}).responseText;
var data = new google.visualization.DataTable(jsonData);
//set chart options
var options = {
title: 'Card Activities',
vAxis: {title: "Value"},
hAxis: {title: "Cards"},
width: 500,
height: 500,
legend: { position: 'none' }
};
// Instantiate and draw our chart, passing in some options.
var chart = new google.visualization.ColumnChart(document.getElementById('chart_div'));
chart.draw(data, options);
}
</script>
</head>
<body>
<!--Div that will hold the chart-->
<div id="chart_div"></div>
</body>
</html>