Search not returning only one result

[jfarthing]

Hi everyone!

I am using the script below to search a db. If the is more than one match in the db, all goes well. But if there is only one match in the db, nothing gets displayed.

Any suggestions will be greatly appreciated.
Jim

#! /usr/bin/perl -w
 
use strict;
use DBI;
use CGI qw(:standard escape escapeHTML);
use diagnostics;
use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
 
my ($cat, $search, @search, $query, $tbl_ref, $page, $cells, $type);
my $type = param ("type");
my $column = "left";
my $db = "DBI:mysql:augusta_augusta";
my $dbPass = "augusta";
my $dbName = "augusta_augusta";
 
print "content-type:text/html\n\n";
 
my $cat1 = param("search1");
my $cat2 = param("search2");
 
if ($cat1) { $search = $cat1 };
if ($cat2) { $search = $cat2 };
 
if (!$search) {
print "<h3>You must enter a search term!</h1>\n";
print "<input type=\"button\" value=\"Back\" onClick=\"history.go(-1)\">\n";
print "</td></tr>\n";
exit;
}
 
my $dbh = DBI->connect("$db","$dbName","$dbPass"
            ,{PrintError => 1, RaiseError => 1});
my $sth = $dbh->prepare("SELECT * FROM Cats WHERE category = %$search%");
 
# Collect parameters that determine where we are in the display.
# Default to beginning of result set, 10 records/page if parameters
# are missing/malformed.
 
my $start = param ("start");
$start = 1
    if !defined ($start) || $start !~ /^\d+$/ || $start < 1;
 
my $per_page = param ("per_page");
$per_page = 10
    if !defined ($per_page) || $per_page !~ /^\d+$/ || $per_page < 1;;
 
# If start > 1, then we'll need a live "previous page" link.
# To determine whether or not there is a next page, try to select one
# more record than we need.  If we get that many, display only the first
# $per_page records, but add a live "next page" link.
 
# Select the records in the current page of the result set, and
# attempt to get an extra record.  (If we get the extra one, we
# won't display it, but its presence tells us there is a next
# page.)
 
if ($type eq 'alpha') {
$query = sprintf (
                "SELECT `Cats`.`Name`,`Address`.`Address`,`Address`.`City`,`Contact`.`Phone`
                FROM
                (
                `Cats`
                    LEFT JOIN
                        `Address`
                            USING (`Name`)
                    LEFT JOIN
                         `Contact`      
                            USING (`Name`) 
                )
                WHERE
                `Cats`.`Name` LIKE '$search%'
                ORDER BY name asc LIMIT %d,%d",
                $start - 1,         # number of records to skip
                $per_page + 1);     # number of records to select
} 
 
if ($cat1) { 
$query = sprintf (
               "SELECT `Cats`.`Name`,`Address`.`Address`,`Address`.`City`,`Contact`.`Phone`
                FROM
                (
                `Cats`
                    LEFT JOIN
                        `Address`
                            USING (`Name`)
                    LEFT JOIN
                         `Contact`      
                            USING (`Name`) 
                )
                WHERE
                `Cats`.`Category` LIKE '%$search%' 
                ORDER BY name asc LIMIT %d,%d",
                $start - 1,         # number of records to skip
                $per_page + 1);     
}    
 
if ($cat2) { 
$query = sprintf (
               "SELECT `Cats`.`Name`,`Address`.`Address`,`Address`.`City`,`Contact`.`Phone`,`Keys`.`Keywords`
                FROM
                (
                `Cats`
                    LEFT JOIN
                        `Address`
                            USING (`Name`)
                    LEFT JOIN
                         `Contact`      
                            USING (`Name`) 
                    LEFT JOIN
                         `Keys`      
                            USING (`Name`)         
                )
                WHERE
                `Keys`.`Keywords` LIKE '%%%$search%%' 
                ORDER BY name asc LIMIT %d,%d",
                $start - 1,         # number of records to skip
                $per_page + 1);     
}
 
my $tbl_ref = $dbh->selectall_arrayref ($query);
 
$dbh->disconnect ( );
 
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    my @cells2 = @{$tbl_ref->[$i+1]};  # get data values in row $i+1
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells;
    @cells2 = map {
                defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
                } @cells2;
    # add cells to table
    @cells="<b>$cells[0]</b><br>$cells[1]<br>$cells[2]<br>$cells[3]<br>$cells[4]<br>$cells[5]";
    @cells2="<b>$cells2[0]</b><br>$cells2[1]<br>$cells2[2]<br>$cells2[3]<br>$cells2[4]$cells2[5]";
    push (@rows, Tr (td ({width=>'275px',valign=>'top'},\@cells),(td ({width=>'275px',valign=>'top'},\@cells2))));
}
 
$page .= table ({-border => 0, width=> 550}, @rows) . br ( );
 
# If we're not at the beginning of the query result, present a live
# link to the previous page.  Otherwise present static text.
 
if ($start > 1)                 # live link
{
    my $url = sprintf ("%s?start=%d;per_page=%d;search1=$search;type=$type",
                        url ( ),
                        $start - $per_page,
                        $per_page);
    $page .= "[" . a ({-href => $url}, "previous page") . "]"; 
    $page .= '&nbsp; ' x 35; 
}
else                            # static text
{
    $page .= '&nbsp; ' x 35;
}
 
