Displaying monthly birthday data in a bar chart using SQL and Chart.js

Data and Databases
1

I’m working on a project to show how many birthdays happen each month using a bar chart. I’ve got the SQL query working fine:

SELECT COUNT(*) FROM employees WHERE MONTH(birth_date) = 1

But I’m stuck on how to get this data into Chart.js. I tried something like this:

$months = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'];
$birthday_counts = [];

foreach ($months as $index => $month) {
    $query = $db->query('SELECT COUNT(*) FROM employees WHERE MONTH(birth_date) = ' . ($index + 1));
    $birthday_counts[] = $query->fetchColumn();
}

$chart_data = json_encode($birthday_counts);

And then in my JavaScript:

let ctx = document.getElementById('birthdayChart').getContext('2d');
new Chart(ctx, {
    type: 'bar',
    data: {
        labels: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec'],
        datasets: [{
            label: 'Birthdays per Month',
            data: JSON.parse(chartData),
            backgroundColor: 'rgba(75, 192, 192, 0.6)'
        }]
    }
});

But it’s not working right. The chart shows up empty. What am I doing wrong? How can I fix this to show the birthday data correctly in the bar chart?

2

hmmm, have u tried checking if the data is actually getting passed to ur javascript? maybe console.log(chartData) before parsing it? also, make sure ur PHP is outputting the JSON correctly. sometimes little things like that can trip u up. what if u hardcode some test data in the JS to see if the chart works at all?

3

It seems like you’re on the right track, but there might be a few issues in your implementation. First, ensure your PHP is actually outputting the JSON data correctly. Try adding echo $chart_data; before your JavaScript code to verify the data is being passed. Also, in your JavaScript, make sure you’re correctly referencing the PHP variable. It should be something like let chartData = <?php echo $chart_data; ?>; instead of using JSON.parse directly.

Another potential issue could be in your database query. Consider using a single query to fetch all month data at once, which would be more efficient. You could use a query like:

SELECT MONTH(birth_date) AS month, COUNT(*) AS count 
FROM employees 
GROUP BY MONTH(birth_date) 
ORDER BY month;

This approach would simplify your PHP code and potentially resolve any data inconsistencies. Remember to always check your browser’s console for any JavaScript errors and the network tab to ensure the data is being properly transmitted from the server to the client.

4

yo, make sure ur PHP is actually outputting the JSON. try echo $chart_data; before ur JS. also, check network tab in browser dev tools to see if data’s coming thru. if that looks good, maybe try hardcoding some test data in JS to see if chart works at all