dongxing2710
2015-01-15 22:47 阅读 53

使用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 
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

2条回答 默认 最新

  • 已采纳
    dqvzfp6468 dqvzfp6468 2015-01-15 23:47

    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.

    点赞 评论 复制链接分享
  • dtsc14683 dtsc14683 2015-01-16 10:06

    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.

    点赞 评论 复制链接分享

相关推荐