CODEWITHSUNDEEP
phparraysdatabasefetch
Shubham
Shubham

Reputation: 75

count total rows date wise from last 7 days in PHP

I want to count the registered user from database for last 7 days includes date of today and I am mentioning the structure is given below for understanding :-

reg_users
+----+---------------------+------+
| id | added-date           | name |
+----+---------------------+------+
| 1  | 2020-06-01 00:02:40  | john |
+----+---------------------+------+
| 2  | 2020-06-01 00:02:41  | sue  |
+----+---------------------+------+
| 3  | 2020-06-03 00:02:42  | fran |
+----+---------------------+------+
| 4  | 2020-06-04 00:02:40  | mark |
+----+---------------------+------+
| 5  | 2020-06-05 00:02:41  | tim  |
+----+---------------------+------+

now suppose How I count the total registered use date wise from last 7 days.. where I am considering today is 2020-06-07 [dd-mm-yyy] and I want get result in array like [2, 0, 1, 1, 1, 0, 0] here 2 because 2 user registered on 2020-06-01 then on 2020-06-02 no user resisted so 0.

Please help me..

Upvotes: 0

Views: 410

Answers (1)

Gavin
Gavin

Reputation: 2365

I think something like this is what you're after?

select count(*) as total, DATE_FORMAT(your_date, '%y%m%d') as date_yyyymmdd from your_table group by date_yyyymmdd;

It will return you a result like

+-------+----------------+
| total |  date_yyyymmdd |
+-------+----------------+
|     1 |       20200727 |
|     0 |       20200726 |
|     3 |       20200725 |
+-------+----------------+

Upvotes: 1

Related Questions