SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 35
  1. #1
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Retreiving the previous and next row through PDO?

    Hey,

    I've been looking to add a next and previous feature into a photo gallery I have.

    What I'm trying to do is get the previous and next row (if available) and make them into an href.

    I'm not sure if this should be done through PDO or SQL itself.

    This is what I attempted but it's not working because I have 2 rows available under my SQL statement and it's going to the else statement.

    PHP Code:
    if ($objStatement->nextRowset()) {
          
    $strNext 'photo.php?id='.$intUserID.'&pid='.$objStatement->next().'';
        }
        else
          
    $strNext '#'
    Can anyone lend a hand? thanks.

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    The feature you're looking for is called Pagination, there are many posts on this forum detailing various solutions.

    There is a surprising amount of logic involved to do it properly.

    Good luck!
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ah alright pagination. Thought it would be something different considering it's only one row at a time.

    thanks.

  4. #4
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It is not exactly pagination though.

    9three, first of all you should define "previous" and "next". These terms exists only with sorted data.
    So, you have to decide, how your data sorted.
    And then you can easily find next and prev records, using SQL queries with just > and < operators

  5. #5
    Non-Member
    Join Date
    Oct 2008
    Posts
    372
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah ha! You triggered the right idea!

  6. #6
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I don't think this is a straight forward pagination. If you just need to show a link to previous image and to a next image, then I would not really call it a traditional pagination.
    Let's say you have the IMAGES table.
    The way I would do this is to add a column 'previous_image_id' to the image table. Then when inserting a new image I would get the highest value of IMAGES.id that also belongs to this same album.

    Now when you have this 'previous_image_id' in every record it's easy to do a select - the previous_image_id you already have and to find the next image you just need to get the row in IMAGES with the previous_image_id value equals to 'this' (current) image. Basically a row that has our current image as it's previous_image_id is naturally the row that holds the next image.

    You will use the LEFT JOIN and join IMAGES on IMAGES twice (using the IMAGES table 3 times in one select, something like this:

    'SELECT I.id as id,
    I.title as title,
    I2.id as prev_id,
    I2.title as prev_title,
    I3.id as next_id,
    I3.title as next_title
    FROM IMAGES I
    LEFT JOIN IMAGES I2 on I.id = I.previous_image_id
    LEFT JOIN IMAGES I3 on I3.previous_image_id = I.id
    WHERE I.id = :current_id'

    This is untested sql, just to give you an idea how to do this. I am using this technique on many of my own projects where I need the next/previous links like links to previous blog post, next blog post.

    The tricky part comes when you have to delete an existing image - it will break the next/prev chain, so on every delete you need to update affected records that has deleted image as their previous_image_id and a next it.
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  7. #7
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    lampcms.com, imagine your records sorted by name, not id
    or there is some filter applied

    your proposal is awfully aganist relational model

  8. #8
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why whould it matter how the records are sorted? Each record still has an 'id' as in unique auto_increment id.

    Well, it's just a proposal, it takes longer to insert and faster to select.

    You can basically do the same type of select without having previous_image_id column, just calculate it in real time during the select but the select may take longer.
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  9. #9
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by lampcms.com View Post
    Why whould it matter how the records are sorted?
    I'll be glad to explain.
    Imagine, you have such table
    id | name
    ---------
    1 | Jaguar
    2 | Chrusler
    3 | GMC
    4 | Ford
    5 | BMW
    and you showing it sorted by name.
    Next after Ford would be Jaguar, not BMW.

    Relational model for our databases made to be very flexible. Any form of sorting or filtering can be applied just with output. But you set uncondition ordering. It is aganist the rules

  10. #10
    PHP Guru lampcms.com's Avatar
    Join Date
    Jan 2009
    Posts
    921
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think I understand what you are saying. In your situation the next and previous are not always the same.

    In my situation where I need to show next blog post and previous blog post, the order is always the same: next is always the record with higher id that also belongs to the same blog and previous is lower id that belongs to the same blog.

    In your situation you cannot use the method I suggested as you need to alwasy calculate the next and previous in real time, depending on the sort order.
    My project: Open source Q&A
    (similar to StackOverflow)
    powered by php+MongoDB
    Source on github, collaborators welcome!

  11. #11
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    there can be hidden posts and there can be multiple blogs in one table
    or one may look through blog posts under certain tag...

    yes, denormalization is possible, especially for performance reasons.
    but not this case when it needed, I believe

  12. #12
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm going to agree with SilverBullet...this is pagination, just with adjusting the limit of returned results to 1 in order to show one image at a time. You'd also have to adjust the output of the pagination to only show previous and next buttons instead of showing all the "pages" the query returns.

    The LIMIT sql command can determine the next and previous records to show, regardless of a unique id or other database field. Remember that record counting starts at 0.

    So, if you have this in your db table:

    id | image
    ------------
    1 | img1.jpg
    2 | img2.jpg
    4 | img4.jpg
    8 | img8.jpg

    and at the end of your query, you have:
    Code MySQL:
    LIMIT 2, 1
    it returns the 3rd record, so it will give you img4.jpg. The pagination previous button will adjust that to:
    Code MySQL:
    LIMIT 1, 1
    which will give you img2.jpg, while the next button will adjust it to:
    Code MySQL:
    LIMIT 3, 1
    will give you img8.jpg.

    This may seem like gibberish, but if you look in to pagination and build it, you'll see where the LIMIT command comes in to play.

    Just sharing what works for me, hope that helps!
    Cheers!
    No, I REALLY dislike having to use Joomla.

  13. #13
    derrrp
    Join Date
    Aug 2006
    Location
    earth
    Posts
    923
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to expand on the query part, the normal PDO query() method should be sufficient. Again, my example is a very simple, static representation. You'll discover how the LIMIT value is dynamically changed as you build your paginator.

    PHP Code:
    $dbh = new PDO($dsn$user$pass);
    $query "SELECT image ";
    $query .= "FROM images ";
    $query .= "ORDER BY id DESC ";
    $query .= "LIMIT 2, 1 ";
    $rows $dbh->query($query); 
    No, I REALLY dislike having to use Joomla.

  14. #14
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    crowden, one little correction.
    With this type of output we don't usually have such information as page number.
    Just look at the same teature of Sitepoint/vbulletin:
    The link is http://www.sitepoint.com/forums/showthread.php?t=645467&goto=nextnewest
    There is current and there is direction. There is no page number. No number - no limit.

    By the way, it shows another technic to do the thing.

    But your limit feature can reduce number of queries from 3 to 2.
    To retrieve current and next we can just make ">= $id LIMIT 2" instead of just "= $id"

  15. #15
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    crowden, one little correction.
    With this type of output we don't usually have such information as page number.
    Just look at the same teature of Sitepoint/vbulletin:
    The link is http://www.sitepoint.com/forums/show...oto=nextnewest
    There is current and there is direction. There is no page number. No number - no limit.
    ?t=2&goto=next
    ?t=3

    difference much?

    It really depends on exactly what you want to do. I think pagination is fine for this, but there are obviously other ways.

  16. #16
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    not much difference.
    but not much similarity to classic pagination too. for both
    as crowden stated above, the key to the classic pagination is LIMIT operator. but there is no place for limit in the prev-next task. there is no page number at all.
    pagination is used to display lists. not a single pages

  17. #17
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    not much difference.
    but not much similarity to classic pagination too. for both
    as crowden stated above, the key to the classic pagination is LIMIT operator. but there is no place for limit in the prev-next task. there is no page number at all.
    pagination is used to display lists. not a single pages
    Page number is just used to calculate where you are in the record set. ?t=645467 is just used to calculate where you are in the record set.

    I see no real difference, except semantics about pages having more than one item.

  18. #18
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No. ?t=645467 points to exact record. Static.
    Bookmarked it, returned in a week - the same page.
    Thant's the point.

  19. #19
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well, nextnewest, or nextbycolour isn't really specified. So assuming the order won't change, then the page number won't either. Of course it could if some were deleted, but then the exact record would point to nowhere.

  20. #20
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes. But imagine there was another record deleted, not bookmarked one
    What happened to page number then?
    Compare your theory to real world.

  21. #21
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Then it would turn into an argument as to whether it is better to show another image or show a 404.

    Here's a real example: http://abstrusegoose.com/
    Bookmark it, a week later it will be different.

    Quote Originally Posted by hash View Post
    It really depends on exactly what you want to do.

  22. #22
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    if you want to bookmark whole site, bookmark http://abstrusegoose.com/
    if you want to bookmark certain picture, there is "link for this comic" link for you
    isn't it obvoius?

  23. #23
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Your point was that a url always points to an image, so you can bookmark it and always return (assuming the image wasn't deleted).

    I gave you an example of an url that does not follow those rules. Perhaps I sent it to gran and she tried to bookmark it, and now can't get back to it.

    I see no reason why pagination couldn't work here. You don't have to actually use page number as the uri btw.

  24. #24
    Non-Member
    Join Date
    Oct 2009
    Posts
    1,852
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hash View Post
    Your point was that a url always points to an image
    Not any url.
    If you read starting topic, you'll see the point.
    Pagination with limit is not applicable here. Noone uses it. But you can, of course, if you wish.

  25. #25
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Shrapnel_N5 View Post
    Not any url.
    You introduced the spec that a url should point to a record. I simply illustrated how that was not so (from your bookmarking point of view).

    Quote Originally Posted by Shrapnel_N5 View Post
    If you read starting topic, you'll see the point.
    Pagination with limit is not applicable here. Noone uses it. But you can, of course, if you wish.
    I see you've stopped bolding stuff.


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
  •