Pagination II

[webmaster]

Thanks to everyone here, I am closer to getting this working but am
still having a problem....

The script runs and outputs a page, but in the db I have set up all of
the categories have enough entries to fill multiple pages. So, the
script runs and always goes to page 2 for some reason.

And if I click on one of the other links for a different page, then I
get nothing on that one.

Can anyone spot the problem?

Thanks in advance,
Jim


#!/usr/bin/perl -w

use DBI;
use CGI;
use CGI::Carp qw(fatalsToBrowser warningsToBrowser);
use CGI qwstandard);
use POSIX;
use HTML::Template;
use strict;
use diagnostics;

my $limit;

my $q = new CGI;
my $find = $q->param("search");

if (!$limit) {$limit = 5;}

my $dbname = "DBI:mysql:farthing_valleyweb:localhost";
my $dbusername = "farthing_farthin";
my $dbpassword = "ginajim";
my( $ID, $category, $name, $description, $contact, $phone, $fax,
$address, $city, $state, $zip, $email, $url, $keywords );

my $dbh = DBI->connect($dbname, $dbusername, $dbpassword)
or die ("Connection to database failed: $!\n");

my $sql = "select * from valley where category like ?";

my $sth = $dbh->prepare($sql) or die("Error in SQL1\n");
$sth->execute($find) or die "Error in SQL2 $!\n";

my $results = $sth->rows;

my $results_per_page = 5;
my $pagesrequired = ceil($results / $results_per_page);

my $sql = "select * from valley where category like ?
limit $limit, $results_per_page";

my $sth = $dbh->prepare($sql) or die("Error in SQL3\n");
$sth->execute($find) or die ("Error in SQL4\n");

$sth->bind_columns( \$ID, \$category, \$name, \$description, \
$contact, \$phone, \$fax, \$address, \$city, \$state, \$zip, \$email, \
$url, \$keywords );

while( $sth->fetch() ) {
print "<b>$name</b><br />
$phone<br />
$address<br />
$city, $state $zip<p />\n";
}


for (my $i = 0; $i <= $pagesrequired -1; $i++) {
if ($i == 0) {
if ($limit != 0) {
print "<a href=\"search.cgi?limit=0&find=$find\">";
print $i + 1;
print "</a>";
}
else {print $i + 1;}
}

if ($i > 0) {
if ($limit != ($i * $results_per_page)) {
print " | <a href=\"search.cgi?limit=";
print ($i * $results_per_page);
print "&find=$find\">\n";
print $i + 1, "</a>";
}
else {print " | ", $i + 1;}
}
}

 

[J. Gleixner]

Thanks to everyone here, I am closer to getting this working but am
still having a problem....

The script runs and outputs a page, but in the db I have set up all of
the categories have enough entries to fill multiple pages. So, the
script runs and always goes to page 2 for some reason.

And if I click on one of the other links for a different page, then I
get nothing on that one.

Can anyone spot the problem? [...]

if (!$limit) {$limit = 5;}

$limit = 5 unless defined $limit;

my $sql = "select * from valley where category like ?";

Do you really need all of the columns?

my $sth = $dbh->prepare($sql) or die("Error in SQL1\n");
$sth->execute($find) or die "Error in SQL2 $!\n";

Look at DBI's RaiseError.. Using that you
can eliminate all of your 'or die..' code.

my $results_per_page = 5;

my $sql = "select * from valley where category like ?
limit $limit, $results_per_page";

Start with your SQL, once it's correct, then print it
to make sure it's correct, then actually run it.

print $sql;

Then run it on your DB, to see what it products. It looks like
limit and results_per_page are both 5, which probably isn't
what you want.

$sth->bind_columns( \$ID, \$category, \$name, \$description, \
$contact, \$phone, \$fax, \$address, \$city, \$state, \$zip, \$email, \
$url, \$keywords );

A little more readable as:

$sth->bind_columns( \( $ID, $category, $name, ..., $keywords ) );

 

[JimJx]

Thanks to everyone here, I am closer to getting this working but am
still having a problem....

The script runs and outputs a page, but in the db I have set up all of
the categories have enough entries to fill multiple pages. So, the
script runs and always goes to page 2 for some reason.

And if I click on one of the other links for a different page, then I
get nothing on that one.

Can anyone spot the problem?
[...]

if (!$limit) {$limit = 5;}

$limit = 5 unless defined $limit;

my $sql = "select * from valley where category like ?";

Do you really need all of the columns?

my $sth = $dbh->prepare($sql) or die("Error in SQL1\n");
$sth->execute($find) or die "Error in SQL2 $!\n";

Look at DBI's RaiseError.. Using that you
can eliminate all of your 'or die..' code.

my $results_per_page = 5;

my $sql = "select * from valley where category like ?
limit $limit, $results_per_page";

Start with your SQL, once it's correct, then print it
to make sure it's correct, then actually run it.

print $sql;

Then run it on your DB, to see what it products. It looks like
limit and results_per_page are both 5, which probably isn't
what you want.

$sth->bind_columns( \$ID, \$category, \$name, \$description, \
$contact, \$phone, \$fax, \$address, \$city, \$state, \$zip, \$email, \
$url, \$keywords );

A little more readable as:

$sth->bind_columns( \( $ID, $category, $name, ..., $keywords ) );

I made the changes that you suggested, but when I print $sql, I get
'select * from valley where category like ? limit 5, 5'

Which I can tell is definitely not what I want.....

Any suggestions on where to proceed from here?

 

[J. Gleixner]

On Aug 13, 3:34 pm, "J. Gleixner" <[email protected]>
wrote: [...]
I made the changes that you suggested, but when I print $sql, I get
'select * from valley where category like ? limit 5, 5'

Which I can tell is definitely not what I want.....

Well, what do you want? Once you know that, then modify
your program accordingly. There is plenty of documentation
and online examples for MySQL.

 

[JimJx]

On Aug 13, 3:34 pm, "J. Gleixner" <[email protected]>
wrote: [...]
I made the changes that you suggested, but when I print $sql, I get
'select * from valley where category like ? limit 5, 5'

Which I can tell is definitely not what I want.....

Well, what do you want? Once you know that, then modify
your program accordingly. There is plenty of documentation
and online examples for MySQL.

That's the problem in a nutshell.....

I have been modifying and trying different things that I have found
online, but there doesn't seem to be a good pagination script for
PERL, just for PHP.

So I am stuck with still getting the second page of the results and
all of the other pages being blank.....

 

[J. Gleixner]

On Aug 13, 3:34 pm, "J. Gleixner" <[email protected]>
wrote: [...]
I made the changes that you suggested, but when I print $sql, I get
'select * from valley where category like ? limit 5, 5'
Which I can tell is definitely not what I want.....

Well, what do you want? Once you know that, then modify
your program accordingly. There is plenty of documentation
and online examples for MySQL.

That's the problem in a nutshell.....

I have been modifying and trying different things that I have found
online, but there doesn't seem to be a good pagination script for
PERL, just for PHP.

Really? It's not terribly difficult, but I'm curious what modules
you tried that didn't work? There seems to be a lot of them
available:

http://search.cpan.org/search?query=pagination&mode=all

So I am stuck with still getting the second page of the results and
all of the other pages being blank.....

Forget about trying to do pagination, for now. Read the
documentation for MySQL's LIMIT syntax, to see what
is wrong with your SQL. Until you understand the
SQL you're just wasting time "trying different things."