Show the number of visits over the past 24 hours, broken by hour

Question

I save the timestamp in the mysql table every time someone visits the site for the first time.

I wrap up with data that looks like this:

2009-08-02 04:08:27
2009-08-02 04:07:47
2009-08-02 05:58:13
2009-08-02 06:28:23
2009-08-02 06:34:22
2009-08-02 08:23:21
2009-08-02 09:38:56

What I want to do with this data, create the number of visits that fall in every hour. So, in the example above, I will arrive at the 4th hour, having 2 visits, 5th hour = 1, 6th hour 2, 8th hour 1, etc.

I thought the best way to do this is to do this:

// a 24 hour loop
for($i = 24; $i > -1; $i--) {

    // the query for each hour
    $sql = 'SELECT * FROM visits WHERE (DATE(added) = DATE_SUB(CURRENT_DATE(), INTERVAL ' . $i . ' HOUR))'

$res = mysql_query($sql);
$count = mysql_num_rows($res);  


    // store the number of rows for this loop in the array
    $visits[] = $count;
}

This seems logical to me ... but for some reason ... its definitely not working.

How do you do this?

Answer 1

how to do this in only one query using a group by clause?

, - :

select HOUR(added), count(*) as nbr
from visits
where added between '2009-07-14' and '2009-07-15'
group by HOUR(added)
order by HOUR(added)

(, , )

, , ... , 24 - .
, , PHP; , !

, PHP, "" , .

Answer 2

:

SELECT hour(added), count(*) 
FROM visits 
WHERE added >= CURRENT_DATE()-1
GROUP BY hour(added)

Hour (time): " . 0 23 , ",