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
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
Question by:davideo7
    LVL 59

    Expert Comment

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

    Author Comment

    mwvisa1: What would the query look like than?
    LVL 59

    Expert Comment

    by:Kevin Cross
    So these are unix timestamps?
    LVL 59

    Accepted Solution

    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
    ORDER BY `total` DESC, `date` DESC
    LIMIT 1

    Open in new window


    Author Closing Comment

    That worked perfectly, thanks.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
    The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now