Recipe 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