Transform a multidimensional array from PDO to two simple arrays for a chart [duplicate]

Question

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/

Answer 1

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);?>.