使用PDO准备和绑定语句的条件查询

Question

I am converting all my queries from mysql to PDO, and in this process I found a conditional query like a follows

if (isset($parameters['searchTerm'])) {
    $where =" And title LIKE '%{$parameters['searchTerm'] }%'";
} 

$sql = "Select * from table data Where tableId = 5 {$where} ";

and when I am trying to convert this query in PDO the expected syntax is as follows

if (isset($parameters['searchTerm'])) {
    $where =" And title LIKE :searchTerm";
} 



$sql = $dbh->prepare("Select * from table data Where tableId = 5 {$where}");



if (isset($parameters['searchTerm'])) {
    $sql ->bindParam(':searchTerm', '%{$parameters['searchTerm'] }%');
} 

$sql ->execute();

Now as you can See that the if condition if (isset ($parameters ['searchTerm'] )) {...} is repeated twice.

The reason is

1. I can not prepare the sql query before $where is being set thus $sql variable is initialized after first if statement
2. I can not bind the parameters until I prepare the sql so it has to be placed after the $sql is being prepared

So there is one if statement before $sql = $dbh->prepare("Select * from table data Where tableId = 5 {$where}"); and one if statement after.

And my question is: Is there a way to remove this redundant if statement or I have to do it this way only.

Answer 1

you can use handy PDO's feature that lets you to send array with parameters straight into execute()

$where  = '';
$params = array();
if (isset($parameters['searchTerm'])) {
    $where =" And title LIKE :searchTerm";
    $params['searchTerm'] = "%$parameters[searchTerm]%";
}
$sql = "Select * from table data Where tableId = 5 $where";
$pdo->prepare($sql)->execute($params);

Note that PHP syntax in your code is also wrong.