CODEWITHSUNDEEP
javascriptphpjsongoogle-visualization
DevNodeX99
DevNodeX99

Reputation: 579

Google Charts using JSON

I have not used Google Charts before, but I am trying to do some temperature graphing using sensors located around the house, however I keep getting an Exception. I'm fairly certain its because the JSON is not in the correct format but struggling on what format it needs and how to get my script to generate JSON in that format.

PHP script generating JSON from database

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

$array = array();
$res = mysqli_query($con, "SELECT * FROM sensors WHERE vera_variable='CurrentTemperature'");
while ($row = mysqli_fetch_array($res)) {
    $sensor_id = $row['sensor_id'];
    $sensor_name = $row['sensor_name'];

    $res2 = mysqli_query($con, "SELECT * FROM logs WHERE sensor_id='$sensor_id'");
    while ($row2 = mysqli_fetch_array($res2)) {
        $time = strtotime($row2['log_time']);
        $formattedTime = date("m-d-y g:i", $time);

        $sensor_value = $row2['sensor_value'];

            $array[$formattedTime][$sensor_name] = $sensor_value;
    }
}

$json = json_encode($array,  JSON_PRETTY_PRINT);
echo "<pre>" . $json . "</pre>";

?>

An example output from the above script. You can see there is a date, multiple sensors and their coresponding values

{
    "12-12-15 8:35": {
        "Living Room Temperature": "18.3",
        "Outside Temperature": "-5",
        "Mud Room Temperature": "16.0",
        "Basement Temperature": "14.0"
    },
    "12-12-15 8:40": {
        "Living Room Temperature": "18.3",
        "Outside Temperature": "-5",
        "Mud Room Temperature": "16.0",
        "Basement Temperature": "13.0"
    },
    "12-12-15 8:45": {
        "Living Room Temperature": "18.3",
        "Outside Temperature": "-5",
        "Mud Room Temperature": "16.0",
        "Basement Temperature": "13.0"
    },
    "12-12-15 8:50": {
        "Living Room Temperature": "18.3",
        "Outside Temperature": "-5",
        "Mud Room Temperature": "16.0",
        "Basement Temperature": "13.0"
    },
    "12-12-15 8:55": {
        "Living Room Temperature": "18.3",
        "Outside Temperature": "-5",
        "Mud Room Temperature": "16.0",
        "Basement Temperature": "13.0"
    },
    "12-12-15 9:00": {
        "Living Room Temperature": "17.8",
        "Outside Temperature": "-5",
        "Mud Room Temperature": "16.0",
        "Basement Temperature": "13.0"
    }
    }

The following is what I have (just a simple example chart using json)

<html>
    <head>
        <!-- Load jQuery -->
        <script language="javascript" type="text/javascript"
        src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.0/jquery.min.js"></script>
        <!-- Load Google JSAPI -->
        <script type="text/javascript" src="https://www.google.com/jsapi"></script>
        <script type="text/javascript">
            google.load("visualization", "1", {
                packages : ["corechart"]
            });
            google.setOnLoadCallback(drawChart);

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

                var obj = window.JSON.stringify(jsonData);
                var data = google.visualization.arrayToDataTable(obj);

                var options = {
                    title : 'Graph'
                };

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

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

EDIT: Here is the error that Chrome shows when trying to load the graph:

Uncaught Error: Not an arraylha @ format+en,default+en,ui+en,corechart+en.I.js:191bha @ format+en,default+en,ui+en,corechart+en.I.js:193drawChart @ temperature.php:22

Upvotes: 3

Views: 710

Answers (2)

Vadim Gremyachev
Vadim Gremyachev

Reputation: 59358

This error occurs since the input JSON data format (obj variable) is not compatible with Google Chart data JSON format.

You could transform the input data to the supported format as demonstrated below:

var chartData = [];
chartData.push(['Time','Living Room Temperature','Outside Temperature','Mud Room Temperature','Basement Temperature']);
for (var key in obj) {
     var item = obj[key];
     chartData.push([new Date(key),parseFloat(item['Living Room Temperature']),parseFloat(item['Outside Temperature']),parseFloat(item['Mud Room Temperature']),parseFloat(item['Basement Temperature'])]);       
 }
 var data = google.visualization.arrayToDataTable(chartData);

Working example

Some changes have been made on how data is loaded, in particular since it's considered a bad practice to perform synchronous calls async is set to true. In addition, requests are handled via promises.

google.load("visualization", "1", {
    packages: ["corechart"]
});
google.setOnLoadCallback(drawChart);

function drawChart() {
    $.ajax({
        url: "https://gist.githubusercontent.com/vgrem/e08a3da68a5db5e934a1/raw/2f971a9d1524d0457a6aae4df861dc5f0af0a2ef/data.json", //json_temp.php
        dataType: "json"
    })
    .done(function (data) {
        
            var chartData = [];
            chartData.push(['Time','Living Room Temperature','Outside Temperature','Mud Room Temperature','Basement Temperature']);
            for (var key in data) {
                var item = data[key];
                chartData.push([new Date(key),parseFloat(item['Living Room Temperature']),parseFloat(item['Outside Temperature']),parseFloat(item['Mud Room Temperature']),parseFloat(item['Basement Temperature'])]);       
            }

            var dataTable = google.visualization.arrayToDataTable(chartData);
            var options = {
                title: 'Graph'
            };
            var chart = new google.visualization.LineChart(document.getElementById('chart_div'));
            chart.draw(dataTable, options);

     });
}
<script type="text/javascript" src="http://code.jquery.com/jquery-1.11.3.min.js"></script>
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
<div id="chart_div" style="width: 900px; height: 500px;"></div>

Upvotes: 1

jonasnas
jonasnas

Reputation: 3580

Looks like you are passing an Object instead of the expected Array.

Try this instead:

var obj = window.JSON.stringify(jsonData);
var arr = [];
Object.keys(obj).forEach(function(key){
   var o = obj[key]; 
   o.time = key; 
   arr.push(o);
});
var data = google.visualization.arrayToDataTable(arr);

Upvotes: 0

Related Questions