Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > perl > answers

Search not returning only one result

Newbie
  
Join Date: Sep 2007
Posts: 11
#1: Oct 16 '07
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

Expand|Select|Wrap|Line Numbers
  1. #! /usr/bin/perl -w
  2.  
  3. use strict;
  4. use DBI;
  5. use CGI qw(:standard escape escapeHTML);
  6. use diagnostics;
  7. use CGI::Carp qw/fatalsToBrowser warningsToBrowser/;
  8.  
  9. my ($cat, $search, @search, $query, $tbl_ref, $page, $cells, $type);
  10. my $type = param ("type");
  11. my $column = "left";
  12. my $db = "DBI:mysql:augusta_augusta";
  13. my $dbPass = "augusta";
  14. my $dbName = "augusta_augusta";
  15.  
  16. print "content-type:text/html\n\n";
  17.  
  18. my $cat1 = param("search1");
  19. my $cat2 = param("search2");
  20.  
  21. if ($cat1) { $search = $cat1 };
  22. if ($cat2) { $search = $cat2 };
  23.  
  24. if (!$search) {
  25. print "<h3>You must enter a search term!</h1>\n";
  26. print "<input type=\"button\" value=\"Back\" onClick=\"history.go(-1)\">\n";
  27. print "</td></tr>\n";
  28. exit;
  29. }
  30.  
  31. my $dbh = DBI->connect("$db","$dbName","$dbPass"
  32.             ,{PrintError => 1, RaiseError => 1});
  33. my $sth = $dbh->prepare("SELECT * FROM Cats WHERE category = %$search%");
  34.  
  35. # Collect parameters that determine where we are in the display.
  36. # Default to beginning of result set, 10 records/page if parameters
  37. # are missing/malformed.
  38.  
  39. my $start = param ("start");
  40. $start = 1
  41.     if !defined ($start) || $start !~ /^\d+$/ || $start < 1;
  42.  
  43. my $per_page = param ("per_page");
  44. $per_page = 10
  45.     if !defined ($per_page) || $per_page !~ /^\d+$/ || $per_page < 1;;
  46.  
  47. # If start > 1, then we'll need a live "previous page" link.
  48. # To determine whether or not there is a next page, try to select one
  49. # more record than we need.  If we get that many, display only the first
  50. # $per_page records, but add a live "next page" link.
  51.  
  52. # Select the records in the current page of the result set, and
  53. # attempt to get an extra record.  (If we get the extra one, we
  54. # won't display it, but its presence tells us there is a next
  55. # page.)
  56.  
  57. if ($type eq 'alpha') {
  58. $query = sprintf (
  59.                 "SELECT `Cats`.`Name`,`Address`.`Address`,`Address`.`City`,`Contact`.`Phone`
  60.                 FROM
  61.                 (
  62.                 `Cats`
  63.                     LEFT JOIN
  64.                         `Address`
  65.                             USING (`Name`)
  66.                     LEFT JOIN
  67.                          `Contact`      
  68.                             USING (`Name`) 
  69.                 )
  70.                 WHERE
  71.                 `Cats`.`Name` LIKE '$search%'
  72.                 ORDER BY name asc LIMIT %d,%d",
  73.                 $start - 1,         # number of records to skip
  74.                 $per_page + 1);     # number of records to select
  75. } 
  76.  
  77. if ($cat1) { 
  78. $query = sprintf (
  79.                "SELECT `Cats`.`Name`,`Address`.`Address`,`Address`.`City`,`Contact`.`Phone`
  80.                 FROM
  81.                 (
  82.                 `Cats`
  83.                     LEFT JOIN
  84.                         `Address`
  85.                             USING (`Name`)
  86.                     LEFT JOIN
  87.                          `Contact`      
  88.                             USING (`Name`) 
  89.                 )
  90.                 WHERE
  91.                 `Cats`.`Category` LIKE '%$search%' 
  92.                 ORDER BY name asc LIMIT %d,%d",
  93.                 $start - 1,         # number of records to skip
  94.                 $per_page + 1);     
  95. }    
  96.  
  97. if ($cat2) { 
  98. $query = sprintf (
  99.                "SELECT `Cats`.`Name`,`Address`.`Address`,`Address`.`City`,`Contact`.`Phone`,`Keys`.`Keywords`
  100.                 FROM
  101.                 (
  102.                 `Cats`
  103.                     LEFT JOIN
  104.                         `Address`
  105.                             USING (`Name`)
  106.                     LEFT JOIN
  107.                          `Contact`      
  108.                             USING (`Name`) 
  109.                     LEFT JOIN
  110.                          `Keys`      
  111.                             USING (`Name`)         
  112.                 )
  113.                 WHERE
  114.                 `Keys`.`Keywords` LIKE '%%%$search%%' 
  115.                 ORDER BY name asc LIMIT %d,%d",
  116.                 $start - 1,         # number of records to skip
  117.                 $per_page + 1);     
  118. }
  119.  
  120. my $tbl_ref = $dbh->selectall_arrayref ($query);
  121.  
  122. $dbh->disconnect ( );
  123.  
  124. for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2)
  125. {
  126.     # get data values in row $i
  127.     my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
  128.     my @cells2 = @{$tbl_ref->[$i+1]};  # get data values in row $i+1
  129.     # map values to HTML-encoded values, or to &nbsp; if null/empty
  130.     @cells = map {
  131.                 defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
  132.                 } @cells;
  133.     @cells2 = map {
  134.                 defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
  135.                 } @cells2;
  136.     # add cells to table
  137.     @cells="<b>$cells[0]</b><br>$cells[1]<br>$cells[2]<br>$cells[3]<br>$cells[4]<br>$cells[5]";
  138.     @cells2="<b>$cells2[0]</b><br>$cells2[1]<br>$cells2[2]<br>$cells2[3]<br>$cells2[4]$cells2[5]";
  139.     push (@rows, Tr (td ({width=>'275px',valign=>'top'},\@cells),(td ({width=>'275px',valign=>'top'},\@cells2))));
  140. }
  141.  
  142. $page .= table ({-border => 0, width=> 550}, @rows) . br ( );
  143.  
  144. # If we're not at the beginning of the query result, present a live
  145. # link to the previous page.  Otherwise present static text.
  146.  
  147. if ($start > 1)                 # live link
  148. {
  149.     my $url = sprintf ("%s?start=%d;per_page=%d;search1=$search;type=$type",
  150.                         url ( ),
  151.                         $start - $per_page,
  152.                         $per_page);
  153.     $page .= "[" . a ({-href => $url}, "previous page") . "]"; 
  154.     $page .= '&nbsp; ' x 35; 
  155. }
  156. else                            # static text
  157. {
  158.     $page .= '&nbsp; ' x 35;
  159. }
  160.  
  161. # If we got the extra record, present a live link to the next page.
  162. # Otherwise present static text.
  163.  
  164. if (@{$tbl_ref} > $per_page)    # live link
  165. {
  166.     my $url = sprintf ("%s?start=%d;per_page=%d;search1=$search;type=$type",
  167.                         url ( ),
  168.                         $start + $per_page,
  169.                         $per_page);
  170.     $page .= "[" . a ({-href => $url}, "next page") . "]";
  171. }
  172. else                            # static text
  173. {
  174.     $page .= "";
  175. }
  176.  
  177. $page .= "<p>[&nbsp; &nbsp; <a href=\"../pages/webdir2.html\">return to search page</a>&nbsp; &nbsp; ]</p>";
  178.  
  179. $page .= "Cat1:$cat1 :: Cat2:$cat2<br>Search: $search :: Type: $type";
  180. $page .= end_html ( );
  181.  
  182. print $page;

KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#2: Oct 16 '07

re: Search not returning only one result

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

for (my $i = 0; $i < $per_page && $i <= @{$tbl_ref}-1; $i+=2)
eWish's Avatar
Moderator
  
Join Date: Jul 2007
Location: Arkansas
Posts: 900
#3: Oct 16 '07

re: Search not returning only one result

Do you get any errors when you run the script?
Expand|Select|Wrap|Line Numbers
  1. my $per_page = param ("per_page");
  2. $per_page = 10
The second line will write over whatever value is coming in from param("per_page").
numberwhun's Avatar
Site Moderator
  
Join Date: May 2007
Location: New Hampshire
Posts: 2,571
#4: Oct 16 '07

re: Search not returning only one result

Quote:

Originally Posted by eWish

Do you get any errors when you run the script?

Expand|Select|Wrap|Line Numbers
  1. my $per_page = param ("per_page");
  2. $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:

Expand|Select|Wrap|Line Numbers
  1. my $start = param ("start");
  2. $start = 1
  3.  
eWish's Avatar
Moderator
  
Join Date: Jul 2007
Location: Arkansas
Posts: 900
#5: Oct 16 '07

re: Search not returning only one result

How did I overlook $start?....need some sleep I guess.
numberwhun's Avatar
Site Moderator
  
