Thread: 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 throughrecords. 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 belinks to pages 2 through 7 as well, just like with results pages of a Google search. They could, from page 1, click anyof 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 postgreSQLdoesn't seem to have. I know how to use LIMIT, but that always starts from the top. I could add a piece to theWHERE 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 sortedrecord 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
Sort of depends on the nature of your application. You can use offset to get specific chunks: select * from foo order by date limit 100 offset 100; You should be aware, however, that on a very large table this can be quite inefficient as you will have to do the select and sort on the large table just to get the next chunk. (With only 600 tuples you will probably have everything in memory anyway so it's not really a problem.) Also, if the data is actively updated you could get a tuple added or deleted between page views which would mess up the offsets and cause someone to miss an item or get a duplicate. If you want to page through a small subset of a large file you can use cursors or temporary tables but you will have to be sure your connection persistence, session management and such can accomodate such an arrangement. Cheers, Steve On Friday 20 December 2002 12:53 pm, cmccormick@mailsnare.net wrote: > 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
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 usea 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@mailsnare.net> To: <pgsql-sql@postgresql.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
Steve Crawford schrieb: > select * from foo order by date limit 100 offset 100; > On Friday 20 December 2002 12:53 pm, cmccormick@mailsnare.net wrote: > > 1. SORT records by the date field, descending select * from foo order by date desc limit 100 offset 100; ^^^^