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

Question

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?

don't use json, build an array from the result set. and instead of timestamp use UNIX_TIMESTAMP(timestamp) — nbk
– nbk, Commented Jul 26, 2020 at 10:10
Don't json_encode($array1) right away, loop over the results to create a new array with the desired structure, json_encode() the new array — user8034901
– user8034901, Commented Jul 26, 2020 at 10:17
Are you calling the PHP with AJAX to get the JSON result or is the chart data being output on page load? — Mark
– Mark, Commented Jul 26, 2020 at 12:16
What I have is a Wordpress site with a php template and a custom table in MySQL. I am not using Ajax. Although I guess if I want to create buttons for adjusting the query I would need to. For now I just try figuring things it out step by step. — Laura
– Laura, Commented Jul 28, 2020 at 8:52

Mark · Accepted Answer · 2020-07-26 12:31:18Z

1

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.

answered Jul 26, 2020 at 12:31

Mark

1,0391 gold badge6 silver badges8 bronze badges

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

7 Comments

Laura Over a year ago

Thank you. It does seem to create a graph, or at least puts the dots and the y-axis. The x-axis says 5-times 'Jan 19' somehow. Also it doesn't connect the points like the original example. Although I don't really need that. Also - the last conversion is done in javascript - would also be possible to change the values in php? Are there PROS or CONS for this? And how about if I need to do some math operations - including an if statement - on the values? e.g. values from 0-360, IF >180 do 360 - value.

Laura Over a year ago

Would it matter if I have not 8 but 2000 query results? And probably 5 graphs (from other columns as well)?

Laura Over a year ago

While testing I removed 'value' from within the () in:return moment().format('YYYY-MM-DD HH:mm'); and changed the date+time format: it takes the current time as a label.

Mark Over a year ago

I would say it hasn't joined up the dots and has the other problems because you need to supply more options to the ChartList.Line setup, see their docs. You have to do the date conversion in JavaScript because based on your criteria/example it needed a JavaScript Date object for x. For all other filters, conditions, etc, do that in PHP. I don't know the ChartList plugin so cannot say how it would handle 2000 points but the PHP side should be fine. I cannot work out what your last comment meant. If my answer worked for you I would appreciate if you could accept it.

Laura Over a year ago

I guess you were right about the javascript Date object. The x-axis isn;'t working still. I also think it has to be in Unix format in miliseconds, I believe it is now in seconds. I will do some more testing.

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related