Chartjs-Chart.js 3 – How to count MySQL entries by month and display them in a bar chart

by

0👍

✅

As we don’t know what your afwijkings_rapporten table contains, other than a datetime in aangemaakt, here is a simple suggestion to get you started.

This query will get you 12 months of data (including current month):

// query to get data from the table
$query = <<<'SQL'
        SELECT
            DATE_FORMAT(`aangemaakt`, '%Y%m') AS `grp`,
            DATE_FORMAT(`aangemaakt`, '%b %Y') AS `label`,
            COUNT(*) AS `num`
        FROM `afwijkings_rapporten`
        WHERE `aangemaakt` >= MAKEDATE(YEAR(NOW()), 1) -- start of current year
          AND `aangemaakt` <  MAKEDATE(YEAR(NOW()) + 1, 1) -- start of next year
        GROUP BY `grp`, `label`
        ORDER BY `grp` ASC
        SQL;

// execute query
$result = $pdo->query($query);

//  you can just fetchAll as you are not manipulating the result set
$data = $result->fetchAll(PDO::FETCH_ASSOC);

// send header so jQuery knows what it's getting
header('Content-Type: application/json; charset=utf-8');
echo json_encode($data);

Then in your success handler you can assign the labels (months) and their values:

let labels = [];
let values = [];

for(let row of data) {
    labels.push(row.label);
    values.push(row.num);
}

Leave a comment