Google stacked column chart -how to generate required json from mysql query results

Question

Google stacked column chart -how to generate required json from mysql query results

It is a matter of generating json from MySQL query results. Json will be used in Google Chart.

I haven't seen an explicit example on how to create a json array from MySQL query results that can be used in Google ColumnChart where isStacked: true.

I have a ColumnChart without stacking. This code is shown below. The chart shows a map on the x-axis and values on the y-axis.

However, now I want to include the date value (year and month string) and put it on the x-axis and then put them in columns by date.

I am struggling to get a json array in pivot / crosstab format which Google seems to require (for example here [ https://developers.google.com/chart/interactive/docs/gallery/barchart#stacked -bar-charts] )

I suspect I need to loop through the maps and create column headers from them in a similar way to how 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 having one Column for the maps as it does for a base unset ColumnChart like:

//define your DataTable columns here
$table['cols'] = array(
    array('label' => 'card', 'type' => 'string'),
    array('label' => 'value', 'type' => 'number')
);

Maybe 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 not sure yet how the value is entered into the array.

Can anyone point me in the right direction?

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>