Pagination II

Discussion in 'Perl Misc' started by webmaster@valleywebnet.com, Aug 12, 2007.

  1. Guest

    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 qw:)standard);
    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;}
    }
    }
    , Aug 12, 2007
    #1
    1. Advertising

  2. J. Gleixner Guest

    wrote:
    > 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 ) );
    J. Gleixner, Aug 13, 2007
    #2
    1. Advertising

  3. JimJx Guest

    On Aug 13, 3:34 pm, "J. Gleixner" <>
    wrote:
    > wrote:
    > > 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?
    JimJx, Aug 14, 2007
    #3
  4. J. Gleixner Guest

    JimJx wrote:
    > On Aug 13, 3:34 pm, "J. Gleixner" <>
    > 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.
    J. Gleixner, Aug 14, 2007
    #4
  5. JimJx Guest

    On Aug 14, 11:10 am, "J. Gleixner" <glex_no-s...@qwest-spam-
    no.invalid> wrote:
    > JimJx wrote:
    > > On Aug 13, 3:34 pm, "J. Gleixner" <>
    > > 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.....
    JimJx, Aug 14, 2007
    #5
  6. J. Gleixner Guest

    JimJx wrote:
    > On Aug 14, 11:10 am, "J. Gleixner" <glex_no-s...@qwest-spam-
    > no.invalid> wrote:
    >> JimJx wrote:
    >>> On Aug 13, 3:34 pm, "J. Gleixner" <>
    >>> 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."
    J. Gleixner, Aug 14, 2007
    #6
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Joseph D. DeJohn

    DataGrid Pagination

    Joseph D. DeJohn, Jul 4, 2003, in forum: ASP .Net
    Replies:
    3
    Views:
    4,386
    Daniel Bass
    Jul 4, 2003
  2. Sreejumon[MVP]

    Re: datagrid custom pagination

    Sreejumon[MVP], Jul 8, 2003, in forum: ASP .Net
    Replies:
    0
    Views:
    424
    Sreejumon[MVP]
    Jul 8, 2003
  3. Child
    Replies:
    0
    Views:
    335
    Child
    Aug 19, 2003
  4. Kruq

    DataList pagination

    Kruq, Apr 29, 2004, in forum: ASP .Net
    Replies:
    0
    Views:
    621
  5. =?Utf-8?B?UkI=?=
    Replies:
    2
    Views:
    2,290
    =?Utf-8?B?UkI=?=
    Oct 14, 2004
Loading...

Share This Page