I have a table that's updated automatically every 24h and I'm trying to get the records from 1 day, 1 week, and 1 month ago. Right now I'm using 3 queries, which look something like this:
// Latest record is from 1 day ago
$q_1d = "SELECT * FROM `foo_records` WHERE foo = 'bar' ORDER BY date DESC LIMIT 0, 1";
// 7th latest record is from a week ago
$q_1w = "SELECT * FROM `foo_records` WHERE foo = 'bar' ORDER BY date DESC LIMIT 6, 1";
// 30th latest record is from a month ago
$q_1m = "SELECT * FROM `foo_records` WHERE foo = 'bar' ORDER BY date DESC LIMIT 29, 1";
It works fine, but I feel like it's dumb to have 3 queries that are so similar instead of merging them into 1. I know I could also do something like this:
$array = array();
// Get the latest 30 records
$query = "SELECT * FROM `foo_records` WHERE foo = 'bar' ORDER BY date DESC LIMIT 30";
$result = mysqli_query($link, $query);
while ($row = mysqli_fetch_assoc($result))
$array[] = $row; // Put them all into an array
$row_1d = $array[0]; // One day ago
$row_1w = $array[6]; // One week ago
$row_1m = $array[29]; // One month ago
But I'd like to know if there's a simpler way. If not, which of the 2 do you think is best?
where-condition?SELECT Notes, TextOrder, FoodOrder1.NotificationType, FoodOrder1.RestID, Trans_DTS, ServerID, TableNumber, TransID, MINUTE(Trans_DTS) AS Minute, Minute(NOW()) As Diff FROM FoodOrder1 WhereNotifyTurnOff=0 AND RestID ='$RestaurantID' AND DATE(Trans_DTS)=CURDATE()- notice how I am using MySQL's inbuilt date functions...you probably can do something similar