From: cmccormick@mailsnare.net
To: pgsql-sql@postgresql.org
Subject: Need help paging through record sets
Date: Fri, 20 Dec 2002 20:53:26 +0000
Message-ID: <W17018311537171040417606@mail.mailsnare.net> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: cmccormick@mailsnare.net
Subject: Re: Need help paging through record sets
In-Reply-To: <W17018311537171040417606@mail.mailsnare.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox