How to get next/previous record in MySQL?

Question

Say I have records with IDs 3,4,7,9 and I want to be able to go from one to another by navigation via next/previous links. The problem is, that I don't know how to fetch record with nearest higher ID.

So when I have a record with ID 4, I need to be able to fetch next existing record, which would be 7. The query would probably look something like

SELECT * FROM foo WHERE id = 4 OFFSET 1

How can I fetch next/previous record without fetching the whole result set and manually iterating?

I'm using MySQL 5.

Answer 1

I was attempting to do something similar to this, but I needed the results ordered by date since I can't rely on the ID field as a sortable column. Here's the solution I came up with.

First we find out the index of the desired record in the table, when it's sorted as we want:

SELECT row
FROM 
(SELECT @rownum:=@rownum+1 row, a.* 
FROM articles a, (SELECT @rownum:=0) r
ORDER BY date, id) as article_with_rows
WHERE id = 50;

Then decrement the result by 2 put it in the limit statement. For example the above returned 21 for me so I run:

SELECT * 
FROM articles
ORDER BY date, id
LIMIT 19, 3

Gives you your primary record along with it's next and previous records given your stated order.

I tried to do it as a single database call, but couldn't get the LIMIT statement to take a variable as one of it's parameters.

Answer 2

Next row

SELECT * FROM `foo` LIMIT number++ , 1

Previous row

SELECT * FROM `foo` LIMIT number-- , 1

sample next row

SELECT * FROM `foo` LIMIT 1 , 1
SELECT * FROM `foo` LIMIT 2 , 1
SELECT * FROM `foo` LIMIT 3 , 1

sample previous row

SELECT * FROM `foo` LIMIT -1 , 1
SELECT * FROM `foo` LIMIT -2 , 1
SELECT * FROM `foo` LIMIT -3 , 1

SELECT * FROM `foo` LIMIT 3 , 1
SELECT * FROM `foo` LIMIT 2 , 1
SELECT * FROM `foo` LIMIT 1 , 1