Google.visualization.dashboard not rendering with json array php

Question

I’m passing an encoded json array from a MYSQL query to render a google.visualization.dashboard. I am almost certain the problem is with my array but I can't find where. The code works when I draw the chart google charts directly (eg google.visualization.PieChart) but not when I use dashboard / control wrapper / chart wrapper classes.

That leads me to believe that the problem is either with my array structure or that google.visualization.dashboard requires the data table to be populated differently than the charts.

PHP code (loadpiechart.php):

$table['cols'] = array(
    array('label' => 'NZ Crime', 'type' => 'string'),
    array('label' => 'Value', 'type' => 'number'),
);

$rows=array();
while($r=mysqli_fetch_assoc($res)){
    $temp=array();
    $temp[]=array('v'=> $r['Offence']);
    $temp[]=array('v' => $r['Total']);
    $rows[]=array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table, JSON_NUMERIC_CHECK);
echo $jsonTable;

Which gives me the following array[]

{"cols":[{"id":"A","label":"NZ Crime","type":"string"},{"id":"B","label":"Value","type":"number"}],"rows":[{"c":[{"v":" Acts intended to cause injury"},{"v":97}]},{"c":[{"v":" Sexual assault and related offences"},{"v":44515}]},{"c":[{"v":" Dangerous or negligent acts endangering persons"},{"v":3016}]},{"c":[{"v":" Abduction, harassment and other related offences against a person"},{"v":859}]},{"c":[{"v":" Robbery, extortion and related offences"},{"v":14157}]},{"c":[{"v":" Unlawful entry with intent\/burglary, break and enter"},{"v":2641}]},{"c":[{"v":" Theft and related offences"},{"v":59323}]},{"c":[{"v":" Fraud, deception and related offences"},{"v":136932}]},{"c":[{"v":" Illicit drug offences"},{"v":9726}]},{"c":[{"v":" Prohibited and regulated weapons and explosives offences"},{"v":22994}]},{"c":[{"v":" Property damage and environmental pollution"},{"v":7074}]},{"c":[{"v":" Public order offences"},{"v":58483}]},{"c":[{"v":" Offences against justice procedures, government security and government operations"},{"v":46105}]},{"c":[{"v":" Miscellaneous offences"},{"v":19084}]}]}

And finally the HTML code.

html>
  <head>           
    <!--Load the AJAX API-->
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript">

    // Load the Visualization API and the piechart package.
      google.charts.load('current', {packages:['corechart', 'table', 'controls']});

    // Set a callback to run when the Google Visualization API is loaded.
    google.setOnLoadCallback(drawTable);

    function drawTable() {
         var jsonData = $.ajax({
             url: "loadpiechart.php",
             dataType:"json",
             async: false
             }).responseText;

         // Create our data table out of JSON data loaded from server.
         var data = new google.visualization.DataTable(jsonData);

      var table = new google.visualization.ChartWrapper({
          'chartType': 'Table',  
          'containerId': 'table_div',
      });

      var chart = new google.visualization.ChartWrapper({
         'chartType': 'PieChart',  
          'containerId': 'chart_div',
          'view': {'columns': [0, 1]},
           });

      var control = new google.visualization.ControlWrapper({
            'controlType': 'CategoryFilter',
            'containerId': 'control_div',
            'options': {
                'filterColumnIndex': 0,
            }
      });

      var dashboard = new google.visualization.Dashboard(
                document.getElementById('dashboard_div'));
      dashboard.bind([control], [table,chart]);
      dashboard.draw(data);
    }
    </script>
  </head>
 <body>
<div id="dashboard_div" style="border: 1px solid #ccc; margin-top: 1em">
  <p style="padding-left: 1em"><strong>NZ Crime Stats</strong></p>
  <table class="columns">
    <tr>
      <td>
        <div id="control_div" style="padding-left: 15px"></div>
      </td>
    </tr><tr>
      <td>
        <div id="chart_div" style="padding-top: 15px"></div>
      </td><td>
        <div id="table_div" style="padding-top: 30px"></div>
      </td>
    </tr>
  </table>
</div>  
  </body>
</html>

The only minor issue I see is you don't set the appropriate header in the PHP code, I would recommend adding `header('Content-Type: application/json');` before echoing the JSON result. The data structure is the same for dashboard and chart as stated [in the docs](https://developers.google.com/chart/interactive/docs/gallery/controls#3.-prepare-your-data): "Dashboards accepts data in a DataTable, the same as charts.". Here is a [working fiddle](https://jsfiddle.net/Moonbird_IT/rk31bfsv/6/) with your code, only change was to use static data instead of AJAX. — SaschaM78, Jun 09 '20 at 09:51
Thanks for this. I was able to get it to work with static data as well. I found a solution using my original code by adding jQuery. — Bennznznznz, Jun 09 '20 at 20:11
Good you solved it. I would still recommend to change from `async:false` to the `success()` method as synchronous calls are locking up the browser for the duration of the request. They are also marked as [deprecated and may issue a warning in the Console](https://stackoverflow.com/questions/28680897/jquery-ajax-async-false-causes-a-strange-warning). — SaschaM78, Jun 10 '20 at 06:39
Thank you for the explanation, I have amended my code to include your change. Much appreciated. — Bennznznznz, Jun 11 '20 at 21:10

score 0 · Answer 1 · answered Jun 09 '20 at 10:07

I would recommend to change the AJAX call to a non-blocking asynchronous call and call the drawing routine in the success() method:

function drawTable() {

  $.ajax("https://gist.githubusercontent.com/Moonbird-IT/da4c7d76a69eb250478bb55b5d2360f5/raw/9dbf9d92981a3c9b71906dd3a680a2cdeca7c4aa/googlecharts.json", {
    dataType: "json",
    success: function(jsonData) {

      // Create our data table out of JSON data loaded from server.
      var data = new google.visualization.DataTable(jsonData);
      ...
    }
  });
}

I updated your code to use the changed recommendation, here is a working Fiddle of it.

Thanks for this addition. I found a solution with my original code as well. — Bennznznznz, Jun 09 '20 at 20:10

score 0 · Answer 2 · answered Jun 09 '20 at 20:09

My problem was that I wasn't calling jQuery. I added this line of code and it works my original code plus this addition.

<script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>

Link here for google.visaulization documentation https://developers.google.com/chart/interactive/docs/php_example

Google.visualization.dashboard not rendering with json array php

2 Answers2