View 100 then click for rest

[Kiko]

Hi,

I have a database that contains several thousands of records.
I have a function that allows the user to view the records
depending on what their search string is.  Lets say that i
chose to view the database by year, then title, then description.
I would get about 111 records.  I only want to display 100
records and then show the remaining records.  This is what
i have thus far:


if ($count>$visible){
    print "<font size=2 face=helvetica>
               <a href=\"$ENV{'SCRIPT_NAME'}?$ENV{'QUERY_STRING'}&min=
+100\">
               Next (what ever is left) </a></font>";
}

It works in the sense that it only shows 100, but then again
it doesn't cause i don't know how to show the remaining records.

ThanX!
Kiko

[btrott]

Keep track of where the user is in the results by passing along that information in the query string. For example, you might pass along a "start=N" parameter, where N is the record that you want to start viewing on, for a particular page.

Then when you select your data out of the database, use that value for the start parameter to index into the results. Your database probably has a method of starting results at a particular record. In MySQL, for example, you could say:

    select foo from bar LIMIT 100,100

Update (thanks, le): The first number in the LIMIT statement is the record number to start at; the second is the maximum number of records to return.

If you're using Oracle, look into the special I<rownum> column.

[le]

The first number in the LIMIT statement is the maximum number of records to return; the second is the record number to start at.

It's the other way round: first is offset, second is rows. (Offset starts at 0!)

[lhoward]

To implement this you will need to use some sort of counting logic in your element printing logic. The following may not be actually functional, but can act as an starting point for your code.

my $min=...  or 0;
my $max=$min+100;

my $row=0;
while(@r=$sth->fetchrow()){
  if(($row>=$min)&&($row<$max)){
    #print some data
  }
  $row++;
}
$sth->finish();

if($row>$max){
  # print next link w. "min=$max"
}

[le]

Nice, but why return the full set, when you're only interested in some columns?

my $offset ||= 0;
my $max = 100;

my $sth = $dbh->prepare("SELECT ... LIMIT ?,?");
$sth->execute($offset, $limit);

[lhoward]

Not all databases support "LIMIT" as part of the SQL language. I agree that if your DB does support LIMIT then you should use it, but if you want a truly DB independant solution (much as DBI is database independant) you should avoid LIMIT and other not-widely supported extensions.

[merlyn]

I did the CGI part of this at my WebTechniques column on paging more items. That could be a good start.

-- Randal L. Schwartz, Perl hacker