Building Queries Programmatically (Perl Cookbook, 2nd Edition)

## 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

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