Highchart - Display JSON Data - MYSQL / PHP

Question

I am trying to display JSON DATA in a Highchart (http://highcharts.com)

The problem are the data for the xAxis. I am really confused how to get the date format so that it is displayed in the xAxis.

I found out that I have to convert the date data from the DB to milliseconds.

Somebody got an idea how to realize that?

This is my javascript:

    var chart;

 chart = new Highcharts.Chart({
    chart: {
            renderTo: 'container',
            defaultSeriesType: 'spline',
            events: {
                load: requestData
            }},
    xAxis: {
         type: 'datetime'
      },
      yAxis: {
         title: {
            text: 'Value'
         },
         plotLines: [{
            value: 0,
            width: 1,
            color: '#808080'
         }]
      },

    series: [{
         name: 'Random data',
         data: []
        }]
});



function requestData() {
    $.ajax({
        url: '../controller/charter/data.php',
        datatype: "json",
        success: function(data) {

            alert(data);

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

        },
        cache: false
    });
}

This is the PHP which produces the JSON:

 <?php
header("Content-type: text/json");
// connect to the database
$dbhost = "localhost";
$dbuser = "cccccc";
$dbpassword = "ccccccc";
$database = "ccccccccc";
$tablename = "ccccccc";
$db = mysql_connect($dbhost, $dbuser, $dbpassword)
or die("Connection Error: " . mysql_error());

mysql_select_db($database) or die("Error conecting to db.");
$result = mysql_query("SELECT COUNT(*) AS count FROM $tablename");
$row = mysql_fetch_array($result,MYSQL_ASSOC);

$SQL = "SELECT aed,savedate FROM $tablename ORDER BY savedate";

$result = mysql_query( $SQL ) or die("Couldn?t execute query.".mysql_error());

$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {

    $row[aed] = (int) $row[aed];

    $rows[$i]=array($row[savedate],$row[aed]);

    $i++;
}

echo json_encode($rows);

;
?>

The JSON data:

[["2011-03-20 18:53:47",40],["2011-03-21 18:53:47",300],["2011-03-22 18:53:47",450],["2011-03-23 18:53:47",40],["2011-03-24 18:53:47",300],["2011-03-25 18:53:47",450],["2011-03-26 18:53:47",40],["2011-03-29 18:53:47",120],["2011-03-29 18:53:47",80],["2011-03-29 18:53:47",300],["2011-03-29 18:53:47",450],["2011-03-29 18:53:47",40],["2011-03-29 18:53:47",100],["2011-03-29 18:53:47",120],["2011-03-29 18:53:47",80],["2011-03-29 18:53:47",300],["2011-03-29 18:53:47",450],["2011-03-29 18:53:47",100],["2011-03-29 18:53:47",40],["2011-03-29 18:53:47",120],["2011-03-29 18:53:47",80],["2011-03-29 18:53:47",300],["2011-03-29 18:53:47",450],["2011-03-29 18:53:47",40],["2011-03-29 18:53:47",100],["2011-03-29 18:53:47",120],["2011-03-29 18:53:47",80],["2011-03-29 18:53:47",300],["2011-03-29 18:53:47",450],["2011-03-29 18:53:47",40],["2011-03-29 18:53:47",300],["2011-03-29 18:53:47",450],["2011-03-29 18:53:47",40],["2011-03-29 18:53:47",300],["2011-03-29 18:53:47",450],["2011-03-29 18:53:47",100]]

Solution

First of try parsing the data (JSON) before passing it as an argument to setData -

chart.series[0].setData( eval( '(' + data + ')' ) );

Update:

[["2011-03-20 18:53:47",40],["2011-03-21 18:53:47",300],...]

"2011-03-20 18:53:47" is not valid x-value for a datetime series. It must ba a number that represents the date in milliseconds.

You can fix it by making change in one of the followings -

1. Javascript (client-side).
2. In your PHP code
3. In your SQL query.

Do that either in your SQL query or PHP, so that you won't have to mess with Javascript and that your JSON will look something like -

[[1318605385652, 40],[1318605385652,300],...]

Then after that just do eval.

Comments

[Janine Kroser]

If I do so, nothing will be displayed anymore.

[Janine Kroser]

Yes I updated the JSON data and I solved the problem before...but now I want to convert my datetime from database into milliseconds...any idea? :-)

[Janine Kroser]

Thanks for the support: The answer was as you mensioned to convert my datetime to millisec. $rows[$i]=array(strtotime($row[savedate])*1000 ,$row[stand]); Is it really necessary to make a eval?

[Bhesh Gurung]

@JanineKroser: Because the setData function takes Javascript array as an argument not JSON so just to parse.