Database paging?

[Michael Dark]

I've got a script that shows a form and uses the selections from drop-downs to filter a query to a database. All is good there - it works fine. I've also got it set up to pull a specified number of records at a time.

My problem is that I'd like to page out the results, but for the life of me can't figure out how! My script is set up to figure out what page of results it should be on using ?page=X in the URL, but if I call that, FormBuilder no longer remembers all the items selected on the previous page, making the database query fail (due to bad information in the SELECT statement). Any suggestions? If I use cgi_param to set the page, how can I call that in a link the user could click? (Basically, how do I implement a next page / previous page button)?

I feel like I'm missing something obvious but I've been working on this for a day and can't figure it out. Sorry for my horrible code - I'm learning.

Code:

#!/usr/bin/perl -w -T

 

 use strict;

 use warnings;

 use diagnostics;

 

 use lib '/Users/darkmich/perl5/lib/perl5';

 use CGI qw/:all/;

 use CGI::FormBuilder;

 use CGI::Carp qw(warningsToBrowser fatalsToBrowser);

 use DBI;

 use Data::Dumper;

 use HTML::Template;

 use CGI::Session;

 use POSIX qw(ceil);

  

#Set up SQL information - change for production database info

 my $platform = "mysql";

 my $database = "XXXX";

 my $host = "localhost";

 my $port = "3306";

 my $user = "XXXX";

 my $pw = "XXXX";

 #Set up query

 my $dsn = "dbi:$platform:$database:$host:$port";

 my $dbh = DBI->connect($dsn, $user, $pw);

 my $q = new CGI;

 my $session = new CGI::Session("driver:MySQL", $q, {Handle=>$dbh});

 my $cookie = $q->cookie(CGISESSID => $session->id);

 

 # Get URL Parameters

# Load information for drop-down selectors

my @species = @{$dbh->selectall_arrayref("SELECT SPECIES_ID,SPECIES_NAME FROM SPECIES ORDER BY SPECIES_NAME")};

my @system = @{$dbh->selectall_arrayref("SELECT SYSTEM_ID,SYSTEM_NAME FROM SYSTEM ORDER BY SYSTEM_NAME")};

my @organ = @{$dbh->selectall_arrayref("SELECT ORGAN_ID,ORGAN_NAME FROM ORGAN ORDER BY ORGAN_NAME")};

my @genpath = @{$dbh->selectall_arrayref("SELECT GENPATH_ID,GENPATH_NAME FROM GENPATH ORDER BY GENPATH_NAME")};

my @items_per_page = qw(10 25 50 75 100);

#Add "ALL" to the options list

unshift @species, [-1,"ALL SPECIES"];

unshift @system, [-1,"ALL SYSTEMS"];

unshift @organ, [-1,"ALL ORGANS"];

unshift @genpath, [-1,"ALL GEN PATH"];

print $q->header( -cookie=>$cookie );

    my $form = CGI::FormBuilder->new(

                    header   => 0,

                    method  => 'post',

                    template => 'show_image_info.tmpl',

                    submit => ['Search'],

                    keepextras => 1,

                    params => $q

               );

#Set up selectors

$form->field(name => 'no_items', options => \@items_per_page, type=>"select", multiple=>0, selectname=>0, onchange=>"this.form.submit()");

$form->field(name => 'testmode', options => ["Test mode"], onchange=>"this.form.submit()");

$form->field(name => 'thumbnail', options => ['Thumbnail viewer'],onchange=>"this.form.submit()");

$form->field(name => 'Species', options => \@species, type=>"select", multiple=>0, selectname=>0,onchange=>"this.form.submit()", style=>"width:10%");

$form->field(name => 'System', options => \@system, type=>"select", multiple=>0, selectname=>0,onchange=>"this.form.submit()", style=>"width:10%");

$form->field(name => 'Organ', options => \@organ, type=>"select", multiple=>0, selectname=>0,onchange=>"this.form.submit()", style=>"width:10%");

$form->field(name => 'Genpath', options => \@genpath, type=>"select", multiple=>0, selectname=>0,onchange=>"this.form.submit()", style=>"width:10%");

$form->field(name => 'Keywords', label=>'Keywords: ', style=>"width:20%");

