Selecting adjacent records

[shacker]

I've got a table that frequently has rows deleted on the fly, leaving large gaps in the auto-incrementing ID. So if I construct prev/next links between records by subtracting/adding 1 to the current record ID, the user will often get 404s when navigating via these links.

Is there a way to construct a query that selects the current record as well as the actual previous and next record IDs in the table?

All of the prev/next tutorials I've found seem geared toward paginating search results, which is a different problem from calculating actual prev/next record IDs.

Thanks,
Scot

[subwayman]

Assuming your current record id is in say, $redID:

SELECT MAX(id) FROM tablename WHERE id < $recID

will give the the previous record, and

SELECT MIN(id) FROM tablename WHERE id > $recID

You could also use something like

SELECT * FROM tablename WHERE id < $recID ORDERBY id DESC LIMIT 1

and

SELECT * FROM tablename WHERE id > $recID ORDERBY id ASC LIMIT 1

[shacker]

Many thanks subwayman - just what I was looking for, works perfectly. Bingo.

Note: "ORDER BY" rather than "ORDERBY".

Thanks,
Scot