# If we got the extra record, present a live link to the next page.
# Otherwise present static text.
 
if (@{$tbl_ref} > $per_page)    # live link
{
    my $url = sprintf ("%s?start=%d;per_page=%d;search1=$search;type=$type",
                        url ( ),
                        $start + $per_page,
                        $per_page);
    $page .= "[" . a ({-href => $url}, "next page") . "]";
}
else                            # static text
{
    $page .= "";
}
 
$page .= "<p>[&nbsp; &nbsp; <a href=\"../pages/webdir2.html\">return to search page</a>&nbsp; &nbsp; ]</p>";
 
$page .= "Cat1:$cat1 :: Cat2:$cat2<br>Search: $search :: Type: $type";
$page .= end_html ( );
 
print $page;

[KevinADC]

try adding '=' on this line, after '<' and before @{$tbl_ref}:

for (my $i = 0; $i < $per_page && $i <= @{$tbl_ref}-1; $i+=2)

[eWish]

Do you get any errors when you run the script?

my $per_page = param ("per_page");
$per_page = 10

The second line will write over whatever value is coming in from param("per_page").

[numberwhun]

Do you get any errors when you run the script?

my $per_page = param ("per_page");
$per_page = 10

The second line will write over whatever value is coming in from param("per_page").

And, you are doing the same thing with $start:

my $start = param ("start");
$start = 1
 

[eWish]

How did I overlook $start?....need some sleep I guess.

[numberwhun]

How did I overlook $start?....need some sleep I guess.

Nothing like getting your variables and then over writing them, huh?

Jeff

[KevinADC]

Thats what you get for fooling around with pee aych pee! You lose all your perl marbles. ;)

[KevinADC]

Nothing like getting your variables and then over writing them, huh?

Jeff

Could be just for test purposes, but if not it needs to be changed.

[jfarthing]

Could be just for test purposes, but if not it needs to be changed.

Yeah, it is just for testing but forgot to change it when I posted here.....

Anyway, I tried adding the "=" but then it complained that @rows hadn't been explicitly declared, so declared it and then got "Can't use an undefined value as an ARRAY reference at search2.cgi line 153."

[KevinADC]

if you look at the code you are using two array indexs at a time:

for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2)
{
    #get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    my @cells2 = @{$tbl_ref->[$i+1]};  # get data values in row $i+1

so if the array only has one element (index 0) your code needs to be adjusted.

And you can see if there is only one element the loop initiation code will not work (ignoring $per_page):

$i = 0; $i < @{$tbl_ref}-1; $i+=2; 

if the array has one element it's length is 1 (one). Your code subtracts one from the length (-1) so now the expression looks like this:

$i = 0; $i < 0; $i+=2

but of course $i is not less than zero so the loop never initializes. That is why you need = in there:

$i = 0; $i <= @{$tbl_ref}-1; $i+=2; 

now the loop will initialize if there is only one element, but when you try and use:

    my @cells2 = @{$tbl_ref->[$i+1]};

you get the error about using the undefined value for an array reference because there is no [$i+1] index in the array, there is only index [0].

For your existing code to work there has to be two elements in the array. If you want it to work when there is only one, you have to make adjustments as indicated.

[jfarthing]

Hiya Kevin,

Ok, I made the changes that you suggested and made some headway.....

but, I am trying to get the output into two columns, and it has been a *very^ long night so please bear with me for a minute......

Anyway, I figured that the easiest way to get the output into two columns was a simple if ... else statement. So I came up with this little bit. For some reason, it always defaults to the first option, no matter how many entries there are.

So where am I going wrong???

Thanks for everything,
Jim

my $size = $#$tbl_ref + 1;
print "Size: $size";
 
if ($size eq '1') {
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
.....<snip>.....
if ($size gt '1') {
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
<snip>

[KevinADC]

Hiya Kevin,

Ok, I made the changes that you suggested and made some headway.....

but, I am trying to get the output into two columns, and it has been a *very^ long night so please bear with me for a minute......

Anyway, I figured that the easiest way to get the output into two columns was a simple if ... else statement. So I came up with this little bit. For some reason, it always defaults to the first option, no matter how many entries there are.

So where am I going wrong???

Thanks for everything,
Jim

my $size = $#$tbl_ref + 1;
print "Size: $size";
 
if ($size eq '1') {
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
.....<snip>.....
if ($size gt '1') {
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)
{
    # get data values in row $i
    my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
    # map values to HTML-encoded values, or to &nbsp; if null/empty
    @cells = map {
<snip>

You shouldbe using '==' and '>' to check numbers for equality, not 'eq' and 'gt'. Your first line is suspect:

my $size = $#$tbl_ref + 1;

$tbl_ref is a reference to an array so needs to be written like so to get the length:

my $size = $#{$tbl_ref} + 1;

[jfarthing]

You shouldbe using '==' and '>' to check numbers for equality, not 'eq' and 'gt'. Your first line is suspect:

my $size = $#$tbl_ref + 1;

$tbl_ref is a reference to an array so needs to be written like so to get the length:

my $size = $#{$tbl_ref} + 1;

Ok, how did I miss that???

Anyway, thanks for all of your help Kevin. It is greatly appreciated.
Jim

[KevinADC]

Ok, how did I miss that???

Anyway, thanks for all of your help Kevin. It is greatly appreciated.
Jim

I don't know how you missed it. You're welcome.