Viewing data one page at a time

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Dear Monks, I'm currently working through the Perl Cookbook trying various recipes and am working on 14.6 - Viewing Data One Page at a Time. I have a database of around 25,000 lines and want to be able to limit the view so that a user can call a single word and view it from there whilst being limited to 50 results per page. The cgi script works but I fear I've gone horrifically wring on the fetching and presenting from the db part of the code

#!c:\perl\bin\perl.exe

use strict;
use warnings;
use DBI;
use CGI;

my $q=new CGI;

my %sm = $q->Vars();

my $word = $sm{concordance};


print $q->header();
print $q->start_html(-title=>'Dickens Concordance');
print qq[<form method="post"action="/cgi-bin/view.pl" name="concordanc
+e">];



#code to create the scroll down box which selects from the db
      my $db = DBI->connect('dbi:mysql:lit:localhost', 'user', 'pw',
        {RaiseError => 1, AutoCommit => 1});
      my $sth = $db->prepare("SELECT DISTINCT word FROM dickens");
      
      
      
      $sth->execute;
      
      my @data;

      while (my @result = $sth->fetchrow_array)
      {
       push @data,$result[0];
       }
      print $q->scrolling_list(-name=>"concordance", -value=>\@data, -
+size=>30);
          
    $sth->finish;
      $db->disconnect();
      
      print $q->submit(-value=>"Choose");
  
  print qq[</form>];




#Count number of lines in db
$db = DBI->connect('dbi:mysql:lit:localhost', 'user', 'pw');

my $row = $db->selectrow_arrayref('SELECT COUNT(*) FROM dickens');
my $count = $row->[0];

my $Page_Size = "50";
my $first = $q->param("start") || 1;
my $last = $first + $Page_Size - 1;
$last = $count if $last > $count;


#Query the db
my $results = $db->selectall_arrayref('SELECT word ,line FROM dickens 
+WHERE word="$concordance" LIMIT 50');


for (my $i=$first; $i<= $last; $i++) {
 my $user = $results->[$i-1];
 printf("%d <br />\n", $i, $user);
}

#Create the next and previous pages
my $prev_rec = $first - $Page_Size;
$prev_rec = 1 if $prev_rec < 1;
my $prev_link = sprintf('%s/%d', url(-full => 1), $prev_rec);
my $next_rec = $last + 1;
my $next_link = sprintf('%s/%d', url(-full => 1), $next_rec);

if ($first == 1) {
print "Previous";
} else {
printf('<a href="%s">Previous</a>', $prev_link);
}
print "|";
if ($next_rec < $count) {
printf('<a href="%s">Next</a>', $next_link);
} 
else {
print "Next";
}
[download]

I'd be grateful for some pointers as to where I've gone wrong so that I can get the results
word line number
word line number
presented on the screen. Many thanks in advance.

Comment on Viewing data one page at a time Download Code

Replies are listed 'Best First'.
Re: Viewing data one page at a time by roboticus (Chancellor) on Feb 29, 2008 at 14:47 UTC
My suggestions are: Reuse your database connection rather than disconnecting and reconnecting. As described in DBI, you shouldn't need to use `$sth->finish()`. You're single-quoting your query and simultaneously trying to get perl to substitute `$concordance`, which won't work. Change your quoting method. I'm not knowledgeable about mySQL, but I don't see how you're going to switch between pages. I would think that you'd need a sortable key column (that you don't have to show the user) so you can tell mySQL where to begin the `SELECT` in order to page through it. To illustrate the last point, here's what I mean. Suppose you have the table: `create table Names ( the_key int PRIMARY KEY, last_name varchar(32), first_name varchar(32) )` [download] Now if you want to present the names, you might have a bit of code something like: `# Setup code my $SortField = 'last_name'; # could be 'first_name' my $Page = ''; my $PageSize = 50; <<SNIP SNIP SNIP SNIP SNIP>> # Get the page of data my $SQL = "SELECT first_name, last_name " . "FROM Names " . "WHERE $SortField >= '$Page' " . "ORDER BY $SortField " . "LIMIT $PageSize "; my $results = $db->selectall_arrayref($SQL);` [download] Then in your code, when the person hits the NEXT PAGE button, you simply set $Page to the value of the last $SortField value in your current page. (To move in reverse, have mySQL sort the data in reverse and use the same logic. But after you get your results, reverse the results array so you don't confuse your user. ...roboticus	[reply] [d/l] [select]
Re: Viewing data one page at a time by spatterson (Pilgrim) on Feb 29, 2008 at 15:59 UTC
You need to use offset with limit, to select where in the results to display from. You should also order the SQL results by some useful field or they will simply show in database insertion order. `my $results = $db->selectall_arrayref('SELECT word ,line FROM dickens +WHERE word="$concordance" ORDER BY line LIMIT 50 OFFSET 50');` [download] just another cpan module author	[reply] [d/l]