Menu
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");
453
In the above SQL query, we use MySQL system function now() to get current datetime. Then we use INTERVAL clause to select those rows where order_date falls within past 24 hours of present datetime. Instead of specifying interval in hours, you can also mention it in day.
How do I get the ID of the last updated row in MySQL using PHP? mysqli_insert_id($link) will return exactly the id of last updated row.
The NOW() function returns the current date and time. Note: The date and time is returned as "YYYY-MM-DD HH-MM-SS" (string) or as YYYYMMDDHHMMSS. uuuuuu (numeric).
WHERE date_created > DATE_SUB( NOW(), INTERVAL 24 HOUR)
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)
42
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
