Show analytics of registered users for last 12 months using chart

I’m trying to get all users for the last 12 months from database and show some statistics using chart.For that purpose I’m using chartJS.

$registeredUsersData = $connection->fetchAll(
    'SELECT
    COUNT(id) AS registered_users,
    CONCAT(MONTHNAME(created_on), " ", YEAR(created_on)) AS created_on
    FROM users 
    WHERE created_on 
    BETWEEN CURDATE() - INTERVAL 12 MONTH AND CURDATE() 
    GROUP BY YEAR(created_on), MONTH(created_on)
    ORDER BY YEAR(created_on) DESC, MONTH(created_on) DESC;
');

We pass it by symfony controller and render html template.

$registeredUsersCount = array_column($registeredUsersData, 'registered_users');
$registeredUserMonths = array_column($registeredUsersData, 'created_on');

return $this->render('dashboard/admin/index.html.twig', [
    'registered_users_count' => $registeredUsersCount,
    'registered_users_months' => $registeredUserMonths
]);

In the template I’m trying to display using a bar diagram that data where year and month should be combined and display on the abscissa(X-axis) and the count respectively on the ordinate(Y-axis).

<div class="chart-container">
    <canvas id="canvas"></canvas>
</div>

let months = {{ registered_users_months|json_encode|raw }};
let users = {{ registered_users_count|json_encode|raw }};
const ctx = document.getElementById('canvas').getContext('2d');
const myChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: months,
        datasets: [{
            label: 'Registered Users Monthly',
            data: users,
            backgroundColor: 'rgba(90, 80, 191, 0.2)',
            borderColor: 'rgba(90, 80, 191, 1)',
            borderWidth: 1
        }]
    },
    options: {
        maintainAspectRatio: false,
        responsive: true,
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero: true
                },
            }]
        }
    }
});

Data is properly displayed but not all “previous” 12 months are displayed.If a month does not have registered users it must be displayed as well but with count of zero. Unfortunately, the given query does not support that function currently, how could I modify it?

Source: Symfony Questions

Was this helpful?

0 / 0

Leave a Reply 0

Your email address will not be published. Required fields are marked *