[SOLVED] Next/prev on paging?

[Strahan]

I am building a photo gallery page, and I'm using limit to do paging.  Right now I do something like:

select id,filename from photos where album = 2 order by filename limit 1,16

 

Which works great.  Now when you click the photo, I want a "prev" and "next" button to move between individual photos.  Prev doesn't strike me as very hard, on the album view I can have it store the current id in a var then for the next photo, have it pass that id as "prev".  However next is where I'm not sure the most efficient way to proceed.  My plan right now is to just select all records from the album and scroll through them until I find the current id then capture that way, but it seems horribly inefficient.

 

What's the best way then to grab the neighboring records?

[corbin]

Well, the basic concept is this:

 

 

I'm on page x, and I need to see a number of results, y.  The results I see need to be based on x and y.  Let's say x is 3 and y is 10 (10 results per page, 3rd page):

 

(x-1)y+1 through xy

21 - 30 in this case.

 

Page 1 would be 1-10 (which would actually be 0-9 in array land)

Page 2: 11-20

Page 3: 21-30

 

So on....

 

So, in PHP land, the offsets would be:

 

(page-1)*y through page*y-1

(20 - 29 on page 3)

http://www.phpfreaks.com/forums/index.php/topic,224652.msg1038409.html#msg1038409

 

 

You need to know the maximum number of rows before you start, so people can't put in page 5000.  Then, once you know the max rows, you need to know the max pages.

 

The minimum is easy, since it's 0.  If you handle things by pages (1, 2, 3, so on) instead of by item (1, 25, 50, or whatever) it's much easier to handle.

[Strahan]

Thanks.  Sorry but now that I reread the original post I see I wasn't very clear..  My setup is an album page where it shows thumbnails, then you click the thumbnail and get a detail page with a full size image and some other data.  My problem isn't the actual page ranging per se, I have the page next/prev working on the album view, that was fairly easy as it is based on an arbitrary counter.  However, my dilemma is record based - the prev/next once you are in the detail view.  On the first page, the album view, it shows say 4 records like this:

 

page 1:

img src=img1 onclick=id.value='1092';submit();

img src=img2 onclick=id.value='1093';submit();

img src=img3 onclick=id.value='1102';submit();

img src=img4 onclick=id.value='1118';submit();

 

As you can see, ID isn't a straight increment it could skip so I can't just +/- the id code.  So they click img2 and get the detail page with a pic that has id 1093.  Prev button has to = 1092 to go back one, and next button has to = 1102 to go forward one.  That's where my problem comes in, because on the detail page I have only an id number (index of the picture database) to go by, it's not being pulled in a set.

 

Right now I'm doing:

  case "detail":
   // this is the start of the detail page.  fold[id] is the index of the album so it can get all pics
   // request[id] is the picture index id of the image they clicked on the album view
   //
    $rs = $db->query("SELECT fileid FROM Media WHERE parent = {$fold["id"]} ORDER BY filename"); $grab = false;
    while ($row = $rs->fetch_assoc()) {
      if ($grab) { $next = $row["fileid"]; break; }
      if ($row["fileid"] == $_REQUEST["id"]) { @$prev = $tmp; $grab = true;}
      $tmp = $row["fileid"];
    }
    $rs->free();

 

Which works, but strikes me as kinda "kludgy" hehe.

[ohitsme]

you could do a

select * from table where id < $currentitemid limit 1

for previous

 

or

select * from table where id > $currentitemid limit 1

for next

 

(you might want to include a order by datefield if it is ordered by date, not by id)

[ohitsme]

oh, and you need to set sort by desc for previous (and sort by asc for next)

[Strahan]

Ooooh, that's perfect!  Much much better than my hack job. 

 

Man I love this place   Thanks alot!