I'm trying to make an image database which does not keep a consistent record of ID's. For example it might go 1,2,6,7,12, but as you can see that is only 5 rows. Inside the table I have fileid and filename. I created a PHP script to show me the image when I give the fileid. But if I give it the ID 5 which does not exist I get an error. That's fine as I want an error for that, but not for users who will browse through these images using forward and back buttons. The forward and back buttons would need to retrieve the true fileid which comes after the given ID. Hopefully that makes sense.
This is how I imagine the code to look like:
SELECT offset( WHERE fileid=4 )
That would give me the offset of the row where fileid is equal to 4. I think this is easy enough to understand. The reasons I need this are for creating the forward and back button. So I planned to add 1 or take 1 from the offset which gives me the new ID, and the new filename. That way when users browse it will skip the dead ID values automatically, but it will give an error when giving a false ID.
Going up:
SELECT * FROM table WHERE id > 'your_current_id' ORDER BY id LIMIT 1;
Going down:
SELECT * FROM table WHERE id < 'your_current_id' ORDER BY id DESC LIMIT 1;
ps: it is better to make LIMIT 2, so that you can see that you are at the first or at the last records in the database when only one record is returned.