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

I am once again in need of Perl Monk wisdom. I am having a hard time wrapping my brain around database paging. I have found a few examples on the internet but they don't quite work for me.

With every example I have tried, the problem is always the same. I can output the database results and the correct number of page links depending on the results of the page. The problem is when I click on a page link, it goes to the next page but with no results and the page links disappear. Here is my code: 

if ($ENV{REQUEST_METHOD} eq "GET")
{
    &header();
    &display();
}
else
{
    &header();
    &display();
}

#########################
###   Print Header   ####
#########################
sub header {

    $cgi_object=new CGI;
    print $cgi_object->header('text/html');
}

#########################
###   Display Page    ###
#########################
sub display {

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

<body>
<form name="login" method="POST" action="/cgi-bin/search.cgi" />
<input type='submit' name='submit' id='submit' />
<h2>How Many Results Would You Like To See Per Page?</h2>

                <select name="per_page" id="per_page">
                <option value="25">25</option>
                <option value="50">50</option>
                <option value="75">75</option>
                <option vaule="100">100</option>
                </select>
<br /><br />~;

    my $query = new CGI;
    #$pagesize=$query->param('per_page');
    my $reqpage = $query->param('reqpage') || '1';    
    
    #Get total amount of rows from db
    $dbh=DBI->connect($connectionInfo,$user,$passwd);
    my $num_rows= $dbh->selectrow_array('select count(name) from video
+ order by name');
    my $num_results = $query->param('per_page');
    
    # calculate the number of pages to show
    my $pagecount = int($num_rows / $num_results);
    if (($pagecount * $num_results) != $num_rows)
    {
        $pagecount++;
    }
    
    # calculate which results to show in the page
    my $firstresult = (($reqpage - 1) * $num_results) + 1;
    my $lastresult = $firstresult + $num_results - 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"); 
    $sth->execute();
    while (@row = $sth->fetchrow_array())
    {
        my $name = $row[0];
        my $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://Server_IP/cgi-bin/search.cgi/p
+age$prev_page\">". "previous" . "</a>";
    }
    if ($reqpage == $pagecount)
    {
        $next_link = "";
    }
    else
    {
        $next_link = " <a href=\"http://Server_IP/cgi-bin/search.cgi/p
+age$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://Server_IP/cgi-bin/searc
+h.cgi/page$pageno\">" . $pageno . "</a>";
            }
            $pagelinks = $pagelinks . $thislink;
        }
        $pagelinks = $pagelinks . "   " . $next_link;
    }
    else
    {
        $pagelinks = "";
    }

print "<br />";    
print $count; print "<br /><br />";
print $pagelinks . "<br />"; 
print qq~
<br />
<br />
</body>
</html>~;
}

[download]

Any ideas on where I'm going wrong?
Thanks in advance.

Replies are listed 'Best First'.
Re: Perl Database Paging
by graff (Chancellor) on Mar 18, 2014 at 02:31 UTC
    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.
[reply]
[d/l]
[select]

      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]
Re: Perl Database Paging
by howdoyouperl (Initiate) on Jun 15, 2016 at 16:06 UTC
    Incase you still need help with this, here is some paging code I came up with that works off a total count of results how you get that is up to you... This code works with the bootstrap 3 pagination if you want different button styling you would have to figure that out. Also it displays 5 buttons at a time and styles the active page differently. Starts at page "0" 
    my $result_count = (however you want to get a total result count);

    if (!$page) {$page = '0'}
    
    my $offset = ($page * $max_page_results );
    my $high_offset = ($offset + $max_page_results);
    my $total_pages = (ceil($result_count / $max_page_results) - '1');
    my $next_page = ($page + '1');
    my $previous_page = ($page - '1');
    my $starting_page = ($page - '2');
    
    #Setting limits to 0 and high page count
    if ($high_offset > $result_count) {$high_offset = $result_count;}
    if ($page == '0' ) {$previous_page = '0';}
    if ($next_page >= $total_pages) { $next_page = $total_pages; }
            
    #Displays 5 Page buttons at a time
    my @page_arr;
    my $ending_page = ($page + '2');
    
    if ($starting_page < '0') {$starting_page = '0'; }
    if ($ending_page < '4') {$ending_page = ('4' - $starting_page);}
    if ($ending_page > $total_pages){$ending_page = $total_pages;}
    for my $i($starting_page..$ending_page){ push @page_arr, $i ;}
    
    #Results Display
    print '<div id="device-browse-all-display">';
    
    print qq{
    <div class="row">
        <div class="col-sm-3">
            <nav>
            <ul class="pagination">
            <li> <a href="$ENV{'SCRIPT_NAME'}?page=$previous_page" ari
+a-label="Previous"> <span aria-hidden="true">&laquo;</span>    </a> <
+/li>};        
    
    foreach my $page_link(@page_arr) {
        if ($page_link == $page) { print qq{<li class="active"><a href
+="$ENV{SCRIPT_NAME}?page=$page_link">$page_link <span class="sr-only"
+>(current)</span></a></li>}; }
            else {    print qq{ <li><a href="$ENV{SCRIPT_NAME}?page=$p
+age_link">$page_link </a> </li>}; }
    }
    print qq{<li> <a href="$ENV{SCRIPT_NAME}?page=$next_page" aria-lab
+el="Next"> <span aria-hidden="true">&raquo;</span> </a> </li>};

    [download]
[reply]
[d/l]
      From the code how can i connect to DB and view form
[reply]

Back to Seekers of Perl Wisdom