SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Photo Albums and Pagination

    So.. I'm working on site where uses can upload photos into albums and when they view individual images I'd like them to be able to click prev and next links to go to the image before and after in the album. I've got the album id in the images table.

    I was originally using /view.php?a=3&i=2 to pass the album id and row where the image was in the album when selecting all images with that album id. If images in the album are deleted the row numbers are going to change and this could lead to duff bookmarks and links to images that should be there, so I'd rather link to the image id.

    So if I linked to the image id how would I find out if there is an image before and after and if so return the image id?

    Thanks

  2. #2
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    How do you order your images in the database? Is there an order field, or ordered by the time uploaded?

  3. #3
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Mal,

    The image_id field is an auto increment which I could order by or I could add a time field if it will help

  4. #4
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    SELECT i1.*, i2.image_id AS last_id, i3.image_id AS next_id
    FROM images AS i1
    LEFT JOIN images AS i2 ON i1.album_id = i2.album_id AND i2.image_id < i1.image_id
    LEFT JOIN images AS i3 ON i1.album_id = i3.album_id AND i3.image_id > i1.image_id
    WHERE i1.image_id = $image_id
    ORDER BY i1.image_id ASC, i2.image_id DESC, i3.image_id ASC
    LIMIT 1

    Try this, it's selecting from the images table three times, once for the info then once more for the prev and next. Because they are left joins they will get it even if it's not there and you will end up with null values that you can check against in php (if $row->last_id > 0 then show the prev button!).

    The order by statement makes sure that the id's are the closest to the current one - that's the only bit that I'm a bit iffy on.

    This is off the top of my head, please check to see if it works and gives the expected results.

    It's generally not a great idea to order by image_id as while a lot of the time these are incremental, there are times this may not end up the case. Always have a field you can order by which isn't the auto incrementing primary key.

  5. #5
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Mal,

    Thanks very much, that's great, worked a treat!

  6. #6
    SitePoint Zealot
    Join Date
    May 2004
    Location
    uk
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Forgot to say Mal, I appreciate the explanation a swell as the query.

  7. #7
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    No problem, glad I could help.

  8. #8
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you create a new column to order by as Mal suggested, and you call that field order remember to use `backticks` so SQL doesn't confuse it for the order statement.
    Better yet call it `position`

  9. #9
    SitePoint Addict Mal Curtis's Avatar
    Join Date
    Jul 2009
    Location
    New Zealand
    Posts
    327
    Mentioned
    14 Post(s)
    Tagged
    0 Thread(s)
    Or you could call it `charles`


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •