|Syntactic Confectionery Delight
Ok, this may or may not be a good solution, depending you whether or not you want to mess with your database design, but it will work.
Somewhere in the table put another column for a sequence number. So, instead of
you have something like
You could then grab the seq_id to the corresponding img_id
Then 1) calculate prev and next seq_id and 2) grab the img_ids you want be referencing the seq_id
You could easily keep the current album seq_id value in another table and reference it when adding new images. As I said, it may be a hassle modifying your database, but it may be worthwhile in the long run as it more easily does what you want than the other options.
Note: This may be a good "teachable moment" as educators like to call them.
Perhaps the most important thing in designing an application that uses a database is to know thoroughly what you want from the application before you even begin designing the database. Too often, people design a database based on a less than thorough examination of the application requirements and end up having to redo it (the database.) :)
In reply to Re^3: Building an index for next/last in a photo album.