Join Date: May 2007
Location: New Hampshire
Posts: 2,571
#6: Oct 16 '07

re: Search not returning only one result

Quote:

Originally Posted by eWish

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

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

Jeff
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#7: Oct 16 '07

re: Search not returning only one result

Thats what you get for fooling around with pee aych pee! You lose all your perl marbles. ;)
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#8: Oct 16 '07

re: Search not returning only one result

Quote:

Originally Posted by numberwhun

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.
Newbie
  
Join Date: Sep 2007
Posts: 11
#9: Oct 16 '07

re: Search not returning only one result

Quote:

Originally Posted by KevinADC

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's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#10: Oct 16 '07

re: Search not returning only one result

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

Expand|Select|Wrap|Line Numbers
  1. for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2)
  2. {
  3.     #get data values in row $i
  4.     my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
  5.     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):

Expand|Select|Wrap|Line Numbers
  1. $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:

Expand|Select|Wrap|Line Numbers
  1. $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:

Expand|Select|Wrap|Line Numbers
  1. $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:

Expand|Select|Wrap|Line Numbers
  1.     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.
Newbie
  
Join Date: Sep 2007
Posts: 11
#11: Oct 16 '07

re: Search not returning only one result

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

Expand|Select|Wrap|Line Numbers
  1. my $size = $#$tbl_ref + 1;
  2. print "Size: $size";
  3.  
  4. if ($size eq '1') {
  5. for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)
  6. {
  7.     # get data values in row $i
  8.     my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
  9.     # map values to HTML-encoded values, or to &nbsp; if null/empty
  10.     @cells = map {
  11. .....<snip>.....
  12. if ($size gt '1') {
  13. for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)
  14. {
  15.     # get data values in row $i
  16.     my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
  17.     # map values to HTML-encoded values, or to &nbsp; if null/empty
  18.     @cells = map {
  19. <snip>
KevinADC's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#12: Oct 16 '07

re: Search not returning only one result

Quote:

Originally Posted by 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

Expand|Select|Wrap|Line Numbers
  1. my $size = $#$tbl_ref + 1;
  2. print "Size: $size";
  3.  
  4. if ($size eq '1') {
  5. for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)
  6. {
  7.     # get data values in row $i
  8.     my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
  9.     # map values to HTML-encoded values, or to &nbsp; if null/empty
  10.     @cells = map {
  11. .....<snip>.....
  12. if ($size gt '1') {
  13. for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i++)
  14. {
  15.     # get data values in row $i
  16.     my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
  17.     # map values to HTML-encoded values, or to &nbsp; if null/empty
  18.     @cells = map {
  19. <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;
Newbie
  
Join Date: Sep 2007
Posts: 11
#13: Oct 17 '07

re: Search not returning only one result

Quote:

Originally Posted by KevinADC

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's Avatar
Expert
  
Join Date: Jan 2007
Location: Southern California USA
Posts: 4,091
#14: Oct 17 '07

re: Search not returning only one result

Quote:

Originally Posted by jfarthing

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.
Reply

« previous question | next question »

Similar Perl bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,419 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved