home | O'Reilly's CD bookshelfs | FreeBSD | Linux | Cisco | Cisco Exam  


Perl CookbookPerl CookbookSearch this book

14.13. Building Queries Programmatically

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);


Library Navigation Links

Copyright © 2003 O'Reilly & Associates. All rights reserved.