drsqpko5286
2018-04-02 21:13 阅读 147

如何使用Highcharts显示数据库中的历史数据和实时数据

I am having difficulty figuring out how to use Highcharts to get new(live) data from a database.

I have tested the example here and it works great.

I get new data written to the database every 1min. The problem is I can only make it grab the newly inserted data from the database and update the chart every 1min, using the newly inserted data as the new data point. I can't get it to show historical data from the database.

Here is an example of what I'm trying to do.

Here is the code I´m using at the moment.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script type="text/javascript">
  var chart;
 $(document).ready(function() {
     var options = {
        chart: {
          renderTo: 'container',
          type: 'area',
          zoomType: 'x'
        },
        title: {
        },
        xAxis: {
           type: 'datetime'
        },
        yAxis: {
        },
        series: [{
           name: 'Download',
           data: []
       }, {
           name: 'Upload',
           data: []
        }]
     }; 
     $.getJSON('data.php', function(json) {
        data1 = [];
        data2 = [];
        $.each(json, function(key,value) {
        data1.push([value[0], value[1]]);
        data2.push([value[0], value[2]]);
        });

        options.series[0].data = data1;
        options.series[1].data = data2;
        chart = new Highcharts.stockChart(options);
     });
  });
</script>

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

</body>

<script src="https://code.highcharts.com/stock/highstock.js"></script>
<script src="https://code.highcharts.com/highcharts.js"></script>
<script src="https://code.highcharts.com/modules/data.js"></script>
<script src="https://code.highcharts.com/modules/exporting.js"></script>

And here is the code for data.php.

<?php

    $dbhost = 'localhost';
    $dbname = 'highchart';  
    $dbuser = '*******';                  
    $dbpass = '*******'; 

    try{

        $dbcon = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass);
        $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    }catch(PDOException $ex){

        die($ex->getMessage());
    }

    $stmt=$dbcon->prepare("SELECT * FROM trafico");
    $stmt->execute();
    $json = [];
    while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
        extract($row);
        $json[]= [strtotime($time_1m)*1000, (int)$Tx, (int)$Rx];
    }
    echo json_encode($json);
?>

This is the output I get from data.php.

[[1521071984000,1255,91],[1521072190000,1212,92],[1521072241000,1220,93],[ ... ]]

This is the graph I get sample graph

Basically I don't know to how to make the code (this graph) above to update dynamically every 1min with new data points.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    doukui7574 doukui7574 2018-04-04 20:10

    Problem solved.

    This is the code I added:

      events: {
        load: function requestData() {
    
            $.ajax({
                url: 'live.php',
                success: function(points) {
                    var series = chart.series,
                    shift;
    
                    $.each(series, function(i, s) {
                        shift = s.data.length > 1;
                        s.addPoint(points[i], true, true);
                    });
                    setTimeout(requestData, 1000);   
                    chart.redraw(); 
                },
                cache: false
            });
    
        }
        }
    

    Now I have historical data from my DB and it adds new data point every time there's a new entry in de DB without the need to refresh the page.

    Here is is my live.php code:

    <?Php
    header("Content-type: text/json");
    
        $dbhost = 'localhost';
        $dbname = 'highchart';  
        $dbuser = '*******';                  
        $dbpass = '*******'; 
    
    
        try{
    
            $dbcon = new PDO("mysql:host={$dbhost};dbname={$dbname}",$dbuser,$dbpass);
            $dbcon->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
        }catch(PDOException $ex){
    
            die($ex->getMessage());
        }
    
        $stmt=$dbcon->prepare("SELECT * FROM trafico");
        $stmt->execute();
        $json = [];
        while($row=$stmt->fetch(PDO::FETCH_ASSOC)){
            extract($row);
                }
            $json[]= [strtotime($time_1m)*1000, (int)$Tx];
            $json[]= [strtotime($time_1m)*1000, (int)$Rx];
    
        echo json_encode($json);
    ?>
    

    This is the output from live.php:

    [[1522869181000,872],[1522869181000,54]]
    

    and this is how the code looks now

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script type="text/javascript">
    
      var chart;
     $(document).ready(function() {
    
            Highcharts.setOptions({
                time: {
                    timezoneOffset: 3 * 60
                }
            });
    
         var options = {
            chart: {
              renderTo: 'container',
              type: 'area',
              zoomType: 'x',
              events: {
                load: function requestData() {
    
                    $.ajax({
                        url: 'live.php',
                        success: function(points) {
                            var series = chart.series,
                                shift;
    
                            $.each(series, function(i, s) {
                                shift = s.data.length > 1;
                                s.addPoint(points[i], true, true);
                            });
                            setTimeout(requestData, 1000);   
                            chart.redraw(); 
                        },
                        cache: false
                    });
    
                }
                }
            },
            title: {
            },
            xAxis: {
               type: 'datetime'
            },
            yAxis: {
            },
            series: [{
               name: 'Download',
               data: []
           }, {
               name: 'Upload',
               data: []
            }]
         }; 
         $.getJSON('data.php', function(json) {
            data1 = [];
            data2 = [];
            $.each(json, function(key,value) {
            data1.push([value[0], value[1]]);
            data2.push([value[0], value[2]]);
            });
    
            options.series[0].data = data1;
            options.series[1].data = data2;
            chart = new Highcharts.stockChart(options);
         });
      });
    </script>
    
    <body>
     <div id="container" style="min-width: 400px; height: 800px; margin: 0 auto"></div>
    
    </body>
    
    <script src="https://code.highcharts.com/stock/highstock.js"></script>
    <script src="https://code.highcharts.com/highcharts.js"></script>
    <script src="https://code.highcharts.com/modules/data.js"></script>
    <script src="https://code.highcharts.com/modules/exporting.js"></script>
    
    点赞 评论 复制链接分享

相关推荐