Thread: Re: Paging through records on the web

>> Where in postgresql you would say
>>
>>       select * from foo limit 20
...
>
> Related question: A typical web search interface
> needs a page based browsing system where you
> can list the 10 next matches...
> I'm thinking of something like :
>
> select * from foo where <some search criteria> and
>        rownum >= 30 and rownum < 40

> Or is this where I should look into using
> cursors to access the result set ?

I don't think cursors will work without some
very fancy back-end programming to match up each successive web-page request with
_the same_ process so you have somewhere
to maintain that cursor.

As an alternative, you're almost certainly presenting the records
in sorted order, so you may be able find a unique or almost-unique
set of fields to control the starting record.

The following is from a PHP application that displays a user's login
history in reverse order.  '$_init_date' is sent via a hidden variable
when the user presses the MORE submit button at the end of each page:

   $max_time_rows = 20;
   if ( $_init_date == '' )
      {
      $qry = "select checktime, status from checkin \
         where acct = $_acct \
         order by checktime desc limit $max_time_rows";
      }
   else
      {
      $qry = "select checktime, status, from checkin \
         where acct = $_acct and checktime <= '$_init_date' \
         order by checktime desc limit $max_time_rows";
      }

Sure, it's theoretically possible that there will be 2 or more
rows with the exact same login or logout time, but it's unlikely,
and the only harm that results is carrying forward the bottom
row or two onto the next page.



Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com

Alternatively, you could do your search once, store the results in a table
along with a search id number, and then just SELECT ... FROM search_cache
WHERE searchId = $SearchID LIMIT $X OFFSET $Y  That would cut down on the
processing time of a  complex search (ie, searching a product database
based on multiple text fields)

I've been meaning to do this for a search system we run here, but haven't
gotten around to it.  I'm looking for a guinea pig to see if it's worth the
effort. :)  Alternatively, instead of storing the whole result tuple, you
could just store the OID's and do a SELECT fields FROM search_table WHERE
oid IN (SELECT sved_oid FROM search_cache WHERE searchId = $SearchID LIMIT
$X OFFSET $Y)  Easier to implement, and probably not too much slower.

Any PostGres gurus out there who could hazard a guess as to what sort of
speed increase you'd get by searching based on one int (like the OID or
search_id) in comparison to one or two text/varchar fields?

At 03:02 PM 6/8/00, K Parker wrote:
> >> Where in postgresql you would say
> >>
> >>       select * from foo limit 20
>...
> >
> > Related question: A typical web search interface
> > needs a page based browsing system where you
> > can list the 10 next matches...
> > I'm thinking of something like :
> >
> > select * from foo where <some search criteria> and
> >        rownum >= 30 and rownum < 40
>
> > Or is this where I should look into using
> > cursors to access the result set ?
>
>I don't think cursors will work without some
>very fancy back-end programming to match up each successive web-page
>request with
>_the same_ process so you have somewhere
>to maintain that cursor.
>
>As an alternative, you're almost certainly presenting the records
>in sorted order, so you may be able find a unique or almost-unique
>set of fields to control the starting record.
>
>The following is from a PHP application that displays a user's login
>history in reverse order.  '$_init_date' is sent via a hidden variable
>when the user presses the MORE submit button at the end of each page:
>
>    $max_time_rows = 20;
>    if ( $_init_date == '' )
>       {
>       $qry = "select checktime, status from checkin \
>          where acct = $_acct \
>          order by checktime desc limit $max_time_rows";
>       }
>    else
>       {
>       $qry = "select checktime, status, from checkin \
>          where acct = $_acct and checktime <= '$_init_date' \
>          order by checktime desc limit $max_time_rows";
>       }
>
>Sure, it's theoretically possible that there will be 2 or more
>rows with the exact same login or logout time, but it's unlikely,
>and the only harm that results is carrying forward the bottom
>row or two onto the next page.
>
>
>
>Join 18 million Eudora users by signing up for a free Eudora Web-Mail
>account at http://www.eudoramail.com