Re^4: Search 2 columns

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
[download]

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 ?,?";
[download]

Comment on Re^4: Search 2 columns Select or Download Code

Replies are listed 'Best First'.
Re^5: Search 2 columns by andreas1234567 (Vicar) on Sep 23, 2007 at 06:48 UTC
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. -- Andreas	[reply] [d/l]
Re^5: Search 2 columns by graff (Chancellor) on Sep 23, 2007 at 19:38 UTC
The thing about building the query string was that you had two different ways to do it, and once you start using placeholders, the only difference between the two is the column spec that follows "where ". So I used the ternary operator ( `(condition) ? expr1 : expr2`) to assign a value to $column, and just put that into the common SQL statement. By the way, when I do something like this with a MySQL database of my own, I get the sort of results that I think you want: `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 );` [download] 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);` [download] (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]