http://www.perlmonks.org?node_id=1078717


in reply to Perl Database Paging

I'm puzzled by the first "if" condition at the top - you do the same thing no matter what the "request_method" is, so why is the "if" statement there at all?

Apart from that, when you call the "header" sub, you call CGI->new, and then when you call the "display" sub right after that, you call CGI->new again -- but it's not clear to me that the second call will yield all the same stuff that the first one did, so that might have something to do with the problem.

I'd suggest that you restructure things a little bit at the top, like this: 

#!/usr/bin/perl -T

# You do have taint mode turned on, don't you? and the next two lines,
+ too?

use strict; 
use warnings;

my $cgi_obj = new CGI;

my $page = $cgi_obj->header( 'text/html' );
$page .= display( $cgi_obj );

print $page;

sub display
{
    my ( $query ) = @_;
    my $html = qq~
... (initial boilerplate stuff for the page) ...
~
    # run your dbi queries...
    # append more html stuff to $html as you go, and finally:

    $html .= qq~
</body>
</html>~;

    return $html;
}

[download]
Another problem is that when you format your paging links, you are printing "page", followed immediately by a number, (e.g. "page1", or "page2"), and that becomes the name of the url parameter. But when you handle the cgi query params on a subsequent (GET or POST) request, you're looking for a parameter named "reqpage", which was never put into your page links. That would certainly lead to disappointment.

Replies are listed 'Best First'.
Re^2: Perl Database Paging
by ironside (Acolyte) on Apr 02, 2014 at 23:31 UTC

    Thanks for your reply graff. The first if statement was because I parsed the form at one point. I have cleaned up the script, and got rid of anything I don't need and I am still having the same problem. I tried putting reqpage in the page links, but still no luck.

    I'm thinking the problem is with the links. I am having a hard wrapping my brain on how to create these links properly and what exactly I should be putting in them. I'm sure I am close to getting this working, so any help would be much appreciated. I have attached the updated script.
    Thanks in advance. 

    #!/usr/bin/perl

use CGI qw(:standard);
use DBI;
use warnings;
use strict; 

my (@row,$name,$path,$dsp,$count);


my $query = new CGI;
print $query->header('text/html');

print qq~
<!DOCTYPE html>
<html>
<head>
<title>Movie Search</title>
</head>

<body>

