CODEWITHSUNDEEP
phpsqlmysqlcakephp
Sam Cogan
Sam Cogan

Reputation: 4334

Multiple Counts in CakePHP

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?

Upvotes: 2

Views: 792

Answers (1)

Sampson
Sampson

Reputation: 268512

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

Upvotes: 4

Related Questions