Getting last and next db-record by date in MySQL-Table?

Question

Hey guys,

I've got a certain question related to a blog, which I am developing in OOP (PHP) right now. All blogposts are stored in a MySQL-table.

In the app you can read a specific post by giving the id of the post via GET-parameter. So like http://example.com/?id=2. Under the blogpost I want to show to navigation links like "previous" and "next", to see the next and previous blogpost ordered by date relative to the post the user is reading now. So what I need is the id of the next and the previous record in the mysql-table by date.

How to solve this? Is there any way to solve this in SQL, or do I have to get all records with php and then do some checks to determine if this is the last or next one?

Just a note: I don't want to fetch the last and next posts by id, but by date to get the id of them.

Any help would be appreciated. Thanks.

Answer 1

To get the newest record older than a certain date:

SELECT id
FROM yourtable
WHERE date < '2010-08-15 14:07:12'
ORDER BY date DESC
LIMIT 1

Or the oldest record newer than a certain date:

SELECT id
FROM yourtable
WHERE date > '2010-08-15 14:07:12'
ORDER BY date 
LIMIT 1

Make sure that the date column is indexed.

This works fine if date is unique, but if you have two records with exactly the same date and use next repeatedly this could skip over one of the records. To solve this you could use a tie-breaker column such that (date, tie-breaker) is always unique. You could for example use the primary key as a tie-breaker.

See my answer to this question to see how to do this:

• Forward Back Records in MySQL with the same DATA in the primary

Answer 2

the next id:

SELECT TOP 1 id FROM blogposts WHERE blogdate > $given_date_of_actual_blogpost$ ORDER BY blogdate

the previous id:

SELECT TOP 1 id FROM blogposts WHERE blogdate < $given_date_of_actual_blogpost$ ORDER BY blogdate DESC