Highcharts async Server Loading with multiple series

Question

i'm trying to use a the lazy loading of Highcharts following its example

http://www.highcharts.com/stock/demo/lazy-loading

and their php code

https://github.com/highslide-software/highcharts.com/blob/master/samples/data/from-sql.php

but the only thing that i get is a blank empty chart with 2 days of 2011(the example data). My php code :

<?php


// get the parameters

$callback = $_GET['callback'];
if (!preg_match('/^[a-zA-Z0-9_]+$/', $callback)) {
        die('Invalid callback name');
}

$start = $_GET['start'];
if ($start && !preg_match('/^[0-9]+$/', $start)) {
        die("Invalid start parameter: $start");
}

$end = $_GET['end'];
if ($end && !preg_match('/^[0-9]+$/', $end)) {
        die("Invalid end parameter: $end");
}
if (!$end) $end = mktime() * 1000;



// connect to MySQL
$link = mysqli_connect('localhost:3306', 'root', 'elektra','telegestione');
// set UTC+1 time
//mysql_query("SET time_zone = '+01:00'");

// set some utility variables
$range = $end - $start;
//$startTime = gmstrftime('%Y-%m-%d %H:%M:%S', $start / 1000);
$startTime= date('Y-m-d H:i:s', strtotime(gmstrftime('%Y-%m-%d %H:%M:%S', $start / 1000) . ' - 1 day'));
//$endTime = gmstrftime('%Y-%m-%d %H:%M:%S', $end / 1000);
$endTime= date('Y-m-d H:i:s', strtotime(gmstrftime('%Y-%m-%d %H:%M:%S', $end / 1000) . ' + 0 day'));

// find the right table
// two days range loads minute data
if ($range < 2 * 24 * 3600 * 1000) {
        if (!$link)     {
                        die('Could not connect: ' . mysqli_error());};  
                        echo "minute data2" ;
            $result = mysqli_query($link,"select 1000*unix_timestamp(q1.time) as time, q1.kwhg, q1.kwhc, q1.kwhi, q1.kwhfm, q2.test from (
SELECT
      (t1.dt) AS time,(case
     when t1.value>t2.value AND t2.value>0
          then abs(t1.value - t2.value)
     when t1.value>t2.value AND t2.value=0
          then t2.value
          else t1.value END)kwhg, 
     (case
     when t1.value1>t2.value1 AND t2.value1>0
          then abs(t1.value1 - t2.value1)
     when t1.value1>t2.value1 AND t2.value1=0
          then t2.value1
     when t1.value1=t2.value1 AND t1.value1>0 
        then abs(t1.value1 - t2.value1)
      else 0 END) kwhc, 
      (case
     when t1.value2>t2.value2 AND t2.value2>0
          then abs(t1.value2 - t2.value2)
     when t1.value2>t2.value2 AND t2.value2=0
          then t2.value2
     when t1.value2=t2.value2 AND t1.value2>0 
        then abs(t1.value2 - t2.value2)
      else 0 END)kwhi,
    (case
     when t1.value3>t2.value3 AND t2.value3>0
          then abs(t1.value3 - t2.value3)
     when t1.value3>t2.value3 AND t2.value3=0
          then t2.value3
     when t1.value3=t2.value3 AND t1.value3>0 
        then abs(t1.value3 - t2.value3)
      else 0 END) kwhfm
    FROM 
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt),hour(dt)) t1
        JOIN
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt),hour(dt)) t2
        on t1.dt = t2.dt + interval 15 minute) as q1
