PHP MySQL Query most popular in last 24 hours

Question

Say I want to get ten records with the MOST likes in the last 24 hours. Here's what I have so far:

$date = date("o-m-d");
$query = "SELECT date_created,COUNT(to),from,to FROM likes WHERE date_created LIKE '$date%' GROUP BY to ORDER BY COUNT(to) DESC LIMIT 10";

The problem with that is that it only gets the most liked from THAT DAY, no matter how far into that day it is. It doesn't get the most liked from the last 24 hours.

structure for likes: from | to | date_created | id

dates are in standard ISO time - example 2010-07-14T00:35:31-04:00. Come straight from the PHP reference: date("c");

Answer 1

WHERE date_created > DATE_SUB( NOW(), INTERVAL 24 HOUR)

Answer 2

You should be using date/time functions, instead of LIKE.

WHERE date_created >= (NOW() - INTERVAL 24 HOUR)

Answer 3

So first off date_created should be defined as a timestamp with on default current timestamp. If you have a date_modified in the table as well then date_modified would have on update current timestamp and you can defined with date created as a timestamp and this trigger to update it

CREATE TRIGGER likes_date_entered
BEFORE INSERT ON likes
FOR EACH ROW SET NEW.date_created = NOW()

Now that we have a timestamp you can easily apply some of the mysql date functions to the column.

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

I'll leave what to do as an exercise for the reader, unless you say pretty please and want me to give the exact syntax.

Answer 4

If your date_created field is a datetime or timestamp field type, you can use DATE_SUB in your where clause as follows;

WHERE date_created > DATE_SUB(NOW(), INTERVAL 24 HOUR)