PHP MySQL Query most popular in last 24 hours

php mysql datetime ranking popularity
Dylan Taylor picture Dylan Taylor · Jul 14, 2010 · Viewed 8.5k times · Source

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

Haim Evgi picture Haim Evgi · Jul 14, 2010 
WHERE date_created > DATE_SUB( NOW(), INTERVAL 24 HOUR)

Related questions

Convert from MySQL datetime to another format with PHP

I have a datetime column in MySQL. How can I convert it to the display as mm/dd/yy H:M (AM/PM) using PHP?

Read More
adding 1 day to a DATETIME format value

In certain situations I want to add 1 day to the value of my DATETIME formatted variable: $start_date = date('Y-m-d H:i:s', strtotime("{$_GET['start_hours']}:{$_GET['start_minutes']} {$_GET['start_ampm']}")); What is the best way to do …

Read More
Object of class DateTime could not be converted to string

I have a table with string values in the format of Friday 20th April 2012 in a field called Film_Release I am looping through and i want to convert them in datetime and roll them out into another table. My …

Read More