Multiple Counts in CakePHP

Question

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

Answer 1

You could COUNT the events, and GROUP BY the date on which they occurred.

Example:

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

Which may render results similar to the following:

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

If you store date and time as the eventDate, you'll need to use the substr() function to get events grouped by just the date:

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