my $query = "SELECT image_info.`IMAGE_NAME`, contrib.`CONTRIB_NAME`, inst.`INST_NAME`, species.`SPECIES_NAME`, system.`SYSTEM_NAME`, organ.`ORGAN_NAME`, genpath.`GENPATH_NAME`, image_info.`diagnosis` FROM image_info AS image_info, contrib AS contrib, inst AS inst, species AS species, system AS system, organ AS organ, genpath AS genpath WHERE `image_info`.`CONTRIB_ID` = `contrib`.`CONTRIB_ID` AND `image_info`.`INST_ID` = `inst`.`INST_ID` AND `image_info`.`SPECIES_ID` = `species`.`SPECIES_ID` AND `image_info`.`SYSTEM_ID` = `system`.`SYSTEM_ID` AND `image_info`.`ORGAN_ID` = organ.`ORGAN_ID` AND `image_info`.`GENPATH_ID` = `genpath`.`genpath_id`";

    if ($form->submitted && $form->validate)

    {

        # form was good, let's get started

$session->save_param($form);

        my $fields = $form->field;

# Get data from these with $fields->{*OPTION NAME*}

        #What page of results are we on? (1 is default)

        my $page = defined $form->cgi_param('page') && $form->cgi_param('page') ? $form->cgi_param('page') : 1;

        print $form->render;

        

       # Save thumbnail and test mode checkboxes for future use after clearing old values

        $session->clear(["testmode", "thumbnail"]);

        $session->save_param($q, ["testmode", "thumbnail"]);

        

# Set up SQL SELECT statement

if ($fields->{Species} != "-1")        

{

$query = $query . " AND species.species_id=" . $fields->{Species};

}

if ($fields->{System} != "-1")        

{

$query = $query . " AND system.system_id=" . $fields->{System};

}

if ($fields->{Organ} != "-1")        

{

$query = $query . " AND organ.organ_id=" . $fields->{Organ};

}

if ($fields->{Genpath} != "-1")        

{

$query = $query . " AND genpath.genpath_id=" . $fields->{Genpath};

}

my $kw;

my $kw_query=0;

if ($fields->{Keywords} != '')        #Using a keyword search!

{

$kw = $fields->{Keywords};

$query = $query . " (image_info.`IMAGE_NAME` LIKE '%?%' OR contrib.`CONTRIB_NAME` LIKE '%?%' OR inst.`INST_NAME` LIKE '%?%' OR species.`SPECIES_NAME` LIKE '%?%' OR system.`SYSTEM_NAME` LIKE '%?%' OR organ.`ORGAN_NAME` LIKE '%?%' OR genpath.`GENPATH_NAME` LIKE '%?%' OR image_info.`diagnosis` LIKE '%?%')";

$kw_query = 1;

}

# Run database query...

my $sth = $dbh->prepare($query) or die "Couldn't prepare statement: " . $dbh->errstr;

if ($kw_query == 1)

{

$sth->execute($kw);

}

else

{

$sth->execute();

}

print Dumper($sth);

# Save image name array for navigation in results pages...

my @image_array;

my $total_count = 0;

my $arrayref = $sth->fetchall_arrayref({});

foreach my $index (@$arrayref) 

{

push(@image_array, $index->{'IMAGE_NAME'});

                        # Get total number of results for page calculations...

                   

$total_count++; 

}

$session->param('Image_array', \@image_array);

           my $max_pages = $total_count / $fields->{no_items};

my $entries_per_page = $fields->{no_items};

        # Get the smaller subset of results

         my $page_query = $query . " LIMIT ?, ?";

my $sth = $dbh->prepare($page_query) or die "Couldn't prepare statement: " . $dbh->errstr;

if ($kw_query == 1)

{

$sth->execute($kw, ($page-1) * $entries_per_page, $entries_per_page);

}

else

{

$sth->execute(($page-1) * $entries_per_page, $entries_per_page);

}

my $arrayref = $sth->fetchall_arrayref({});

my $t;

$t = HTML::Template->new(filename => 'show_image_info_result.tmpl', loop_context_vars => 1);

$t->param(ROWS => \@$arrayref );

$dbh->disconnect;

print $t->output;

if ($page > 1) {

print "<A HREF=show_image_info_page.cgi?page=" . $page - 1 . ">Next Page</A>";

}

print "   Current Page: " . $page . "    ";

if ($max_pages > $page) 

{

print "<A HREF=show_image_info_page.cgi?page=";

print $page + 1;

print ">Next Page</A>";

}

print "<BR>" . $max_pages;

    }

    else {

        # print the form for them to fill out

        print $form->render;

        print "<h3>Select options above to begin search.</h3>\n";

        print Dumper($session);

[Wolfgang Radke]

Hello Michael,

take a look at
http://search.cpan.org/~leejo/CGI-4.35/lib/CGI.pod#Creating_a_self-referencing_url_that_preserves_state_information

It describes how to create a reference containing all data your form
included. If you just add ";db_page=17" to the end of that line you should
be able to switch to another page, here '17', in your DB.

Wolfgang

PS: I recommend grouping your statements into some subroutines rather than
setting up "spaghetti code". You can't start to early with that.