动态搜索PHP MYSQL PDO

Question

I have a form with 3 select boxes: age,room,type.

<form action="results.php" method="get">
<div class="form-group">
<select name="age">
     <option value>Any</option>
     <option value="1">15</option>
     <option value="2">25</option>
     <option value="3">30</option>
     <option value="4">40</option>
   </select>
</div>
<div class="form-group">
<select name="room">
     <option value>Any</option>
     <option value="1">1</option>
     <option value="2">2</option>
   </select>
</div>
<div class="form-group">
<select name="type">
     <option value>Any</option>
     <option value="1">Personal</option>
     <option value="2">Business</option>
   </select>
</div>
</form> 

What i am trying to do with PDO is to make a small search. If all variables are empty then my condition is:

$search = $db->query("SELECT * FROM table");

If 1 of them (as example the age) is not empty then i have:

 if(!empty($_GET['age'])){
$age = $_GET['age'];
$search = $db->query("SELECT * FROM table WHERE age = '$age'");
}

Now, if 2 of them are npt empty i have:

if(!empty($_GET['age']) && !empty($GET['room'])){
    $age = $_GET['age'];
$room = $_GET['room'];
    $search = $db->query("SELECT * FROM table WHERE age = '$age' AND room = '$room'");
    }

In order to avoid all possible search combinations, how can i make a search with the term if is not empty. I had made one in the past:

if(!empty($age)){
$where = "WHERE age = '$age'";
}

if(!empty($room)){
$where .= "and room = '$room'";
}

$query = "SELECT * FROM table $where";

How can i make it happen with PDO?? :/

Answer 1

I'd do something like this:

$param = array();
$query = 'SELECT ... FROM t WHERE 1=1';

if(!empty($_GET['age'])){
  $param['age'] = $_GET['age'];
  $query .= ' AND t.age = :age';
}

if(!empty($_GET['room'])){
  $param['room'] = $_GET['room'];
  $query .= ' AND t.room = :room';
}

if(!empty($_GET['type'])){
  $param['type'] = $_GET['type'];
  $query .= ' AND t.type = :type';
}

$dbh->prepare($query)->execute($param);

You might want to separate out the prepare and the execute. Check the return from the prepare before you try calling execute. Or, configure PDO can throw an exception when an error occurs, e.g.

 $dbh->setAttribute(PDO::ERRMODE_EXCEPTION);