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");

Vivianne

my datatype is varchar , what i do now... – Brewery

Answer 1

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

Increasing

@Dave Rix Welcome to stackoverflow. :) – Fordo

You may also use arithmetic operators such as NOW() - INTERVAL 24 HOUR – Cursed

Answer 2

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)

Costin

Answer 3

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

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

Fordo

@haim evgi Yes, I noticed that error after I submitted the answer. :) – Fordo

good answer but there is one problem: we should process the time in php not mysql – Purcell

@narcisradu Why would that be? – Fordo

@George Marian 1. if the sql server and php are on two different machines time data is no longer accurate 2. If the database server is in yet another timezone, things begin to get out of sync – Purcell

@narcisradu Sure, but those are situational. You can sync time on your servers to within a few seconds at least. If those few seconds make a difference, you deal with it; potentially as you've suggested. Ditto for point 2, which is quite atypical. – Fordo

it should be INTERVAL 24 HOUR and not 24 HOURS – Haerr

Answer 4

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.

Heehaw

George or Hami's conditions will work once you change the data type. – Heehaw

you know, my answer's better :-P Calculating the timestamp in PHP is a bad idea: "dates are in standard ISO time - example 2010-07-14T00:35:31-04:00. Come straight from the PHP reference: date("c");" – Heehaw