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

Hi all, This will probably go down as the silliest question of the day, but it is very early and I have been up all night....

Anyway, I am trying to use HTML::Pager after a search in MySQL. So, my question is how do I get the search results into Pager?

Thanks for reading!
Jim

#!/usr/bin/perl -w

use CGI::Carp qw/fatalsToBrowser warningsToBrowser/; 

$|++;
use strict;
use warnings;
use diagnostics;
use DBI;
use CGI;
use HTML::Pager;

my $row;


# get CGI query object
  my $query = CGI->new();
  
  
my $dbname    = "DBI:mysql:farthing_valleyweb";
my $dbusername    = "farthing_farthin";
my $dbpassword    = "ginajim";

my $dbh = DBI->connect($dbname, $dbusername, $dbpassword,
          { RaiseError => 1, PrintError => 0 });

# The sql for selecting out of the DB
my $sql = 'select * from valley where category like "Real Estate"'; 

# Prepare the SQL and then execute it
my $db_query = $dbh->prepare($sql);
$db_query->execute(); 

# store each record into @results
while (my @results = $db_query->fetchrow_array()) {
        push(@results, $row);

} 


  # create a callback subroutine to generate the data to be paged
  my $get_data_sub = sub {
     my ($offset, $rows) = @_;
     my @return_array;

     for (my $x = 0; $x < $rows; $x++) {
        push(@return_array, [ $row ]);
     }
     return \@return_array;
}
;

my $pager = HTML::Pager->new(
                               query => $query,
                               get_data_callback => $get_data_sub,
                               rows => 100,
                               page_size => 3,
);

  # make it go - send the results to the browser.
  print $pager->output;

[download]

Replies are listed 'Best First'.
Re: HTML Pager
by moritz (Cardinal) on Aug 22, 2007 at 11:43 UTC
    You should include the $offset and $rows variables as limits in the SQL query, which means you have to execute the query inside the callback, and retrieve the data from the DB inside the callback as well.

    And then you just return \@results - that's it.

    Perl 6 in German -- Difficult Sudoku

[reply]
[d/l]
      I am not sure that I understand what you mean by "inside the callback"

      Could you elaborate or give an example?

[reply]
        In your code you write a subroutine that HTML::Pager calls to get the data, and this is called a "callback function".

        In your example it is this: 

          
# create a callback subroutine to generate the data to be paged
my $get_data_sub = sub {
     my ($offset, $rows) = @_;
     my @return_array;

     for (my $x = 0; $x < $rows; $x++) {
        push(@return_array, [ $row ]);
     }
     return \@return_array;
}

        [download]

        Now this is just the stub function frrm the example in the documentation. You don't want that to return a few numbers here, but real data from your database.

        So you have to change your callback function: 

        my $get_data_sub = sub {
     my ($offset, $rows) = @_;
     my @return_array;
    
     # Prepare SQL query:
     my $sth = $dbi->prepare("Your SQL here that fetches the data from
+ $offset to $offset+$rows");
     $sth->execute($offset, $rows);
     
     while (my $array_ref = $sth->fetchrow_arrayref){
         push @return_array, $array_ref;
     }
     return \@return_array;
}

        [download]

        The only thing that's left for you now is to figure out your SQL (a small hint: use placeholders as explained in the DBI documentation).

        Perl 6 in German -- Difficult Sudoku

[reply]
[d/l]
[select]
        Can you name the callback? Its  get_data_callback => $get_data_sub
[reply]
[d/l]

Back to Seekers of Perl Wisdom