<br /><br />~;

    my $reqpage = $query->param('reqpage') || '1';    
    param('per_page');
        
    #Get total amount of rows from db
    my $dbh=DBI->connect($connectionInfo,$user,$passwd);
    my $num_rows= $dbh->selectrow_array('select count(name) from video
+ order by name');
    my $num_results_perpage = 25;
    
    # calculate the number of pages to show
    my $pagecount = int($num_rows / $num_results_perpage);
    if (($pagecount * $num_results_perpage) != $num_rows)
    {
        $pagecount++;
    }
    
    # calculate which results to show in the page
    my $firstresult = (($reqpage - 1) * $num_results_perpage) + 1;
    my $lastresult = $firstresult + $num_results_perpage - 1;
    if ($lastresult > $num_rows)
    {
        $lastresult = $num_rows;
    }
    
    # sql limit starts at 0
    my $start_point = $firstresult - 1;
    
    my $sth = $dbh->prepare("Select name,path from video order by name
+ LIMIT $start_point,$num_results_perpage"); 
    $sth->execute();
    while (@row = $sth->fetchrow_array())
    {
        $name = $row[0];
        $path = $row[1];
    
        $path =~ s/'/%27/g;
        $dsp = substr $path, 27, 255;
        print "<a href='/media/$dsp'>$name</a> <br />";
        
        $count=$count+1;
    }
    $sth->finish();
    
    # page links
    my ($prev_link, $next_link, $pagelinks, $pageno, $thislink, $pages
+ize);
    my $prev_page = $reqpage - 1;
    my $next_page = $reqpage + 1;
 
    if ($reqpage == 1)
    {
        $prev_link = "";
    }
    else
    {
        $prev_link = " <a href=\"http://path/to/script.cgi?reqpage=$re
+qpage=$prev_page\">". "previous" . "</a>";
    }
    if ($reqpage == $pagecount)
    {
        $next_link = "";
    }
    else
    {
        $next_link = " <a href=\"http://path/to/script.cgi?reqpage=$re
+qpage&page=$next_page\">". "Next" . "</a>";
    } 

    if ($pagecount > 1)
    {
        $pagelinks = $prev_link;
        $pageno = 0;
        while ($pageno < $pagecount)
        {
            $pageno++;
            if ($pageno == $reqpage)
            {
                $thislink = " <strong>$pageno</strong> ";
            }
            else
            {
                $thislink = " <a href=\"http://path/to/script.cgi?reqp
+age=$reqpage&page=$pageno\">" . $pageno . "</a>";
            }
            $pagelinks = $pagelinks . $thislink;
        }
        $pagelinks = $pagelinks . "   " . $next_link;
    }
    else
    {
        $pagelinks = "";
    }

print "<br />";    
print $count . "<br /><br />";
print $pagelinks . "<br />"; 
print "Database Results: " . $num_rows . "<br />"; 
print "Page " . $firstresult . "  Of&nbsp;&nbsp;" . $pageno;
print qq~
<br />
<br />
</body>
</html>~;

    [download]
[reply]
[d/l]
      If that's the code you're trying to run, I see a few problems. I'll take it for granted that you've just edited out the declarations and assignments for the database connection strings, and when I put dummy assignments back in, the script compiles ok. But…
      • Line 26 might cause a runtime error (param('per_page');) at best, it does nothing (except add confusion) -- have you checked the web server error log after the script runs? You might try adding use CGI::Carp 'fatalsToBrowser';
      • I'd be very suspicious/nervous about the assignment to $dsp at line 59, esp. because you're using hard-coded numbers in a substr call, right after changing all apostrophes to "%27" -- how confident are you that substr() is returning what you really want? I'll bet there's a better way to get what you want.
      • The "href" value that you assign at line 77 looks wrong -- I think you left out &page before the second '='
      Eventually you'll want to switch over to using a template module - store your page layout as an HTML(ish) plain-text template file, and let the module plug in all the context-dependent variables for you. But that's a slightly bigger step for later.
[reply]
[d/l]
[select]

        Problem solved! Another perl monk was able to help. It turns out the links were the problem. Here is the code that worked:

        # page links
my $pagelinks;
 
if ($reqpage > 1){
  my $prev_page = $reqpage - 1;
  $pagelinks .= qq! <a href="?reqpage=$prev_page"> Previous </a> !;
}    
    
for my $pageno (1..$pagecount){
  if ($pageno == $reqpage){
    $pagelinks .= qq! <strong>$pageno</strong>!;
  } else {
    $pagelinks .= qq! <a href="?reqpage=$pageno"> $pageno </a> !;     
  }
}
    
if ($reqpage < $pagecount){
  my $next_page = $reqpage + 1;
  $pagelinks .= qq! <a href="?reqpage=$next_page"> Next </a> !;
}

        [download]

        Thanks for your input. Much appreciated

[reply]
[d/l]

        Thank you for taking the time to help me with this problem. I did edit out the db connection info. Connecting and outputting the first page of results works fine.
        I added line 26 when I was playing around with the values in the links. I have a feeling that's where the problem may be.
        Line 77 was syntax error I missed when changing the link back to how it was originally.
        On line 59 I have to remove all apostrophes from the name and replace them with "%27" so that it works in a link. I'll look into if there is a better way to do this.
        I have checked the error log and there was one minor error that I fixed, but it didn't fix the problem.
        I use Template Toolkit for storing my page layout, but right now I'm just trying to get paging working.
        Do you think it's possible why the links are working is because of the values I put after the question mark in the url?

[reply]

In Section Seekers of Perl Wisdom