Find the "next" highest id in MySQL

Question

I am developing a web application where there are a number of very similar pages I'd like users to browse through. The data for these "pages" is stored in a database with a unique ID as the primary key.

I'd like to have a "NEXT" button on each page that queries the database and finds out what the next highest ID is, and displays the data from that id. My problem is that there are a couple conditions:

• Sometimes pages may be deleted or removed, meaning that there are gaps in the IDs, so I can't just do -1.
• I need to only return pages where the column 'active' == 1

Does anyone have any tips or suggestions? Thanks!

Answer 1

something like this?

SELECT id FROM table WHERE id > '$id' AND active = '1' ORDER BY id ASC LIMIT 1

a PREV button would then need something like this:

SELECT id FROM table WHERE id < '$id' AND active = '1' ORDER BY id DESC LIMIT 1