How can I figure out the most posts a user has made in a single day on my PHP forum?

I basically want a query which will tell me the most posts a single user has made in a single day.  I'm not sure if this can be done with a MySQL query but I have a query that tells how many posts a certain user has on a specific day but I want it to figure out which day that user has made the most posts and how many posts it was.

Anyways, here's my current query:

SELECT COUNT(*) as total
FROM posts
WHERE DATE BETWEEN Unix_Timestamp('2011-10-20 00:00:00') AND Unix_Timestamp('2011-10-21 00:00:00')
AND user=1
davideo7Asked:
Who is Participating?
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Here is an example of one way to do this with unix timestamps:
SELECT DATE(FROM_UNIXTIME(`date`)) AS `date`
     , COUNT(`id`) AS `total`
FROM posts
WHERE `date` BETWEEN UNIX_TIMESTAMP('2011-10-20 00:00:00') 
               AND UNIX_TIMESTAMP('2011-10-28 00:00:00')
AND `user` = 1
GROUP BY DATE(FROM_UNIXTIME(`date`))
ORDER BY `total` DESC, `date` DESC
LIMIT 1
;

Open in new window

0
 
Kevin CrossChief Technology OfficerCommented:
You could use GROUP BY to group on each day within a range. Then your COUNT() will be over that one day. You can then ORDER BY total DESC LIMIT 1 to get the highest count and what day it falls on. You may need a secondary sort on DATE DESC to get the last day of the highest count or ASC if you want the first, for example.
0
 
davideo7Author Commented:
mwvisa1: What would the query look like than?
0
 
Kevin CrossChief Technology OfficerCommented:
So these are unix timestamps?
0
 
davideo7Author Commented:
That worked perfectly, thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.