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]]

Bhesh Gurung · Accepted Answer · 2011-10-14 19:35:21Z

3

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 -

Javascript (client-side).
In your PHP code
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.

edited Oct 14, 2011 at 19:35

answered Oct 14, 2011 at 13:56

Bhesh Gurung

51.1k23 gold badges96 silver badges147 bronze badges

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

4 Comments

Janine Kroser Over a year ago

If I do so, nothing will be displayed anymore.

Janine Kroser Over a year ago

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 Over a year ago

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 Over a year ago

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

Collectives™ on Stack Overflow

Highchart - Display JSON Data - MYSQL / PHP

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related