title: Google stacked column chart -how to create required json from mysql query results
body: <p>This is a question about creating json from MySQL query results. The json will be used in Google Chart.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>I am struggling with getting the json array into the pivoted / cross table format that Google appears to want (eg here [<a href="https://developers.google.com/chart/interactive/docs/gallery/barchart#stacked-bar-charts]" rel="nofollow">https://developers.google.com/chart/interactive/docs/gallery/barchart#stacked-bar-charts]</a>) </p>
  
  <p>I suspect that i need to loop through the cards and create column headings from them similarly to how the row values are created </p>
  
  <pre><code>$rows = array();
  while($r = mysql_fetch_assoc($result)) {
      $temp = array();
      $temp[] = array('v' =&gt; $r['card']);
      $temp[] = array('v' =&gt; (int) $r['value']); 
      // insert the temp array into $rows
      $rows[] = array('c' =&gt; $temp);
  }
  </code></pre>
  
  <p>And instead of just have a single column for the cards as done for basic unstacked ColumnChart like this:</p>
  
  <pre><code>//define your DataTable columns here
  $table['cols'] = array(
      array('label' =&gt; 'card', 'type' =&gt; 'string'),
      array('label' =&gt; 'value', 'type' =&gt; 'number')
  );
  </code></pre>
  
  <p>Maybe do something like this:</p>
  
  <pre><code>//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' =&gt; $r['card']);
      $cols[] = array('c' =&gt; $temp);
  }
  );
  </code></pre>
  
  <p>But then still not sure how the value is introduced into array.</p>
  
  <p>Can anyone point me in right direction please?</p>
  
  <p><strong>MySQL/php/json data source:</strong></p>
  
  <pre><code>&lt;?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' =&gt; 'card', 'type' =&gt; 'string'),
      array('label' =&gt; 'value', 'type' =&gt; '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' =&gt; $r['card']);
      $temp[] = array('v' =&gt; (int) $r['value']); 
      // insert the temp array into $rows
      $rows[] = array('c' =&gt; $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);
  
  ?&gt;
  </code></pre>
  
  <p><strong>Google chart web page:</strong></p>
  
  <pre><code>&lt;html&gt;
  &lt;head&gt;
      &lt;!--Load the AJAX API--&gt;
      &lt;script type="text/javascript" src="https://www.google.com/jsapi"&gt;   &lt;/script&gt;
      &lt;script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"&gt;&lt;/script&gt;
      &lt;script type="text/javascript"&gt;
  
          // 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);
  
          }
  
      &lt;/script&gt;
  &lt;/head&gt;
  
  &lt;body&gt;
      &lt;!--Div that will hold the chart--&gt;
      &lt;div id="chart_div"&gt;&lt;/div&gt;
  
  &lt;/body&gt;
  &lt;/html&gt;
  </code></pre>