CODEWITHSUNDEEP
javascriptphparraysjsongoogle-visualization
jelle Kake
jelle Kake

Reputation: 27

Google area chart empty page with data from JSON

I am working on a KPI dashboard, my database contains hot water (hwater) usage per month(adate), comparing this year with last year as an area chart.

However when I run this report my webpage is blank. I have tried setting google.visualization.arrayToDataTable to .DataTable while declaring the column names and also tried with server side AJAX with no success.

Area_Chart.php

<?php
require_once  ("connect.php");

//fetch table rows from mysql db
$query = $handler->query("
    SELECT MONTH(adate) as month,
    MAX(case when YEAR(adate) = YEAR(CURRENT_DATE)-1 THEN  hwater ELSE 0 END) as 'Last_Year',
    MAX(case when YEAR(adate) = YEAR(CURRENT_DATE) THEN hwater ELSE 0 END) as 'This_Year'
    FROM utlt
    WHERE YEAR(adate) = YEAR(CURRENT_DATE)-1 OR year(adate) = YEAR(CURRENT_DATE)
    GROUP BY MONTH(adate)
    ORDER BY MONTH(adate)");

$query->execute();
$results=$query->fetchAll(PDO::FETCH_NUM);
$json=json_encode($results);

//echo $json;
?>    

<html>
  <head>
    <script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>
    <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.10.2/jquery.min.js"></script>
    <script type="text/javascript">

      google.charts.load('current', {'packages':['corechart']});

      google.charts.setOnLoadCallback(drawChart);

      function drawChart() {
        var data = google.visualization.arrayToDataTable([
          [ {label: 'Year', id: 'month'},
            {label: 'Last_Year', id: 'Last_Year', type: 'number'},
            {label: 'This_Year', id: 'This_Year', type: 'number'}],
          <?=$json?>      
        ], false);


        var options = {
          isStacked: 'absolute',
          title: 'Hot Water Useage',
          hAxis: {title: 'Year',  titleTextStyle: {color: '#333'}},
          vAxis: {minValue: 0}

        };

        var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));
        chart.draw(data, options);
      } 


    </script>
  </head>
  <body>
    <div id="chart_div" style="width: 100%; height: 500px;"></div>
  </body>
</html>

Output of echo $json:

[["1","1.671","21.269"],["2","3.810","22.017"],["3","5.554","24.045"],["4","6.930","25.036"],["5","8.845","27.116"],["6","11.793","28.058"],["7","13.244","28.996"],["8","14.471","30.342"],["9","16.260","30.977"],["10","17.199","31.923"],["11","18.494","33.155"],["12","19.563","33.797"]]

I notice above that hwater from utlt are rapped in "", does this mean its a string? The field type is actually a decimal (6,3).

Upvotes: 1

Views: 175

Answers (1)

WhiteHat
WhiteHat

Reputation: 61222

hwater from utlt being wrapped in "" is definitely a problem,
since the column type is 'number'

try using JSON_NUMERIC_CHECK to encode the json

$json=json_encode($results, JSON_NUMERIC_CHECK);

EDIT

think i found the problem here... 

var data = google.visualization.arrayToDataTable([
  [ {label: 'Year', id: 'month'},
    {label: 'Last_Year', id: 'Last_Year', type: 'number'},
    {label: 'This_Year', id: 'This_Year', type: 'number'}],
  <?=$json?>  // <-- problem    
], false);

the problem is the outer array wrapping the row arrays
//--> [[1,1.671,21.269]...

given where <?=$json?> is placed, the rows shouldn't be wrapped in array

this can easily be corrected by using the addRows method to load the json

remove json from arrayToDataTable 

var data = google.visualization.arrayToDataTable([
  [ {label: 'Year', id: 'month', type: 'number'},
    {label: 'Last_Year', id: 'Last_Year', type: 'number'},
    {label: 'This_Year', id: 'This_Year', type: 'number'}],
]);

then add another statement for the addRows method, which needs the outer array 

data.addRows(
  <?=$json?>
);

see following working snippet... 

google.charts.load('current', {
  callback: drawChart,
  packages: ['corechart']
});

function drawChart() {
  var data = google.visualization.arrayToDataTable([
    [ {label: 'Year', id: 'month', type: 'number'},
      {label: 'Last_Year', id: 'Last_Year', type: 'number'},
      {label: 'This_Year', id: 'This_Year', type: 'number'}],
  ]);

  data.addRows(
    [[1,1.671,21.269],[2,3.810,22.017],[3,5.554,24.045],[4,6.930,25.036],[5,8.845,27.116],[6,11.793,28.058],[7,13.244,28.996],[8,14.471,30.342],[9,16.260,30.977],[10,17.199,31.923],[11,18.494,33.155],[12,19.563,33.797]]
  );

  var options = {
    isStacked: 'absolute',
    title: 'Hot Water Useage',
    hAxis: {title: 'Year',  titleTextStyle: {color: '#333'}},
    vAxis: {minValue: 0}

  };

  var chart = new google.visualization.AreaChart(document.getElementById('chart_div'));
  chart.draw(data, options);
}
<script src="https://www.gstatic.com/charts/loader.js"></script>
<div id="chart_div"></div>

Upvotes: 1

Related Questions