I'm trying to form two SQL statements on a detail page that will provide a item_id number that will be added to a URL string passed to that same page for two buttons, NEXT and PREVIOUS. What's tricky is I don't want to go to the previous/next record by the items' numeric ID #, but rather according to their order by artist last name (and subsequently by artist first name and finally by title if there is ambiguity).
So, here's what I came up with but I know it's not quite right yet:
NEXT
Code:
SELECT item_id FROM items LEFT JOIN artists ON items.artist_id = artists.artist_id WHERE $current_artist_last_name > artists.last_name ASC LIMIT 1
PREVIOUS
Code:
SELECT item_id FROM items LEFT JOIN artists ON items.artist_id = artists.artist_id WHERE $current_artist_last_name < artists.last_name ASC LIMIT 1
Am I on the right track here? I know I haven't even included the logic for what happens if the artists' last name is the same and I'm really not sure how to do that.
EDIT: I'm beginning to think I need to form an array of all the records, sort the array as I want it, and then manipulate the pointer to pick the records I want. So it's a bit more involved than just doing the query. Is that closer?
Top
