CODEWITHSUNDEEP
phpmysqlsqlpdo
grueb
grueb

Reputation: 133

construct conditional SQL statement with PDO

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?

Upvotes: 2

Views: 330

Answers (1)

CLaFarge
CLaFarge

Reputation: 1375

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);

Upvotes: 4

Related Questions