Ieuan Ieuan - 6 months ago 11
SQL Question

Show 5 database entries based on date

I've got this SQL query right now;

SELECT * FROM `articles` WHERE
`type` = 'press' AND (`published` = '1' AND `time_start` >= '".$time_today."')
ORDER BY `time_start` ASC, `id` DESC LIMIT 5


The idea is that 5 articles are always shown.
time_start
is the starting time,
time_end
is the end time (just PHP's
mktime
).

The articles are basically calendar entries, only entries starting tomorrow should be shown (working) and only the first upcoming five. Right now, there's less than five upcoming calendar points.

How do I basically make it do this query
if num_rows > 5, else fill up with older ones
? Should I be looking at a PHP fallback simply overwriting the data with a different query (showing the 5 'newest' articles) or is there a way to work this in the query itself?

Question isn't as much about how it could be done as looking for the proper way to do it, hope that's not against the rules

Answer

So it looks like you want a window of 5 events from start time onwards.. with it showing previous events in the case where less are upcoming.

Two viable options here..

If you are happy dealing with this in the application logic:

Run:

  SELECT * 
    FROM articles
   WHERE type = press 
     AND published = '1' 
     AND time_start >= :time_today
ORDER BY time_start ASC, id ASC /* 'I thought id should be ascending here */ 
   LIMIT 5

Calculate how many extra articles you still need and then run:

  SELECT * 
    FROM articles
   WHERE type = press 
     AND published = '1' 
     AND time_start < :time_today
ORDER BY time_start DESC, id DESC 
   LIMIT :slack

Simply reverse the first result set and append the second to it and move on.

If you want to do this all in the DB I'd probably do:

   SELECT *
     FROM ((
      SELECT * 
        FROM articles
       WHERE type = press 
         AND published = '1' 
         AND time_start >= :time_today
    ORDER BY time_start ASC, id ASC 
       LIMIT 5
           )
   UNION ALL
           (
      SELECT * 
        FROM articles
       WHERE type = press 
         AND published = '1' 
         AND time_start < :time_today
    ORDER BY time_start DESC, id DESC 
       LIMIT 5
          )) window_extents
 ORDER BY time_start DESC, id DESC 
    LIMIT 5

This works by selecting all possible records for the window and then narrowing down the results. It should also make good use of your indexes.

N.B. It looks like you are concatting the date into the query rather than binding it in.. I'd get out of the habit of doing this, even in a secure environment unless absolutely necessary.

Comments