使用json将数据从mysql解析为highchart

问题描述：

new to this and (nearly) desperated. What i want to do is the following:

read temp from sensor (works, returns float)
save data in mysql database boxklima.sensorid (works - table is boxklima.0414604605ff) in sets of date-temp pairs the date is stored as 2014-01-01 09:00:00 (datetime) and the temp as 12.123 (real)
graph data as x-axis: date y-axis: temp (totally messed this up)

So i stick to highcharts which looks nice and seems be easy to set up because everything happens client-side (except data-parsing).

Now I dont't know exactly how do create the array which is given to highchart. It should look something like this (content of create-graph.html)

<!DOCTYPE html>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script‌>
<script src="http://code.highcharts.com/highcharts.js"></script>
<script src="http://code.highcharts.com/modules/exporting.js"></script>

<div id="container" style="min-width: 310px; height: 400px; margin: 0 auto"></div>

<script type="text/javascript">
$(document).ready(function() {

    var options = {
        chart: {
            renderTo: 'container',
            type: 'spline'
        },
        series: [{}]
    };

    $.getJSON('data.php', function(data) {
        options.series[0].data = temp;
        var chart = new Highcharts.Chart(options);
    });

});
</script>

Parse the data with the data.php (content shown below) from the sql-server, should generate the data on call, right?

<php>
 $dbc = mysqli_connect("localhost", "pi", "pi", "boxklima");
 $query = "SELECT date,temp FROM 0414604605ff";
 $data = mysqli_query($dbc, $query);
 $i=0;
 while($row = mysqli_fetch_array($data)) {
    $rows[$i]=array($row['date'],$row['temp']);
    $i++;
 }
 echo json_encode($rows);
</php>

Opening (in browser) of data.php just shows

$dbc = mysqli_connect("localhost", "pi", "pi", "boxklima"); $query = "SELECT date,temp FROM 0414604605ff"; $data = mysqli_query($dbc, $query); $i=0; while($row = mysqli_fetch_array($data)) { $rows[$i]=array($row['date'],$row['temp']); $i++; } echo json_encode($rows);

Opening create-graph.html results in empty screen. Credentials set to 755, the php-file has execution bit set, to. Files are in the same directory.

Help appreciated, thank you in advance, i'am lost in information and definitly puzzled. Read through the other topics but since I'am new to this it gives me only little chunks which I was not able to match together. :S Further information:

lighttpd/1.4.31

PHP Version 5.4.36-0+deb7u1
additional config files parsed

/etc/php5/cgi/conf.d/10-pdo.ini, /etc/php5/cgi/conf.d/20-gd.ini, 
/etc/php5/cgi/conf.d/20-mcrypt.ini, /etc/php5/cgi/conf.d/20-mysql.ini, 
/etc/php5/cgi/conf.d/20-mysqli.ini, /etc/php5/cgi/conf.d/20-pdo_mysql.ini 

json support    enabled
json version    1.2.1 

mysql Client API version    5.5.40

答

Change your php file to this:

<?php
  $dbc = mysqli_connect("localhost", "pi", "pi", "boxklima");
  $query = "SELECT date,temp FROM 0414604605ff";
  $data = mysqli_query($dbc, $query);
  $i=0;
  while($row = mysqli_fetch_array($data)) {
     $rows[$i]=array($row['date'],$row['temp']);
     $i++;
  }
  echo json_encode($rows);
?>

In create-graph.html, 'temp' variable is undefined.(You may open JavaScript Console and check for JavaScript errors)This is the reason why that page is blank.

Looking at your data.php file, I think the 'data' variable in callback function of $.getJSON would have structure like following:

[
   [2014-01-01 09:00:00, 12.43]
   [2014-01-02 09:00:00, 13.57]
]

Basically Array of Arrays. So changing your function to this might help:

$.getJSON('data.php', function(data) {
    dataTemp = [];
    for(i=0;i<data.length;i++){
        dataTemp.push(data[i][1]);  // '1' index for getting that temp value. '0' for date.     
    }
    options.series[0].data = dataTemp;
    var chart = new Highcharts.Chart(options);
});

Also Please post the contents of data.php when you open it in web browser.
Also mention the JavaScript Console Error(if you get) when you open create-graph.html after making the changes.

答

You need to convert your string dates to timestamp and set datetime type of xAxis. To help, see strtotime function and apply in to your PHP script.

使用json将数据从mysql解析为highchart

相关推荐