Show analytics of registered users for last 12 months using chart

Question

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?

So, to me, you'll need to take your results array and bump it against a preset array to check which months are missing and pop those into the result set... you can do that in the model before going back to your controller. sound reasonable? — AWP, Mar 04 '20 at 21:24
Sounds like a solution but I'd love to try some more elegant way. — Nick, Mar 04 '20 at 21:45
https://stackoverflow.com/questions/27600863/mysql-monthly-sale-of-last-12-months-including-months-with-no-sale — miken32, Mar 04 '20 at 22:20

score 1 · Answer 1 · answered Mar 04 '20 at 21:25

You need to make changes in your query like below:


$start = new DateTime(date('Y-m-d'));
$end = new DateTime(date('Y-m-d'));
$end->modify('+12 month');

$interval = DateInterval::createFromDateString('1 month');
$period   = new DatePeriod($start, $interval, $end);

foreach ($period as $dt) {
    $registeredUsersData[$dt->format('Y-m')] = 0;
}

$registeredUsersData = $connection->fetchAll(
    'SELECT
    COUNT(id) AS registered_users,
    DATE_FORMAT(created_on,'%Y-%m') AS created_on
    FROM users 
    WHERE created_on BETWEEN CURDATE() - INTERVAL 12 MONTH AND CURDATE() 
    GROUP BY DATE_FORMAT(created_on,'%Y-%m')
    ORDER BY DATE_FORMAT(created_on,'%Y-%m') DESC;
');

foreach($registeredUsersData as $registeredUser) {
    $registeredUsersData[$registeredUser['created_on']] = $registeredUser['registered_users'];
}   

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

I also updated your PHP have all dates for 12 months

It does not work.Would you offer more elegant approach to do it? — Nick, Mar 04 '20 at 21:44

score 1 · Answer 2 · answered Mar 04 '20 at 22:08

I am more of a caveman coder than eloquent, but I believe this gets the result set you are looking for without changing your query... add this after your query to send the new data set to your controller.

for ($i = 1; $i < 12; $i++) {
    $monthToCheck[$i]['registered_users'] = 0;
    $monthToCheck[$i]['month_of_creation'] = date("F", strtotime( date( 'Y-m-01' )." -$i months"));
    $monthToCheck[$i]['year_of_creation'] = date("Y", strtotime( date( 'Y-m-01' )." -$i months"));
}

 return (array_merge($registeredUsersData,$monthToCheck));

Show analytics of registered users for last 12 months using chart

2 Answers2