CODEWITHSUNDEEP
javascriptphpmysqlarraysgraph
Laura
Laura

Reputation: 17

How to convert array result of mysql query for use in chartist.js

I have a query

$array1 = $wpdb->get_results( "SELECT timestamp,temp FROM $table_name ORDER BY id desc LIMIT 8", ARRAY_A );
$data1 = json_encode($array1);
echo $data1;

result echoed like this:

[
  {"timestamp":"2020-07-26 09:50:25","temp":"26.31"},
  {"timestamp":"2020-07-26 09:40:29","temp":"26.37"},
  {"timestamp":"2020-07-26 09:30:33","temp":"26.31"},
  {"timestamp":"2020-07-26 09:20:37","temp":"26.43"},
  {"timestamp":"2020-07-26 09:19:56","temp":null},
  {"timestamp":"2020-07-26 08:54:54","temp":"26.37"},
  {"timestamp":"2020-07-26 08:44:58","temp":"26.18"},
  {"timestamp":"2020-07-26 08:35:02","temp":"26.25"}
]

which I would like to use as input for a graph.

The template given for chartist.js (including also moments.js) is like so:

var chart = new Chartist.Line('.ct-chart', {  series: [    {
  name: 'series-1',
  data: [
    {x: new Date(143134652600), y: 53},
    {x: new Date(143234652600), y: 40},
    {x: new Date(143340052600), y: 45},
    {x: new Date(143366652600), y: 40},
    {x: new Date(143410652600), y: 20},
    {x: new Date(143508652600), y: 32},
    {x: new Date(143569652600), y: 18},
    {x: new Date(143579652600), y: 11}
  ]
},

And so on. How to convert this array in php / replace timestamp by x / temp by y and use it (in a loop?) within javascript?

Upvotes: 1

Views: 310

Answers (1)

Mark
Mark

Reputation: 1039

In the sql, use UNIX_TIMESTAMP to get the date in the correct format and alias the columns as x and y.

$array1 = $wpdb->get_results( "SELECT UNIX_TIMESTAMP(timestamp) as x,temp as y FROM $table_name ORDER BY id desc LIMIT 8", ARRAY_A );
$data1 = json_encode($array1);

JSON encode the data as you have done.

Depending on how you receive that JSON into JavaScript, loop through the array and convert the timestamp into a date and the value into a float. The following assumes the PHP is echo'd into the JavaScript

var data = JSON.parse('<?php echo $data1; ?>');
data.forEach(function(row){
  row.x = new Date(parseInt(row.x));
  row.y = parseFloat(row.y);
});

Then use the data in your chart

var chart = new Chartist.Line('.ct-chart', {  series: [    {
    name: 'series-1',
    data: data
  },

If you fetch the JSON string from PHP via AJAX then simply pass in the result string and parse it the same way.

Upvotes: 1

Related Questions