PHP + Mysql - Get statistics for each day of the last 30 days

Question

The table name is Users

ID | name  | created_at 
------------------------------------------
 1 | John  | 2018-11-08 14:06:05 
 2 | Adam  | 2018-12-12 10:06:05
 3 | Peter | 2019-01-08 17:16:05 
-------------------------------------------

How do I get how many new users i have received for each day of the last 30 days (using mysql or php or both). The results should look like this:

1-Sat  15
2-Sun  08
.
.
.
30-Fri 13

If the day has no records it shows 0.

I tried this but i get stuck:

select date_format(created_at,\'%a\') as day  
from users 
where datediff (now(),created_at) <=30

Thanks alot

We will be glad to help if you get stuck on a specific programming problem, but we are not here to write code or design your system for you. You will need to at least make an attempt at solving your own issue. Please see [ask] a good question and [What topics can I ask about here?](//stackoverflow.com/help/on-topic) After [doing more research](//meta.stackoverflow.com/q/261592) if you have a problem you can post what you've tried with a clear explanation of what isn't working and providing a [Minimal, Complete, and Verifiable example](//stackoverflow.com/help/mcve). — John Conde, Feb 24 '19 at 20:40
@Strawberry might have a good idea here, "stream" the records to the browser import them in javascript in the browsers SQLite (indexedDB, WebSQL api's) and generate a resultset on the clients pc and use Javascript to generate the table's html from the generated resultset.. Edited: only problem can be API's limits to store data with those.. — Raymond Nijland, Feb 24 '19 at 23:08

score 0 · Accepted Answer · answered Feb 24 '19 at 21:18

Here is a solution in 3 steps :

generate a list of the last 30 days (see detailed solution here : Select last 30 days in MySQL
generate the number of registrations per day, with GROUP BY. It is a key concept in SQL database, so I invite you to learn it if you use SQL regularly : SQL Group By
Use a LEFT JOIN to keep all the dates in the calendar, and associate them to the number of registrations. If a day has no registration, the value will be NULL, so I used COALESCE to return 0 in this case

Full query :

SELECT calendar.day, COALESCE(daily_registrations.nb_created, 0)
FROM
( 
    SELECT DATE_FORMAT(m1, '%a %d %M') AS day
    FROM (
        SELECT SUBDATE( NOW() , INTERVAL 30 DAY) + INTERVAL m DAY AS m1
        FROM (
            select @rownum:=@rownum+1 as m from
            (select 1 union select 2 union select 3 union select 4) t1,
            (select 1 union select 2 union select 3 union select 4) t2,
            (select 1 union select 2 union select 3 union select 4) t3,
            (select 1 union select 2 union select 3 union select 4) t4,
            (select @rownum:=-1) t0
            ) d1
    ) d2 
    WHERE m1 <= now()
    ORDER BY m1
) calendar
LEFT JOIN 
(
    SELECT date_format(created_at,'%a %d %M') AS day, COUNT(*) AS nb_created
      FROM users
     WHERE datediff (now(),created_at) <=30
     GROUP BY day
) 
daily_registrations
ON calendar.day = daily_registrations.day

Thanks Joffrey for your creative solution but I'm getting just 25 results? — Zaki, Feb 24 '19 at 21:58
If you are using PhpMyAdmin, it displays only 25 lines per default. You can change this settings. But when I run the query, I get 31 lines in total. — Joffrey Schmitz, Feb 24 '19 at 22:08
@Strawberry I'm trying to put new users per day on graph like this: [link](https://www.chartjs.org/samples/latest/charts/line/basic.html) — Zaki, Feb 24 '19 at 22:35
The outer SQL also needs to have a `ORDER BY` clause to maintain the order. — Raymond Nijland, Feb 24 '19 at 23:01

PHP + Mysql - Get statistics for each day of the last 30 days

1 Answers1