Multiple Counts in CakePHP

Question

I have a PHP application using CakePHP. I need to present a table showing the number of specific events for each day of the month. Each event is a record in the database with a name and data. What is the best way to create a table for this, do I really need to make 31 SQL calls to count events for each day or take data for the whole month and then analyze them in my application, or is it better to find a command to use with the cake?

Answer 1

You can RECORD events and GROUP BY the date they occurred.

Example:

SELECT eventDate, COUNT() as 'events'
FROM   events
GROUP  BY eventDate
ORDER  BY eventDate ASC

, :

2009-08-11 | 23
2009-08-09 | 128
2009-08-06 | 15

EventDate, substr() , :

SELECT substr(eventDate, 1, 10) as 'date', COUNT() as 'events'
FROM   events
GROUP  BY substr(eventDate, 1, 10)
ORDER  BY eventDate ASC