1

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.

Improve this question
4
  • possible duplicate of very recent How to find prev and next id record from table Commented Aug 15, 2010 at 12:08
  • @Col. Shrapnel: While similar, that's not an exact duplicate because in that question the field to search by was a PK which is guaranteed to be unique. That's not necessarily the case here. Commented Aug 15, 2010 at 12:10
  • @Mark with non-unique values you couldn't move to the next record with same value using your queries. Commented Aug 15, 2010 at 12:12
  • @Col Shrapnel: Yes I know, and I mentioned that in my answer. For the more advanced case see my answer to this question: stackoverflow.com/questions/3366777/… where I handle this case. Commented Aug 15, 2010 at 12:14

2 Answers 2

Reset to default
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:

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Does this work, when the table-coloums storing the date are of type "datetime" and not something like unix-timestamp?
Since I think, that there will never be two records with the same date. I don't have to consider that, but thanks!
0

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

Improve this answer

3 Comments

The TOP syntax doesn't work in MySQL. That works in for example SQL Server.
What does TOP 1 mean? Is it the same like LIMIT 1 in the end of the query?
ah sorry ;) im from the MS Sql World. Of course it s LIMIT 1 for MySql

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.