jwlarson3rd has asked for the wisdom of the Perl Monks concerning the following question:

I am building a image gallery with multiple users. I have a page which generates all of the images for each user. when I click on a image a larger image open up in its own page. what I need is next prev links on this page all of my information is in a mysql table which has the following setup username and multiple unique filenames(images).what I need to do is when the next or prev link(button) is clicked on the next or previous images is displayed. how do I get the next record and previous record. do I have to add a numeric id. john larson

Replies are listed 'Best First'.
Re: next prev record mysql table
by jdtoronto (Prior) on Dec 07, 2003 at 13:59 UTC
    Despite what has been said in another reply - NEVER RELY ON THE ORDER IN WHICH DATA IS RETURNED. In MySQL it is possible that ANY change of the data in a table will change the order of items being returned

    I would have an indentity field in each record which could be an auto-incremented value (one counter for all users) then when you select the records for a single user you can retrieve them ordered by ID. Then do one of two things, either embed the ID for the next and previous. Or you can do a query which asks for ID > lastID for the next record or ID < lastID for the previous.

    This way, the user could add a new image while somebody is scanning through them and the new one would be found at the end.

    Their are a number of more sophisticated ways to handle this. I wrote a gallery for internal use here a couple of years ago. The user uploads the images in any order they like, then Using a simple interface they can create the sequence they wish the images to appear in. This creates a hash which is stored in their user record which maps sequence number to ID. They can edit the list to insert a new image into the sequence or remove one.

    Good luck! jdtoronto

[reply]
Re: next prev record mysql table
by tcf22 (Priest) on Dec 07, 2003 at 00:00 UTC
    The best move would probably be to add an identity field, and just grab the next/prev image by ID. You could also use an ORDER BY, to order by filename, date, or something else, which would make sure they come out in the same order, so you could make sure the next/prev image is correct.

    - Tom

[reply]
[d/l]
Re: next prev record mysql table
by bradcathey (Prior) on Dec 07, 2003 at 00:09 UTC
    Try using the LIMIT feature of SELECT as in: 
        $stmt = "SELECT * FROM tablename LIMIT $place,1";

    [download]
    where $place is equal to what record you want. You will need to keep track of what record you are on by writing to a hidden input value in your html or writing a cookie. Of course, you have to do the maintenance of testing for if your are displaying the first record, or the last record and whether to show a Previous button or a Next button. I get the total number of records with: 
        $stmt = "SELECT COUNT(*) FROM tablename";

    [download]
    See my scratch pad for how I did this showing a list of book "reviews" with MySQL and HTML::Template.

    —Brad
    "A little yeast leavens the whole dough."
[reply]
[d/l]
[select]
      I really don't like this idea for one reason. Your data is going to move and shift, so your pages change and shift.

      It's probablybest that you keep track of the last element retrieved. For the first page:

      select * from table order by something limit 10

      and for next pages, do

      select * from table  where value > lastValueFromPriorQuery order by something limit 10

      It's a pain in the ass when people add data or delete it and your pages go a little nutty. Your idea is great if your data doesn't change.

[reply]
[d/l]
[select]
        I see your point, however I seems that because I am only grabbing and displaying one record at a time, and keeping track of it, I shouldn't have any problems other than an unexpected record displaying when cycling back through previous records. But I'm open to refinement if any one has a safer/cleaner way of doing this.

        —Brad
        "A little yeast leavens the whole dough."
[reply]

Back to Seekers of Perl Wisdom