[cgiapp] Q: Any guidelines for paging thru db records via CGI params?

[Stephen Carville]

On Sun, May 25, 2008 at 6:37 PM, Ron Savage <ron at savage.net.au> wrote:
> Hi Folks
>
> Let's say I offered the user a form with 2 fields, id and (Winemaker's)
> name.
>
> The user enters B% for the name's value, and, using Rose, I know there
> are 18 matching records.
>
> But it happens I'm using a per_page of 10 (say) to call
> get_winemaker_iterator(), so the first call returns records 1 .. 10 to
> the user.
>
> What's a good way to display buttons so the user can page forward to get
> records 11 .. 18, and (later) back from page 2 to page 1?

Does your SQl support the LIMIT command?  MySQL and PostgreSQL both do
but with slightly different syntax.  I think Oracle does too but SQL
Server does not.

You could do somehing like (MySQL):

select count (*)
from   wherever
where whatever=whomever;

This returns a total number of rows: $lastrow.

Then make the first call

$startrow=0;
$display=10;

select stuff, more_stuff, last_stuff
from    wherever
where whatever=whomever
order by whatever
limit  $startrow, $display;

This get you the first ten rows so next page would be

$startrow+=$display;

select stuff, more_stuff, last_stuff
from    wherever
where whatever=whomever
order by whatever
limit  $startrow, $display;

 You have to track $lastrow anyway to know where to stop displaying
next in the form (well not really but the logic is tricky) so you
could create a drop down box to jump to any page.

If your DB has plenty of temp space then the extra time for the LIMIT
command is small.

-- 
Stephen Carville