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