JOIN
    (select dataora, AVG(Test) as test from temperature where dataora between '$startTime' and '$endTime' GROUP BY date(dataora),hour(dataora))  as q2
    ON q1.time=q2.dataora");

 //one week range loads hourly data
} elseif ($range < 7 * 24 * 3600 * 1000) {
        if (!$link)     {
            die('Could not connect: ' . mysqli_error());};  
            echo "hourly data1" ;
    $result = mysqli_query($link,"select 1000*unix_timestamp(q1.time) as time, q1.kwhg, q1.kwhc, q1.kwhi, q1.kwhfm, q2.test from (
SELECT
      (t1.dt) AS time,(case
     when t1.value>t2.value AND t2.value>0
          then abs(t1.value - t2.value)
     when t1.value>t2.value AND t2.value=0
          then t2.value
          else t1.value END)kwhg, 
     (case
     when t1.value1>t2.value1 AND t2.value1>0
          then abs(t1.value1 - t2.value1)
     when t1.value1>t2.value1 AND t2.value1=0
          then t2.value1
     when t1.value1=t2.value1 AND t1.value1>0 
        then abs(t1.value1 - t2.value1)
      else 0 END) kwhc, 
      (case
     when t1.value2>t2.value2 AND t2.value2>0
          then abs(t1.value2 - t2.value2)
     when t1.value2>t2.value2 AND t2.value2=0
          then t2.value2
     when t1.value2=t2.value2 AND t1.value2>0 
        then abs(t1.value2 - t2.value2)
      else 0 END)kwhi,
    (case
     when t1.value3>t2.value3 AND t2.value3>0
          then abs(t1.value3 - t2.value3)
     when t1.value3>t2.value3 AND t2.value3=0
          then t2.value3
     when t1.value3=t2.value3 AND t1.value3>0 
        then abs(t1.value3 - t2.value3)
      else 0 END) kwhfm
    FROM 
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt),hour(dt)) t1
        JOIN
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt),hour(dt)) t2
        on t1.dt = t2.dt + interval 1 hour) as q1
JOIN
    (select dataora, AVG(Test) as test from temperature where dataora between '$startTime' and '$endTime' GROUP BY date(dataora),hour(dataora))  as q2
    ON q1.time=q2.dataora");

// one month range loads hourly data
} elseif ($range < 31 * 24 * 3600 * 1000) {
        if (!$link)     {
            die('Could not connect: ' . mysqli_error());};  
            echo "hourly data2" ;
    $result = mysqli_query($link,"select 1000*unix_timestamp(q1.time) as time, q1.kwhg, q1.kwhc, q1.kwhi, q1.kwhfm, q2.test from (
SELECT
      (t1.dt) AS time,(case
     when t1.value>t2.value AND t2.value>0
          then abs(t1.value - t2.value)
     when t1.value>t2.value AND t2.value=0
          then t2.value
          else t1.value END)kwhg, 
     (case
     when t1.value1>t2.value1 AND t2.value1>0
          then abs(t1.value1 - t2.value1)
     when t1.value1>t2.value1 AND t2.value1=0
          then t2.value1
     when t1.value1=t2.value1 AND t1.value1>0 
        then abs(t1.value1 - t2.value1)
      else 0 END) kwhc, 
      (case
     when t1.value2>t2.value2 AND t2.value2>0
          then abs(t1.value2 - t2.value2)
     when t1.value2>t2.value2 AND t2.value2=0
          then t2.value2
     when t1.value2=t2.value2 AND t1.value2>0 
        then abs(t1.value2 - t2.value2)
      else 0 END)kwhi,
    (case
     when t1.value3>t2.value3 AND t2.value3>0
          then abs(t1.value3 - t2.value3)
     when t1.value3>t2.value3 AND t2.value3=0
          then t2.value3
     when t1.value3=t2.value3 AND t1.value3>0 
        then abs(t1.value3 - t2.value3)
      else 0 END) kwhfm
    FROM 
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt),hour(dt)) t1
        JOIN
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt),hour(dt)) t2
        on t1.dt = t2.dt + interval 1 hour) as q1
JOIN
    (select dataora, AVG(Test) as test from temperature where dataora between '$startTime' and '$endTime' GROUP BY date(dataora),hour(dataora))  as q2
    ON q1.time=q2.dataora");

// one year range loads daily data
} elseif ($range < 15 * 31 * 24 * 3600 * 1000) {
        if (!$link)     {
            die('Could not connect: ' . mysqli_error());};  
            echo "daily data2" ;
    $result = mysqli_query($link,"select 1000*unix_timestamp(q1.time) as time, q1.kwhg, q1.kwhc, q1.kwhi, q1.kwhfm, q2.test from (
SELECT
      (t1.dt) AS time,(case
     when t1.value>t2.value AND t2.value>0
          then abs(t1.value - t2.value)
     when t1.value>t2.value AND t2.value=0
          then t2.value
          else t1.value END)kwhg, 
     (case
     when t1.value1>t2.value1 AND t2.value1>0
          then abs(t1.value1 - t2.value1)
     when t1.value1>t2.value1 AND t2.value1=0
          then t2.value1
     when t1.value1=t2.value1 AND t1.value1>0 
        then abs(t1.value1 - t2.value1)
      else 0 END) kwhc, 
      (case
     when t1.value2>t2.value2 AND t2.value2>0
          then abs(t1.value2 - t2.value2)
     when t1.value2>t2.value2 AND t2.value2=0
          then t2.value2
     when t1.value2=t2.value2 AND t1.value2>0 
        then abs(t1.value2 - t2.value2)
      else 0 END)kwhi,
    (case
     when t1.value3>t2.value3 AND t2.value3>0
          then abs(t1.value3 - t2.value3)
     when t1.value3>t2.value3 AND t2.value3=0
          then t2.value3
     when t1.value3=t2.value3 AND t1.value3>0 
        then abs(t1.value3 - t2.value3)
      else 0 END) kwhfm
    FROM 
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt)) t1
        JOIN
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt)) t2
        on t1.dt = t2.dt + interval 1 day) as q1
