Re: Need help paging through record sets

I have an almost identical application, but I am using Apache::ASP instead of PHP. Apart from the language differences, I
suspect the ideas are the same.

What I have done is store the *entire* list of results in a session variable with a clock-time. When I get a new query
(...?page=2), I check if the query args are the same (I only have two) and to see if the results are still timely (I use a 30
second timeout, but whatever is good for you). If they are OK, slice the array/list (in perl) else do a new query and use
those results.

I have yet to add sorting, but that becomes another key in the 'valdation' of the stored list.

As my entire site is driven through the use of MD5 checksums to identify photographs (rather than files names or hierarchies)
all I end up carrying around is (at most) 100 or so 32 character strings in the session store. A few thousand results should
be fine for most medium sized servers I guess.

Peter

----- Original Message -----
From: <cmccormick(at)mailsnare(dot)net>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Friday, December 20, 2002 8:53 PM
Subject: [SQL] Need help paging through record sets

Hello everyone, and thanks for reading my first newbie post. :-)

I am a neopyhte PHP and postgreSQL user, with a website at www.the-athenaeum.org. We store (among other things) artworks,
which people can view in a list, sorted by artist name, date, medium, etc.

We now have enough works that I need to rewrite the PHP listings script (and its embedded SQL) so that users can page through
records. As an example, if a user is looking at works by date ascending, they may want to see 100 records at a time. Since
we have 600+ records, there would be 7 pages. They'd start on the first page (of course!) and there would be links to pages
2 through 7 as well, just like with results pages of a Google search. They could, from page 1, click any of the other pages
to go immdiately to that set of 100 records for display.

I see this kind of thing all over the place, and in looking it up, I see most solutions use "SELECT TOP x", which postgreSQL
doesn't seem to have. I know how to use LIMIT, but that always starts from the top. I could add a piece to the WHERE
clause, say something like "WHERE date > 01-02-1853", but how do I know where the cutoffs are several pages along, without
retrieving the whole record set?

I suppose the optimal solution for me would be to sort all of the records, then be able to select a range from that sorted
record set. So, if they click the link to page 3, I'd like to do this (in pseudocode):

1. SORT records by the date field, descending
2. Retrieve only records 200-299 from the sorted list

Is there a way to do that? How is it done elsewhere?

Thanks in advance for your help,
Chris McCormick, webmaster
The Athenaeum - Interactive Humanities Online
www.the-athenaeum.org

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster