construct conditional SQL statement with PDO

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?

CLaFarge · Accepted Answer · 2015-08-30 14:02:19Z

4

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

edited Aug 30, 2015 at 14:02

answered Aug 30, 2015 at 13:28

CLaFarge

1,38511 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

grueb Over a year ago

yes, that would probably be more elegant to construct the query – but how do I then know what I need to bind in the execute statement?

CLaFarge Over a year ago

Not 100% on this, but looks like execute take associative array... see code in edited Answer.

zerobandwidth Over a year ago

This will work most of the time, but the WHERE clause should be added conditionally, since it's possible that none of variableN contains a value.

CLaFarge Over a year ago

I completely agree with zero... actually $query never need be set if count($arrWhere)<1... didn't add to the answer as SO doesn't encourage completing code for inquirers beyond answering the question posted.

Collectives™ on Stack Overflow

construct conditional SQL statement with PDO

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related