How to execute prepare statement using if statement in PHP PDO?

Question

I am fetching some data from MySQL database using PHP PDO prepared statement. I have to use if statement inside the execution of the prepared statement. See my codes below for better understanding

$query = "SELECT * FROM my_table WHERE 1=1";

if(isset($_GET['one'])){
    $query .= " AND one = :one";
}

if(isset($_GET['two'])){
    $query .= " AND two = :two";
}

if(isset($_GET['three'])){
    $query .= " AND three = :three";
}

$result = $db->prepare($query);
$result->execute([

    /* ------------------------------------
    How to declare the above parameters here
    as it will show error if any of the if statement is not true? 
    ----------------------------------------*/

]);

I want to know how to declare the prepared array parameter using if statement inside the $result->execute(......]) block?

Answer 1

You need to create an empty $params array, and inside each if block you can push the appropriate value to it. For example:

if(isset($_GET['one'])){
    $query .= " AND one = :one";
    $params[':one'] = $_GET['one'];
}

Then you can simply do

$result->execute($params);

Note that you can based on what you've written, you could simplify your code with an outer foreach on a list of parameter names e.g.

$names= array('one', 'two', 'three');
$params = array();
foreach ($names as $name) {
    if (isset($_GET[$name])) {
         $query .= " AND $name = :$name";
         $params[":$name"] = $_GET[$name];
    }
}
$result->execute($params);