Re^2: Perl Database Paging

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]

Comment on Re^2: Perl Database Paging Download Code

Replies are listed 'Best First'.
Re^3: Perl Database Paging by graff (Chancellor) on Apr 03, 2014 at 01:44 UTC
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^4: Perl Database Paging by ironside (Acolyte) on Apr 03, 2014 at 22:43 UTC
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]
Re^4: Perl Database Paging by ironside (Acolyte) on Apr 03, 2014 at 04:23 UTC
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]