Highcharts data series issue with ajax/json and PHP

Question

This is my first request here, and I've read many of the other related posts on this same issue, but I'm STILL getting stuck and pretty much at my wits end on this... So any help is much appreciated!

I've got the following Highcharts object on Page1.php, and I'm using AJAX to get data from Page2.php on page load as well as when a dropdown option is changed.

(truncated for ease of reading):

$(document).ready(function() {

   var e = document.getElementById("selOption"); //<--- This is the dropdown
   var domText = e.options[0].text;
   var domID = e.options[e.selectedIndex].value;

   var options = {
      chart: {
         renderTo: 'linechart',
         type: 'line'
      },
      title: {
         text: 'Title for ' + domText
      },
      subtitle: {
         text: ''   
      },
      xAxis: {
         type: 'datetime',
         dateTimeLabelFormats: {
            month: '%b %e, %Y',
            year: '%Y'
         }
      },
      yAxis: {
         title: {
            text: 'Important Values'
         },
         reversed: true,
         min: 0,
         max: 100
      },
      tooltip: {
         formatter: function() {
                   return '<b>'+ this.series.name +'</b><br/>'+
               Highcharts.dateFormat('%b %e', this.x) +': '+ this.y;
         }
      },
      series: []

};

$.get('Page2.php?domID=' + domID,function(data) {
    $.each(data, function(key,value) {
        //var series = {};
        //series.name.push(value);
        //series.data.push([value]);
        options.series.push(data);
        //alert(data);
    });

    var linechart = new Highcharts.Chart(options);
});

});

Page2.php has the following sending back the json:

$sqlSelect = "SELECT Item1,Item2,Item3 FROM... ";
$result = mysql_query($sqlSelect);

while ($item = mysql_fetch_assoc($result)) {            
    $name = $item['Item1'];
    $date = str_replace("-",",",$item['Item2']);
    $pos = $item['Item3'];

    $arr = array("name"=>$name,"data"=>"[Date.UTC(".$date."), ".$pos." ],");
    echo json_encode($arr);         
}

My json return looks like this:

{"name":"Item1","data":"[Date.UTC(2011,11,08), 4 ],"}
{"name":"Item1","data":"[Date.UTC(2011,11,08), 2 ],"}

When my chart loads, it fills in 135 Series names (?!?!?!) at the bottom and doesn't appear to show the points on the line graph.

If I remove the double quotes and hard code the result into the series array, it works great (though I noticed the example doesn't appear to have a comma between the objects.

THANK YOU for all help ...especially quick replies! ;-)

Answer 1

SOLVED:

I found an answer that finally worked! Frustrating that it took me several days because it wasn't well documented by Highcharts (and/or maybe my understanding of jQuert and JavaScript is still at a novice level).

The answer is do not send the X/Y data as a preformed array in your json object, but instead simply send the numbers that make up the Date (x value) and the Value (y value) as a list of comma separated values in a json object, and then do the parsing and pushing on the client end.

For example: I originally was trying to get my PHP to send something like

[{"name":"Name 1","data":["[Date.UTC(2011,11,08), 4 ]","[Date.UTC(2011,11,09), 4 ]","[Date.UTC(2011,11,10), 4 ]","[Date.UTC(2011,11,11), 4 ]","[Date.UTC(2011,11,14), 3 ]"]}

but what I really needed to do was send something like

[{"name":"Name 1","data":["2011,11,08, 4","2011,11,09,4"....`

The first step is to make sure you have the "series" option set to an empty array series: []

(I mention this because I've seen other answers where this was done differently).

Then in your getJSON function, you need to create a new object for each object you're pulling in, which also includes an empty "data" array (see "var series" below).

Then nest a few $.each loops to parse and push the data into their necessary arrays, and populate the "name" of each object:

    $.getJSON('http://exmaple.com/getdata.php?ID=' + id, function(data) {           
        $.each(data, function(key,value) {
            var series = { data: []};
            $.each(value, function(key,val) {
                if (key == 'name') {
                    series.name = val;
                }
                else
                {
                    $.each(val, function(key,val) {
                        var d = val.split(",");
                        var x = Date.UTC(d[0],d[1],d[2]);
                        series.data.push([x,d[3]]);
                    });
                }
            });
            options.series.push(series);
        });

After the above code, you need to instantiate the chart:

var chart = new Highcharts.Chart(options);

And that should be it!

Hopefully somebody else finds this answer useful and doesn't need to beat themselves over the head for days trying to figure it out. :-)

Answer 2

Since Javascript UTC method will return with an Integer, it's just better if you just pass the Unix Timestamp as an integer when you're generating the JSON on the serverside instead trying to pass a function (UTC.Date) in the JSON - that makes the entire JSON invalid!

So

Instead of this

while ($item = mysql_fetch_assoc($result)) {            
    $name = $item['Item1'];
    $date = str_replace("-",",",$item['Item2']);
    $pos = $item['Item3'];

    $arr = array("name"=>$name,"data"=>"[Date.UTC(".$date."), ".$pos." ],");
    echo json_encode($arr);         
}

Do this:

while ($item = mysql_fetch_assoc($result)) {            
    $name = $item['Item1'];
    $unix_date = date("Y-m-d", strtotime($item['Item2']));
    $pos = $item['Item3'];

    $arr = array("name"=>$name,"data"=>"[".($unix_date*1000).", ".$pos." ],");
    echo json_encode($arr);         
}

Please note we're multiplying the PHP unix timestamp by 1000 to make it JS compatible.

Reference: http://www.w3schools.com/jsref/jsref_utc.asp

Answer 3

Just checked with some sample code for Highcharts, series needs to be an array of JSON objects each containing {"name":"Item1","data":"[Date.UTC(2011,11,08), 4 ],"} etc.

The main problem is your output from page2, is not an array.

You'll need to fix your $.each first, you need to push value, not data:

$.each(data, function(key,value) {
    options.series.push(value);
});

This will properly set each item in series to the full json object.

Then to fix the output:

$output = [];
while ($item = mysql_fetch_assoc($result)) {            
    $name = $item['Item1'];
    $date = str_replace("-",",",$item['Item2']);
    $pos = $item['Item3'];

    //I don't think there's supposed to be a comma after this square bracket
    $arr = array("name"=>$name,"data"=>"[Date.UTC(".$date."), ".$pos." ]");
    array_push($output, json_encode($arr));     
}

echo "[";
foreach($output as $val){
    echo $val;
    if($val != end($output)) echo ",";
}
echo "]";

And that should do it.

Answer 4

Checked in PHP5.5

You can simply add this into the json with the data series

$date = '31-12-2015 00:00:00';
$datetimeUTC = (strtotime($date) * 1000);
$data[] = [$datetimeUTC, (float) $value];

Answer 5

I came across the problem of sending timestamps produced by mktime() because highcharts expects UTC time while PHP Timestamp is not UTC. The data could be shown on a wrong day in the chart, so you also have to consider the time zone.

function _convertToUTC($timeStamp) {
    return (int)$timeStamp + (int)date('Z', $timeStamp);
}

Code Example would be:

$serie = array(
    'name' => 'title',
    'data' => array(
        // multiply timestamp by 1000 to get milliseconds
        array(_convertToUTC(mktime(0,0,0,1,1,2016)) * 1000, 15),
        array(_convertToUTC(mktime(0,0,0,2,1,2016)) * 1000, 18),
        array(_convertToUTC(mktime(0,0,0,3,1,2016)) * 1000, 13),
    ),
);
echo json_encode($serie);