If you are looking for the epic motorcycle journey blog that I've written, please see the Miles By Motorcycle site I put together. 
  • Finding Next and Prev entries from an SQL Result set given only a single reference number.
    03/02/2009 6:48PM

    For ages I've been doing handstands dealing with the next and previous problem in SQL result sets. This is used, for instance, in the Member Photos section. 

    Today I've been working to try to improve linking to the shared photos area. As of this writing, I still use the long encrypted links which embed values that make it easier for me to generate the next and previous link.

    For ages, I've had this sense that there had to be an easier way.

    And of course there is a MUCH better way than I've been doing it. Once I saw the solution, I found myself thinking "now why didn't I think that of that, it's so obvious".

    So let's say you have a photo_id value which is the unique key of your list of photos. You want to know what the photo_id of the next and previous photos in the list is.

    You can solve this problem with a very simple LEFT JOIN:

    SELECT photos.photo_id, next.photo_id AS next_id, prev.photo_id AS prev_id
    FROM photos
    LEFT JOIN photos AS next ON next.photo_id > photos.photo_id
    LEFT JOIN photos AS prev ON prev.photo_id < photos.photo_id
    WHERE photos.photo_id = <photo_id to display here>
    ORDER BY photos.photo_id ASC, prev_id DESC LIMIT 1

  • Comment By:
    03/02/2009 10:52PM
    Interesting usage of joins. Maybe I'll use that one some day.

    I did a little test, though, and found that I needed to amend the ORDER BY clause in order to get the right value for next_id:

    ORDER BY photos.photo_id ASC, next_id ASC, prev_id DESC LIMIT 1

    or the next_id value would be random.
  • Comment By:
    03/02/2009 10:54PM
    Good point.
You must be logged into an account to post comments.