لطفا منتظر باشید ...
 |  |
14.13. Building Queries Programmatically
14.13.1. Problem
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.
14.13.2. Solution
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");
14.13.3. Discussion
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);
14.13.4. See Also
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.
