Google Charts和JSON数据

I'm not a JS guy, I working on a small interface for my weather station. I have a serverside code which generates the JSON data for the graph. It looks like this:

[
  {
    "temperature": "32.1",
    "humidity": "91",
    "battery": "100",
    "time": "2016-02-21 15:28:56"
  },
  {
    "temperature": "32.1",
    "humidity": "99.3",
    "battery": "100",
    "time": "2016-02-21 15:28:47"
  },
  {
    "temperature": "22.2",
    "humidity": "70.2",
    "battery": "88.2",
    "time": "2016-02-21 15:28:19"
  },
  {
    "temperature": "21.2",
    "humidity": "88.1",
    "battery": "90.4",
    "time": "2016-02-21 15:28:22"
  }
]

How can I feed this data into a Line chart using Google's Chart API? I have tried using the example but it does not work. (https://developers.google.com/chart/interactive/docs/gallery/linechart)

First define your array in the format which google charts requires:

$resultsarray = array(
                    'cols' => array(
                                    array('label' => 'temperature', 'type' => 'number'),
                                    array('label' => 'humidity', 'type' => 'number'),
                                    array('label' => 'battery', 'type' => 'number'),
                                    array('label' => 'time', 'type' => 'date'),
                                        ),
                    'rows' => array()
                    );

Then add the data to your array:

$resultsarray['rows'][] = array('c' => array( array('v'=> 'tempvalue'), array('v'=>'humidityval'), array('v'=>'batteryval'),array('v'=>'timeval')));

Encode as JSON and write to a file:

$fp = fopen('data.json', 'w');

    //JSON encode and write array to file
    fwrite($fp, json_encode($resultsarray, JSON_NUMERIC_CHECK));
    fclose($fp);

Then in your JavaScript you need: (as per Google Example)

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

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

Then the contents of getdata.php (as per google example)

<?php 

// This is just an example of reading server side data and sending it to the client.
// It reads a json formatted text file and outputs it.

$string = file_get_contents("data.json");
echo $string;

// Instead you can query your database and parse into JSON etc etc

?>

Google charts is expecting the JSON in this format:

{
  "cols": [
        {"id":"","label":"temperature","pattern":"","type":"number"},
        {"id":"","label":"humidity","pattern":"","type":"number"},
        {"id":"","label":"battery","pattern":"","type":"number"},
        {"id":"","label":"time","pattern":"","type":"date"}
      ],
  "rows": [
        {"c":[{"v":"32.1","f":null},{"v":99.3,"f":null},{"v":100,"f":null},{"v":2016-02-21 15:28:47,"f":null}]}
      ]
}

The json isn't formatted properly. Google charts expect a specific json type for its chart. I'm assuming that your database is mysql. If not, I can always modify the following code. I have created a small library to perform these annoying tasks. So here it is :

<?php


function generate_GChart_cols($result) {

    $fieldcount = mysqli_num_fields($result);
    $stringVal = [253]; // MySQL field type codes -See http://php.net/manual/en/mysqli-result.fetch-field-direct.php
    $numericVal = [246, 8];  // MySQL field type codes -See http://php.net/manual/en/mysqli-result.fetch-field-direct.php
    $colsarray = array();

    for ($i = 0; $i < $fieldcount; $i++) {
        $finfo = mysqli_fetch_field_direct($result, $i);
        switch ($finfo->type) {
        case in_array($finfo->type, $stringVal):
            $type = 'string';
            break;

        case in_array($finfo->type, $numericVal):
            $type = 'number';
            break;

        default:
            $type = 'string';
        }

        // Constructs the column array

        $colsarray[] = array(
            'label' => $finfo->name,
            'type' => $type
        );
    }

    return $colsarray;
}



function generate_GChart_rows($result) {

    $fieldcount = mysqli_num_fields($result);
    $rows = array();

    while ($r = $result->fetch_row()) {

        $temp = array();

        for ($j = 0; $j < $fieldcount; $j++) {            

            $temp[] = array(
                'v' => $r[$j]
            );
        }

        $rows[] = array(
            'c' => $temp
        );
    };
    return $rows;
}

?>

A usage example would be :

<?php
header('content-type: application/json; charset=utf-8');
header('Access-Control-Allow-Origin:*'); //Include this header to make the requests cross-origin
include 'dbconnect.php';
include 'generate_google_json.php';

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = // Your query here - e.g SELECT * FROM TABLE // ;
$result = $conn->query($sql);
$table['data']['rows'] = generate_GChart_rows($result); // Call to function to generate rows
$table['data']['cols'] = generate_GChart_cols($result); // Call to function to generate columns
$conn->close(); // Close db connection

// We echo the json and enforce a numeric check on the values

echo $_GET['callback'] . '(' . json_encode($table, JSON_NUMERIC_CHECK) . ')';
?>

An example output would be :

?(
    {  
      data:{  
          rows:[  
             {  
                c:[  
                   {  
                      v:3123600
                   },
                   {  
                      v:3116452
                   }
                ]
             }
          ],
          cols:[  
             {  
                label:2013,
                type:"number"
             },
             {  
                label:2014,
                type:"number"
             }
          ]
       }
    }
)

In the event where you use other types than bigint, varchart and decimal, you might wanna add more values to the $stringVal and $numericVal arrays :)

Cheers