By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,660 Members | 1,560 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,660 IT Pros & Developers. It's quick & easy.

Search not returning only one result

P: 11
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. if ($cat1) { 
  77. $query = sprintf (
  78.                "SELECT `Cats`.`Name`,`Address`.`Address`,`Address`.`City`,`Contact`.`Phone`
  79.                 FROM
  80.                 (
  81.                 `Cats`
  82.                     LEFT JOIN
  83.                         `Address`
  84.                             USING (`Name`)
  85.                     LEFT JOIN
  86.                          `Contact`      
  87.                             USING (`Name`) 
  88.                 )
  89.                 WHERE
  90.                 `Cats`.`Category` LIKE '%$search%' 
  91.                 ORDER BY name asc LIMIT %d,%d",
  92.                 $start - 1,         # number of records to skip
  93.                 $per_page + 1);     
  94. }    
  95.  
  96. if ($cat2) { 
  97. $query = sprintf (
  98.                "SELECT `Cats`.`Name`,`Address`.`Address`,`Address`.`City`,`Contact`.`Phone`,`Keys`.`Keywords`
  99.                 FROM
  100.                 (
  101.                 `Cats`
  102.                     LEFT JOIN
  103.                         `Address`
  104.                             USING (`Name`)
  105.                     LEFT JOIN
  106.                          `Contact`      
  107.                             USING (`Name`) 
  108.                     LEFT JOIN
  109.                          `Keys`      
  110.                             USING (`Name`)         
  111.                 )
  112.                 WHERE
  113.                 `Keys`.`Keywords` LIKE '%%%$search%%' 
  114.                 ORDER BY name asc LIMIT %d,%d",
  115.                 $start - 1,         # number of records to skip
  116.                 $per_page + 1);     
  117. }
  118.  
  119. my $tbl_ref = $dbh->selectall_arrayref ($query);
  120.  
  121. $dbh->disconnect ( );
  122.  
  123. for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2)
  124. {
  125.     # get data values in row $i
  126.     my @cells = @{$tbl_ref->[$i]};  # get data values in row $i
  127.     my @cells2 = @{$tbl_ref->[$i+1]};  # get data values in row $i+1
  128.     # map values to HTML-encoded values, or to &nbsp; if null/empty
  129.     @cells = map {
  130.                 defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
  131.                 } @cells;
  132.     @cells2 = map {
  133.                 defined ($_) && $_ ne "" ? escapeHTML ($_) : "&nbsp;"
  134.                 } @cells2;
  135.     # add cells to table
  136.     @cells="<b>$cells[0]</b><br>$cells[1]<br>$cells[2]<br>$cells[3]<br>$cells[4]<br>$cells[5]";
  137.     @cells2="<b>$cells2[0]</b><br>$cells2[1]<br>$cells2[2]<br>$cells2[3]<br>$cells2[4]$cells2[5]";
  138.     push (@rows, Tr (td ({width=>'275px',valign=>'top'},\@cells),(td ({width=>'275px',valign=>'top'},\@cells2))));
  139. }
  140.  
  141. $page .= table ({-border => 0, width=> 550}, @rows) . br ( );
  142.  
  143. # If we're not at the beginning of the query result, present a live
  144. # link to the previous page.  Otherwise present static text.
  145.  
  146. if ($start > 1)                 # live link
  147. {
  148.     my $url = sprintf ("%s?start=%d;per_page=%d;search1=$search;type=$type",
  149.                         url ( ),
  150.                         $start - $per_page,
  151.                         $per_page);
  152.     $page .= "[" . a ({-href => $url}, "previous page") . "]"; 
  153.     $page .= '&nbsp; ' x 35; 
  154. }
  155. else                            # static text
  156. {
  157.     $page .= '&nbsp; ' x 35;
  158. }
  159.  
  160. # If we got the extra record, present a live link to the next page.
  161. # Otherwise present static text.
  162.  
  163. if (@{$tbl_ref} > $per_page)    # live link
  164. {
  165.     my $url = sprintf ("%s?start=%d;per_page=%d;search1=$search;type=$type",
  166.                         url ( ),
  167.                         $start + $per_page,
  168.                         $per_page);
  169.     $page .= "[" . a ({-href => $url}, "next page") . "]";
  170. }
  171. else                            # static text
  172. {
  173.     $page .= "";
  174. }
  175.  
  176. $page .= "<p>[&nbsp; &nbsp; <a href=\"../pages/webdir2.html\">return to search page</a>&nbsp; &nbsp; ]</p>";
  177.  
  178. $page .= "Cat1:$cat1 :: Cat2:$cat2<br>Search: $search :: Type: $type";
  179. $page .= end_html ( );
  180.  
  181. print $page;
Oct 16 '07 #1
Share this Question
Share on Google+
13 Replies


KevinADC
Expert 2.5K+
P: 4,059
try adding '=' on this line, after '<' and before @{$tbl_ref}:

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

eWish
Expert 100+
P: 971
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").
Oct 16 '07 #3

numberwhun
Expert Mod 2.5K+
P: 3,503
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.  
Oct 16 '07 #4

eWish
Expert 100+
P: 971
How did I overlook $start?....need some sleep I guess.
Oct 16 '07 #5

numberwhun
Expert Mod 2.5K+
P: 3,503
How did I overlook $start?....need some sleep I guess.
Nothing like getting your variables and then over writing them, huh?

Jeff
Oct 16 '07 #6

KevinADC
Expert 2.5K+
P: 4,059
Thats what you get for fooling around with pee aych pee! You lose all your perl marbles. ;)
Oct 16 '07 #7

KevinADC
Expert 2.5K+
P: 4,059
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.
Oct 16 '07 #8

P: 11
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."
Oct 16 '07 #9

KevinADC
Expert 2.5K+
P: 4,059
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.
Oct 16 '07 #10

P: 11
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>
Oct 16 '07 #11

KevinADC
Expert 2.5K+
P: 4,059
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;
Oct 16 '07 #12

P: 11
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
Oct 17 '07 #13

KevinADC
Expert 2.5K+
P: 4,059
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.
Oct 17 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.