I have a MariaDB with articles and each article has a date. I want to display a bar chart with number of articles per date.
I get the data with this code:
$statement = $pdo->prepare(
" SELECT
Date(a_timestamp) AS thedate,
count(a_timestamp) AS counted
FROM `$dbtable`
WHERE Year(a_timestamp) = '2019'
GROUP BY thedate
ORDER BY thedate
");
$statement->execute();
$result = $statement->fetchAll();
var_dump($result);
print json_encode($result);
With the result:
array(2) {
[0]=>
array(4) {
["thedate"]=>
string(10) "2019-06-15"
[0]=>
string(10) "2019-06-15"
["counted"]=>
string(2) "30"
[1]=>
string(2) "30"
}
[1]=>
array(4) {
["thedate"]=>
string(10) "2019-06-16"
[0]=>
string(10) "2019-06-16"
["counted"]=>
string(2) "29"
[1]=>
string(2) "29"
}
}
[{"thedate":"2019-06-15","0":"2019-06-15","counted":"30","1":"30"},{"thedate":"2019-06-16","0":"2019-06-16","counted":"29","1":"29"}]
I need for Chart.js something likes this (https://www.chartjs.org/docs/latest/getting-started/):
labels: ['January', 'February', 'March', 'April', 'May', 'June', 'July'],
data: [0, 10, 5, 2, 20, 30, 45]
How can i transform the result to make it fit for Chart.js?
I think there are many ways: Changing the PDO result, transforming the resulted array and editing the array with js.
I tried to change the PDO result with fetchColumn
but than i can fetch only one column and have to do two SQL requests - right?
I tried to transform the resulted array with $singleD = array_reduce($result, 'array_merge', array());
but didn't fit.
I tried two Chart.js tutorials, but i think the json input data wasn't properly formated. https://www.dyclassroom.com/chartjs/chartjs-how-to-draw-bar-graph-using-data-from-mysql-table-and-php and https://phppot.com/php/creating-dynamic-data-graph-using-php-and-chart-js/
I used array_column()
to get the json data. A very easy solution at all:
$thedate = json_encode(array_column($result, 'thedate'));
$counted = json_encode(array_column($result, 'counted'));
and got it in chartjs with "labels": <?php print($thedate);?>
.