[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-27
5
Medium Priority
?
402 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:davideo7
  • 3
  • 2
5 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37042276
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
 

Author Comment

by:davideo7
ID: 37042358
mwvisa1: What would the query look like than?
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37042777
So these are unix timestamps?
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 37042853
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
 

Author Closing Comment

by:davideo7
ID: 37046696
That worked perfectly, thanks.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses
Course of the Month19 days, 9 hours left to enroll

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question