JOIN
    (select dataora, AVG(Test) as test from temperature where dataora between '$startTime' and '$endTime' GROUP BY date(dataora))  as q2
    ON q1.time=q2.dataora");

// greater range loads monthly data
} else {
        if (!$link)     {
            die('Could not connect: ' . mysqli_error());};
                echo "monthly data2" ;
    $result = mysqli_query($link,"select 1000*unix_timestamp(q1.time) as time, q1.kwhg, q1.kwhc, q1.kwhi, q1.kwhfm, q2.test from (
SELECT
      (t1.dt) AS time,(case
     when t1.value>t2.value AND t2.value>0
          then abs(t1.value - t2.value)
     when t1.value>t2.value AND t2.value=0
          then t2.value
          else t1.value END)kwhg, 
     (case
     when t1.value1>t2.value1 AND t2.value1>0
          then abs(t1.value1 - t2.value1)
     when t1.value1>t2.value1 AND t2.value1=0
          then t2.value1
     when t1.value1=t2.value1 AND t1.value1>0 
        then abs(t1.value1 - t2.value1)
      else 0 END) kwhc, 
      (case
     when t1.value2>t2.value2 AND t2.value2>0
          then abs(t1.value2 - t2.value2)
     when t1.value2>t2.value2 AND t2.value2=0
          then t2.value2
     when t1.value2=t2.value2 AND t1.value2>0 
        then abs(t1.value2 - t2.value2)
      else 0 END)kwhi,
    (case
     when t1.value3>t2.value3 AND t2.value3>0
          then abs(t1.value3 - t2.value3)
     when t1.value3>t2.value3 AND t2.value3=0
          then t2.value3
     when t1.value3=t2.value3 AND t1.value3>0 
        then abs(t1.value3 - t2.value3)
      else 0 END) kwhfm
    FROM 
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt),hour(dt)) t1
        JOIN
        (SELECT (dataora) dt, MAX(kwhg) value, MAX(kwhc) value1, MAX(kwhi) value2, MAX(kwhfm) value3  FROM misure where dataora between '$startTime' and '$endTime' GROUP BY date(dt),hour(dt)) t2
        on t1.dt = t2.dt + interval 1 month) as q1
JOIN
    (select dataora, AVG(Test) as test from temperature where dataora between '$startTime' and '$endTime' GROUP BY date(dataora),hour(dataora))  as q2
    ON q1.time=q2.dataora");
};

$rows = array();
while ($row = mysqli_fetch_assoc($result)) {
        //echo $a= "hello";
        extract($row);

        $rows[] = "[$time,$test,$kwhg,$kwhc,$kwhi,$kwhfm]";
                                            }

// print it
header('Content-Type: text/javascript');
//echo $range;
//echo $startTime;
//echo $end;
echo "/* console.log(' range = $range, start = $start, end = $end, startTime = $startTime, endTime = $endTime '); */";
echo $callback ."([\n" . join(",\n", $rows) ."\n]);";

?>

The output data should be correct for the highcharts, as indicated by the demo, but i can't initialize the chart or display any series...

