Posted by Connector5 on 11/08/89 11:34

Your image gallery search code should be reproducable, right?

I mean, if you know that you want page 3 you can instantly pull up page 3,
right? If not, you may want to rethink your schema.

When I am doing pagination, I use the sql offset commands to specify a
rowstart and a rowcount to return. That way, I only need to know that I was
on page 3 and I can set a returnto=search&page=3 schema.

MySQL: http://dev.mysql.com/doc/refman/5.0/en/select.html
PostgreSQL: http://www.postgresql.org/docs/7.4/static/sql-select.html


PHP:


$offset = (($pictures_per_page * $page) - $page);

// Very good habit to get into with queries
$offset = (int) $offset; // For strings: $offset = (string)
mysql_escape_string($offset);
$page = (int) $page; // For strings: $page = (string)
mysql_escape_string($page);

/* MySQL */ $result = mysql_query("select * from `image_table` order by
`submit_date` desc limit '$offset','$pictures_per_page'");
/* PgSQL */ $result = pgsql_query("select * from \"image_table\" order by
\"submit_date\" desc limit '$pictures_per_page' offset '$offset'");

// Extra credit
/* MySQL */ if (mysql_num_rows($result) == 0) { /* Show Error Page or maybe
page 1? */ }
/* PgSQL */ if (pgsql_num_rows($result) == 0) { /* Show Error Page or maybe
page 1? */ }



NOTE: The offset formula sometimes will raise a derelict page. Like 2
pages when you only have 15 results, and the second page will be blank.
This is not consistently reproduceable, so you may want to query the last
page automatically and see if it has results, and if so, then leave out the
page number. Maybe someone else can offer a better solution to that.


"Jon" <jonra@netins.com> wrote in message
news:dnkp1g$um2$1@news.netins.net...
> Ok, I'm so close to being done with this application, but have run into
> something I simply cannot seem to fix. Here's the deal:
>
> I have a MySQL driven gallery page, that displays 15 images per page
> (thumbnails, in rows of 5). If the page goes over 1, there's a
next/previous
> button with numbers of each page at the top.
>
> So far so good. I also have a page that displays the full version of the
> images - with a next/previous button to rotate through the images. As you
> may have seen in my previous posts, I'm tracking where I'm at in the
> recordset returned from the DB via an array storing each file name, so the
> next button and previous button are all built using a variable counter of
$i
> so I know exactly where I'm at in the recordset from the DB. Each time you
> click next, the query is run again, the array of file names is rebuilt,
and
> $i is incremented to the next value in the data returned.
>
>
> Alright, here's where it gets ugly - I WAS passing $i from the actual
> gallery display (the thumbnails) so when the user clicked on a thumb, it
> would query the full list of photos and we'd always be at $i on the other
> side, therefore we'd always know were we were at in the array of images.
>
> The problem is now trying to pass $i from the multiple pages - I've tried
> incrementing $i by 15 (the total number of photos per page), and though
the
> next button and previous buttons work, the numbered links do not work - in
> particular when you go BACK a number - so if I'm at 4, and I click on page
> 3, $i is incremented wrong, and everything blows up.
>
> Mainly, I'm wondering if anyone has built an application like this and has
a
> better algorithm for passing this data - If anyone has a clear idea on
what
> I'm needing, or thinks the code will help, I'll post any code you need
(it's
> quite a bit, so I figured I'd start with just a description of the
problem).
> Does anyone have some advice for me here on a better way to do this, or a
> way to fix the algorithm I'm currently using? I'm desparate :(
>
>

[Back to original message]