14.13. Building Queries Programmatically14.13.1. ProblemYou 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. SolutionBuild 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. DiscussionDon'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 AlsoThe documentation with the DBI module from CPAN; http://dbi.perl.org; Programming the Perl DBI; Recipe 14.12 Copyright © 2003 O'Reilly & Associates. All rights reserved. |
|