 |  |
| |
You want to create searches at runtime. For example, you want users of your program to be able to specify combinations of columns and allowable ranges of values.
Build a list of clauses and join them together to form the SQL WHERE clause:
if ($year_min) { push @clauses, "Year >= $year_min" }
if ($year_max) { push @clauses, "Year <= $year_max" }
if ($bedrooms_min) { push @clauses, "Beds >= $bedrooms_min" }
if ($bedrooms_max) { push @clauses, "Beds <= $bedrooms_max" }
# ...
$clause = join(" AND ", @clauses);
$sth = $dbh->prepare("SELECT beds,baths FROM Houses WHERE $clause");Don't try to build up a string in a loop:
$where = '';
foreach $possible (@names) {
$where .= ' OR Name=' . $dbh->quote($possible);
}That code will end up creating a WHERE clause like:
OR Name="Tom" OR Name="Nat" OR Name="Larry" OR Name="Tim"
Then you end up having to lop off the leading " OR ". It's much cleaner to use map and never have the extra text at the start:
$where = join(" OR ", map { "Name=".$dbh->quote($_) } @names);The map produces a list of strings like:
Name="Nat" Name="Tom" Name="Larry" Name="Tim"
and then they're joined together with " OR " to create a well-formed clause:
Name="Nat" OR Name="Tom" OR Name="Larry" OR Name="Tim"
Unfortunately, you cannot use placeholders here:
$sth = $dbh->prepare("SELECT id,login FROM People WHERE ?"); # BAD
$sth->bind_param(1, $where);As explained in Recipe 14.12, placeholders can only be used for simple scalar values and not entire clauses. However, there is an elegant solution: construct the clause and the values to be bound in parallel:
if ($year_min) { push @clauses, "Year >= ?"; push @bind, $year_min }
if ($year_max) { push @clauses, "Year <= ?"; push @bind, $year_max }
if ($bedrooms_min) { push @clauses, "Beds >= ?"; push @bind, $bedrooms_min }
if ($bedrooms_max) { push @clauses, "Beds <= ?"; push @bind, $bedrooms_max }
$clause = join(" AND ", @clauses);
$sth = $dbh->prepare("SELECT id,price FROM Houses WHERE $clause");
$sth->execute(@bind);The documentation with the DBI module from CPAN; http://dbi.perl.org; Programming the Perl DBI; Recipe 14.12
|  | |
| 14.12. Repeating Queries Efficiently |  | 14.14. Finding the Number of Rows Returned by a Query |
Copyright © 2003 O'Reilly & Associates. All rights reserved.