1

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?

Emilio Venegas
  • 546
  • 5
  • 22
  • Can't you just use the date(s) you want in your `where`-condition? – Solarflare Mar 30 '17 at 03:40
  • This should scintillate your senses `SELECT Notes, TextOrder, FoodOrder1.NotificationType, FoodOrder1.RestID, Trans_DTS, ServerID, TableNumber, TransID, MINUTE(Trans_DTS) AS Minute, Minute(NOW()) As Diff FROM FoodOrder1 Where `NotifyTurnOff`=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 – Usman Shahid Mar 30 '17 at 03:44
  • 1
    Please check if this helps, http://stackoverflow.com/a/4230798/4874281 – manian Mar 30 '17 at 16:15

0 Answers0