使用PDO构造条件SQL语句

Question

what is the best way to construct an SQL statement with PDO when it depends on whether some PHP variable are set?

Here is an example;

$query="SELECT * FROM table ";

if($variable1 != "") {  $query = $query . "WHERE variable1 = :variable1";   }                   
if($variable2 != "") {  $query = $query . " AND variable2 = :variable2";    }                   

$query -> execute(array(':variable1' => $variable1, ':variable2' => $variable2));

I have a lot of these if statements and when binding the variables to the query I don't want to go through all these if statements again.

Is there an easier way to construct an SQL statement with such if/else conditions?

Answer 1

I would use an array to contain each part of the where... as a match occurs, add the resulting statement to the array. After all are evaluated, implode, separating with " AND " and concatenate onto $query.

$arrWhere = array();
$assWhere = array();

if($variable1 != "") {
    $arrWhere[] = "variable1 = :variable1";
    $assWhere[":variable1"] = $variable1;
}
if($variable2 != "") {
    $arrWhere[] = "variable2 = :variable2";
    $assWhere[":variable2"] = $variable2;
}
if($variable3 != "") {
    $arrWhere[] = "variable3 = :variable3";
    $assWhere[":variable3"] = $variable3;
}

$query="SELECT * FROM table WHERE " . implode ( " AND " , $arrWhere );

$query -> execute($assWhere);