Generate result by day

Question

I am working on PHP and MySQL, I want to count result that are generated today and yesterday.

Here is screenshot of my phpmyadmin and table is results. In date_generate column I have used TIMESTAMP

table

I have this query which count total results

<?php

   $query = $db->query("SELECT * FROM `results`");
   $count = $query->rowCount();
   echo $count;

Here's a hint: start with using a `WHERE` clause. You should show some minimum effort before asking for help. — Bartosz Zasada, Feb 21 '17 at 09:59
yes i trying form yesterday, but still problem, can u post query for this — Rohaan Ahmed, Feb 21 '17 at 10:02
Welcome to Stackoverflow. Please do some research before asking new questions. This one has been answered many times already. Check this link: http://stackoverflow.com/questions/10395444/mysql-count-and-group-by-day — Erik Kalkoken, Feb 21 '17 at 10:05
yes i have searched as well but from my query its showing output result 1, can you post query for me — Rohaan Ahmed, Feb 21 '17 at 10:07

score 1 · Answer 1 · answered Feb 21 '17 at 10:06

1

You need to use group by to get the records created based on date_genrate. So, your query will be something like Considering date_generate as timestammp

SELECT DATE(FROM_UNIXTIME(date_generate)) AS date_g, COUNT(id) AS NumPosts
 FROM   results
 WHERE date_generate BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE()
 GROUP BY date_g
 ORDER BY date_g

answered Feb 21 '17 at 10:06

Naincy

2,953
1
12
21

i tried this but still this showing output is 1 here is query i tried 'query("SELECT DATE(FROM_UNIXTIME(date_generate)) AS date_g, COUNT(id) AS NumPosts FROM results WHERE date_generate BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE() GROUP BY date_g ORDER BY date_g "); echo $query->execute(); ?> ' – Rohaan Ahmed Feb 21 '17 at 10:14
get row_count.... execute will return only query executed successfully or not....try running query directly in phpmyadmin and see result... – Naincy Feb 21 '17 at 10:19

Vikas Umrao · Accepted Answer · 2017-02-21T10:14:55.507

0

Replace your query :

 $query = $db->query("SELECT * FROM `results` where date_generate BETWEEN DATE_ADD(CURDATE(), INTERVAL -1 day) AND CURDATE()");
$query->execute(); 
echo $count = $query->rowCount();

edited Feb 21 '17 at 10:14

answered Feb 21 '17 at 10:06

Vikas Umrao

2,800
1
15
23

1

Check the updated answer, and use the $count variable. echo $query->execute(); will always return 1 or 0 as its return type will always be BOOL (true or false) – Vikas Umrao Feb 21 '17 at 10:16
if you feel this is correct, plz tick the check button – Vikas Umrao Feb 21 '17 at 10:18
i'm really sorry but i have not repu that i can vote :( – Rohaan Ahmed Feb 21 '17 at 10:23

Generate result by day

2 Answers2