in reply to Re: Search 2 columns in thread Search 2 columns
Here is the complete script, the only thing I have removed is the comments....
Thanks for looking!
Jim
my $dbh = DBI->connect('DBI:mysql:farthing_valleyweb','farthing_farthi
+n','ginajim')
if ($type eq 'alpha') {
$query = sprintf (
"SELECT name, address, city, phone
FROM valley
where name like '$search%'
ORDER BY name LIMIT %d,%d",
$start - 1,
$per_page + 1);
} else {
$query = sprintf (
"SELECT name,address,city,phone
FROM valley
WHERE CONCAT_WS(' ',keywords,category) LIKE '%$searc
+h%'
ORDER BY name LIMIT %d,%d",
$start - 1, # number of records to skip
$per_page + 1); # number of records to select
}
my $tbl_ref = $dbh->selectall_arrayref ($query);
$dbh->disconnect ( );
my @rows;
my $z = @rows;
if ($z = 0) {
print "We are sorry, but your search did not return any results.<P />\
+n";
print "<input type=\"button\" value=\" Try again \" onClick=\"histor
+y.go(-1)\">\n";
print "$tbl_ref\n";
}
for (my $i = 0; $i < $per_page && $i < @{$tbl_ref}-1; $i+=2)
{
my @cells = @{$tbl_ref->[$i]}; # get data values in row $i
my @cells2 = @{$tbl_ref->[$i+1]}; # get data values in row $i+1
@cells = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : " "
} @cells;
@cells2 = map {
defined ($_) && $_ ne "" ? escapeHTML ($_) : " "
} @cells2;
@cells="<b>$cells[0]</b><br>$cells[1]<br>$cells[2]<br>$cells[3]<br
+>$cells[4]";
@cells2="<b>$cells2[0]</b><br>$cells2[1]<br>$cells2[2]<br>$cells2[
+3]<br>$cells2[4]";
push (@rows, Tr (td (\@cells),(td (\@cells2))));
}
$page .= table ({-border => 0, width=> 550}, @rows) . br ( );
if ($start > 1)
{
my $url = sprintf ("%s?start=%d;per_page=%d;search=$search",
url ( ),
$start - $per_page,
$per_page);
$page .= "[" . a ({-href => $url}, "previous page") . "] &n
+bsp; &
+nbsp;
+ ";
}
else
{
$page .= "
+
+  
+; ";
}
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 .= "Search: $search Cat1: $cat1 Cat2: $cat2\n";
$page .= end_html ( );
print $page;
Re^3: Search 2 columns
by blokhead (Monsignor) on Sep 22, 2007 at 14:53 UTC
|
| [reply] [d/l] [select] |
|
Yeah ... interpolation really makes a hash of things. ;^)
%$search
...roboticus
| [reply] [d/l] |
|
Well, I did try the following test before putting "%$search%" into the snippet suggestion in my own reply:
perl -Mstrict -le 'my $s="blah"; print "%$s%"'
which prints "%blah%". Putting a "%" in front of a scalar variable in a double-quoted string will not turn that variable into a hash ref.
If the variable happens to already be a hash ref, then of course it will be interpolated as such, though not in a way that most folks would consider useful:
perl -Mstrict -le 'my $s={foo=>"bar"}; print "%$s%"'
prints something like "%HASH(0x1801380)%". It's only arrays (and array refs) that get interpolated into the list of values when placed inside double-quotes -- and only if the sigils are right:
perl -Mstrict -le 'my %s=(foo => "bar"); print "%s"'
%s
perl -Mstrict -le 'my $s=[qw/foo bar/]; print "%$s%"'
%ARRAY(0x1801380)%
perl -Mstrict -le 'my $s=[qw/foo bar/]; print "@$s@"'
foo bar@
| [reply] [d/l] [select] |
|
Re^3: Search 2 columns
by graff (Chancellor) on Sep 22, 2007 at 15:11 UTC
|
If the following snippet is really what you are running now, this explains why you are not getting any results I'm pretty sure this does not do what you intend it to do -- in fact, it's kind of pointless:
my @rows;
my $z = @rows; # There is nothing in @rows yet, so $z is 0
if ($z = 0) { # but in any case, this will never return true!
print "We are sorry, but your search did not return any results.<P />\
+n";
print "<input type=\"button\" value=\" Try again \" onClick=\"histor
+y.go(-1)\">\n";
print "$tbl_ref\n";
}
Your "if" statement is not testing whether $z equals zero -- you would need to use "==" to test that. But you also need to do that "if" statement after you try to push things onto @rows, not before. (See below.)
Apart from that, I have a couple suggestions that I hope will make your life as a perl programmer more enjoyable... First, use placeholders in your queries -- they are so much better, in so many ways:
my $column = ( $type eq 'alpha' ) ? 'name' : "CONCAT_WS(' ',keywords,c
+ategory)";
my $query = "SELECT name, address, city, phone FROM valley ".
"WHERE $column LIKE ? ORDER BY name LIMIT ?,?";
my $sth = $dbh->prepare( $query );
$sth->execute( "%$search%", $start-1, $per_page+1 );
my $table_ref = $sth->fetchall_arrayref;
# here is where/how you check whether anything was returned:
if ( ref( $table_ref ) ne 'ARRAY' or @$table_ref == 0 ) {
...
}
(Updated to include the check for empty return from the DB.)
Next, given that your query results are sorted by name, I don't know why you seem to want your html table to be organized like this:
name1 name2
name3 name4
...
(Actually, I'm not sure what your intention is, but whatever...) Your loop for creating the table markup is probably broken. You start with @cells (and @cells2) containing 4 elements, and then do @cells="ugly string"; which reduces it to a single-element array, containing just the one "ugly string". (And you are referencing 5 elements in each row, where your query was only requesting 4.) There's a better way to code all that. Here's how I would arrange a simpler table:
for my $table_row ( @$table_ref ) {
my @table_cols = map { (defined($_) && /\S/) ? $_ : ' '} @$ta
+ble_row;
push @rows, Tr( \@table_cols );
}
$page .= "<b>" . table( {-border => 0, width=> 550}, \@rows) . "</b>"
+. br();
That has not been tested (I might not have it right), but if it's wrong, it should be easy to fix. And if you really want a more complicated table layout, it'll be easier to build from this simpler approach.
Finally, the "x" operator will make it easier for you to get all those " " thingies to come out right, and your code will be easier to read and maintain:
$page .= ' ' x 35;
| [reply] [d/l] [select] |
|
Holy K-Rap, Batman!
Atsa lotsa stuff!
Thanks blokhead and graff! You both brought up some very good points, it is greatly appreciated. I am going to put in placeholders where I can.
graff, that table code outputs 2 columns in the format
<b>Name</b> <b>Name</b>
Address Address
City City
Category Category
One thing though, graff. Can you explain this section to me?
I am not real sure that I am following the logic.
Thanks for everything guys! It is much appreciated and the points are well taken.
Jim
my $column = ( $type eq 'alpha' ) ? 'name' : "CONCAT_WS(' ',keywords,c
+ategory)";
my $query = "SELECT name, address, city, phone FROM valley ".
"WHERE $column LIKE ? ORDER BY name LIMIT ?,?";
| [reply] [d/l] [select] |
|
my $column = ( $type eq 'alpha' ) ? 'name' : "CONCAT_WS(' ',keywords,category)";
It's just a compact way to write if (..) {} else {}. Look for Ternary "?:" in perlop.
my $query = "SELECT name, address, city, phone FROM valley ". "WHERE $column LIKE ? ORDER BY name LIMIT ?,?";
The concept of Placeholders and Bind Values is fairly well described in the DBI documentation.
| [reply] [d/l] |
|
my @search = qw/%this% %that%/;
my $sql = "select something from table where column1 like ? or colu
+mn2 like ?";
my $sth = $dbh->prepare( $sql );
$sth->execute( @search );
It also works as expected if the second "like ?" applies to the same column as the first one.
As for the table layout, you might try something like this:
my $left_col = '';
for my $table_row ( @$table_ref ) {
my @table_cols = map { (defined($_) && /\S/) ? $_ : ' '} @$ta
+ble_row;
my $cell_str = join( '<br>', "<b>$table_cols[0]</b>",
@table_cols[1..$#table_cols] );
if ( $left_col eq '' ) {
$left_col = $cell_str;
} else {
push @rows, Tr([ $left_col, $cell_str ]);
$left_col = '';
}
}
$page .= table( {-border => 0, width=> 550}, \@rows);
(expression).
(Again, that's not tested, but it should be close to what you want. Updated to fix the sigils on table_cols in the "join()" expression.) | [reply] [d/l] [select] |
|
|