Select Next and Previous record in MySQL

Discuss coding issues, and scripts related to PHP and MySQL.
PloMar
Posts: 48

Select Next and Previous record in MySQL

Hello,
Let say I have records into a MySQL table with IDs: 3, 5, 8, 9, 13; and I want to be able to go from one to another by navigation via next/previous links. But I don't know how to fetch the rows with nearest higher ID (Note that id values are not contiguous).

I use this SQL query to select the record with id 5:

Code: Select all

SELECT * FROM table_name WHERE id = 5
I need to be able to fetch next existing row, which would be 8, and previous record (ID 3).
How can I fetch next and previous record without fetching whole result set and manually iterating (if it's possible in one query)?

MarPlo Posts: 186
Hi,
Try this SQL code that combines two sql statements into one.

Code: Select all

SELECT * FROM table_name 
WHERE (
  id = IFNULL((SELECT MIN(id) FROM table_name WHERE id > 5),0) 
  OR  id = IFNULL((SELECT MAX(id) FROM table_name WHERE id < 5),0)
)

Similar Topics