$(function() {

    // See source code from the JSONP handler at https://github.com/highslide-software/highcharts.com/blob/master/samples/data/from-sql.php
    $.getJSON('grafico_nuovo.php?callback=?', function(data) {

        // Add a null value for the end date 
        data = [].concat(data, [[Date.GMT(2013, 9, 14, 19, 59), null, null, null, null]]);

        // create the chart
        $('#container').highcharts('StockChart', {
            chart : {
                type: 'spline',
                zoomType: 'xy'
            },

            navigator : {
                adaptToUpdatedData: false,
                series : {
                    data : data
                }
            },

            scrollbar: {
                liveRedraw: false
            },

            title: {
                text: 'AAPL history by the minute from 1998 to 2011'
            },

            subtitle: {
                text: 'Displaying 1.7 million data points in Highcharts Stock by async server loading'
            },

            rangeSelector : {
                buttons: [{
                    type: 'hour',
                    count: 1,
                    text: '1h'
                }, {
                    type: 'day',
                    count: 1,
                    text: '1d'
                }, {
                    type: 'month',
                    count: 1,
                    text: '1m'
                }, {
                    type: 'year',
                    count: 1,
                    text: '1y'
                }, {
                    type: 'all',
                    text: 'All'
                }],
                inputEnabled: true, // it supports only days
                selected : 2 // day
            },

            xAxis : {
                events : {
                    afterSetExtremes : afterSetExtremes
                },
                minRange: 3600 * 1000 // one hour
            },

            series : [{
                data : data,
                dataGrouping: {
                    enabled: false
                }
            }]
        });
    });
});


/**
 * Load new data depending on the selected min and max
 */
function afterSetExtremes(e) {

    var currentExtremes = this.getExtremes(),
        range = e.max - e.min,
        chart = $('#container').highcharts();

    chart.showLoading('Loading data from server...');
    $.getJSON('grafico_nuovo.php?start='+ Math.round(e.min) +
            '&end='+ Math.round(e.max) +'&callback=?', function(data) {

        chart.series[0].setData(data[0]);
        chart.hideLoading();
    });

}

thanks for any help or suggestion Giorgio

Sebastian Bochan · Accepted Answer · 2014-01-24 10:42:49Z

3

What echo $callback ."([\n" . join(",\n", $rows) ."\n]);"; returns? I advice to transform your array to json by json_encode() and then use in javascript.

EDIT:

You have no access to chart in you scirpt, morever You can use setData only on exsiting serie, not on series which not exist like chart.series[2]. You should to call addSeries

 val1 = [];
    val2 = [];
    val3 = [];
    val4 = [];
    val5 = [];
    $.each(data, function (key, value) {
        val1.push([value[0], value[1]]);
        val2.push([value[0], value[2]]);
        val3.push([value[0], value[3]]);
        val4.push([value[0], value[4]]);
        val5.push([value[0], value[5]]);
    });
chart.series[0].setData(val1);
    chart.addSeries({
        data: val2
    });

    chart.addSeries({
        data: val3
    });

    chart.addSeries({
        data: val4
    });

    chart.addSeries({
        data: val5
    });



    chart.hideLoading();

http://jsfiddle.net/4knAX/2/

edited Jan 24, 2014 at 10:42

answered Jan 15, 2014 at 12:17

Sebastian Bochan

37.6k3 gold badges51 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

21 Comments

Ashkrimn Over a year ago

this is the return of the callback: /* console.log(' start = 1318621440000, end = 1318625040000, startTime = 2011-10-14 19:44:00, endTime = 2011-10-14 20:44:00 '); */jQuery18208912840717552396_1389783951004([ ]); in the highcharts example this callback works, in my case...no! :( i will try to encode everything in json, i hope this might help, thanks

Ashkrimn Over a year ago

this is the return of callback now: daily data2/* console.log(' range = 2680999000, start = 1383264000000, end = 1385944999000, startTime = 2013-10-31 00:00:00, endTime = 2013-12-02 00:43:19 '); */jQuery18208912840717552396_1380585600([ [1383260400,14.45208,32.50,7.40,1.80,18.10], [1383346800,14.49792,28.00,4.00,2.10,17.90], [1383433200,16.25417,27.80,3.90,2.00,17.40], [1383519600,14.10208,64.90,10.20,20.90,27.10], [1383606000,14.67083,62.60,8.20,20.60,28.70], and so on... ]);

Ashkrimn Over a year ago

Now it works, but i obtain only a blank chart, because the date is stuck to 1970...and i can't change it by range selector or datapicker, they don't works

Sebastian Bochan Over a year ago

You need to multiply your timestamps by 1000, because it is unix timestamps.

Ashkrimn Over a year ago

Yes, now it works, the navigator show the first series of data but anything is shown in the graph...but i' coming closer

|

Collectives™ on Stack Overflow

Highcharts async Server Loading with multiple series

1 Answer 1

21 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

21 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related