Static chart with timestamp on x-axis

Question

I want to create a static chart of values pulled out of a MySQL database. The chart format would be (x axis : dd/mm/yy hh:mm:ss (corresponding to timestamp of mysql database)) and y-axis would be a double value. I am able to successfully retrieve these values from MySql database.I want help plotting them by ZingChart

Answer 1

Nikita.

Once you've retrieved your values from your MySQL database, you'll want to convert the MySQL date values in to Unix time in milliseconds. I've populated a $date array with the MySQL date values, and iterated over the array, calling strtotime to first convert to Unix time, and multiplying by 1000 to convert to milliseconds. In order to be able to directly modify array elements within the loop, I've also preceded $value with to assign by reference.

foreach ($date as &$value){
  $value = strtotime( $value ) * 1000;
}

So now that the values in the $date array have been converted to the proper format, it's time to create a JavaScript array from the PHP array. This can be done using join():

var dateValues = [<?php echo join($date, ',') ?>];

The resulting array looks like this:

var dateValues = [1356994800000,1357081200000,1357167600000, ... ];

To use this array in ZingChart, use the dateValues variable with "values" in the scale-x object. To convert the Unix time values back to dates in ZingChart, add the "transform" object, and set it to "type":"date".

"scale-x":{
  "values": dateValues,
  "transform":{
    "type":"date",
    "item":{
      "visible":false
    }
  }
},
...

That takes care of the scale. To get your other values in the chart, you do pretty much the same thing. Convert the PHP arrays into JavaScript arrays, and use the array variable in your chart JSON.

With the PHP $series array:

var seriesValues = [<?php echo join($series, ',') ?>];

In your chart JSON:

"series":[
  {
    "values":seriesValues
  }
]

I've compiled all of this in to a Github Gist for you. Let me know if you have any questions!

Answer 2

Check out our demos repo on GitHub. We have a tutorial specifically about connecting to a MySQL database with PHP.

There's a step-by-step walkthrough on our site, too.

If you share your JSON or more details about it, I can help you with putting your chart together.

I'm on the ZingChart team. Please let me know if you have other questions.