How to build a WHERE clause with multiple parameters in PHP

Question

I have a HTML form which contains 4 fields . The user can query on one field or multiple fields.

Based on what fields the user selects I need to build a WHERE condition.

I can check if each one of the parameters has a value or not. But I am stuck with trying to build a WHERE clause dynamically based on whether the parameters are populated or not.

SELECT field1, field2,field3 
FROM table1 
WHERE field1 = param1 AND field2 = param2 
AND field3 = param3 AND field4 = param4

This is what I am doing for checking for one parameter:

if(isset($_POST)['param1'])) {
$param1 = mysqli_real_escape_string($mysqli, $_POST['param1']);
$stmt   = mysqli->prepare("SELECT field1, field2,field3
                            FROM table1 WHERE field1 = ?");
$stmt->bind_param("s", $param1);
$stmt->execute();
$num_of_rows = $stmt->num_rows;

Based on the above code I need to check the parameters and then build a WHERE clause dynamically.

I hope the question is clear . If not please let me know. Also please note my code is taking care to prevent SQL injection. I basically need the logic to build the WHERE clause.

@Jerodev I have updated the question with the code I have for one of the parameters. Hope it is clear. — David D'Lima, Aug 12 '19 at 08:07
And where's the problem in attaching multiple other conditions? — Nico Haase, Aug 12 '19 at 08:07

score 1 · Answer 1 · answered Aug 12 '19 at 08:22

I would store the params in an associative array (field name => value), such as :

$params = array("field1" => "param1",
                "field2" => "param2",
                "field3" => "param3",
                "field4" => "param4");

Now, you can iterate through that array and build the parameterized string of your query :

$query = "SELECT field1, field2, field3 FROM table1";
$NumberOfParams = count($params);
// Do we have a where clause ?
if ($NumberOfParams > 0)
{
    $whereClause = " WHERE ";
    $i = 0;
    foreach ($params as $ParamName => $value)
    {
        $whereClause .= $ParamName . " = :" . $ParamName;
        // Are we not at last param ?
        if (++$i < $NumberOfParams)
        {
            $whereClause .= " AND ";
        }
    }
    $query .= $whereClause;
}

var_dump($query);

The var_dump outputs this :

string(126) "SELECT field1, field2, field3 FROM table1 WHERE field1 = :field1 AND field2 = :field2 AND field3 = :field3 AND field4 = :field4"

Now you can use pdo to execute that prepared statement, using that $params array

// using pdo
$stmt = $dbh->prepare($query);
$stmt->execute($params);

Thanks for replying. Just one question, the user could select param1 and param2 and leave the other parameters empty. In this case how would I check which parameters are blank? Basically the parameters which do not have a value should not be a part of the WHERE condition, — David D'Lima, Aug 12 '19 at 08:53
This is done when building the array `$params`, you can check if `isset($_POST["something"]) && $_POST["something"] != ''` — Cid, Aug 12 '19 at 09:03

score 0 · Answer 2 · answered Aug 12 '19 at 07:55

You can do it with simple boolean logic

WHERE (param1 is null or field1 = param1)
  AND (param2 is null or field2 = param2)
  AND (param3 is null or field3 = param3)
  AND (param4 is null or field4 = param4)

If your parameters are empty strings instead of null then use

WHERE (param1 = '' or field1 = param1)
...

score 0 · Answer 3 · answered Aug 12 '19 at 09:21

Try this :

$where = "";
if(isset($_POST['param1']) && $_POST['param1']!=''){
    $where .= " and param1='".mysqli_real_escape_string($_POST['param1'])."' ";
}
if(isset($_POST['param2']) && $_POST['param2']!=''){
    $where .= " and param2='".mysqli_real_escape_string($_POST['param2'])."' ";
}
if(isset($_POST['param3']) && $_POST['param3']!=''){
    $where .= " and param3='".mysqli_real_escape_string($_POST['param3'])."' ";
}
if(isset($_POST['param4']) && $_POST['param4']!=''){
    $where .= " and param4='".mysqli_real_escape_string($_POST['param4'])."' ";
}
$query = "SELECT field1, field2,field3  FROM table1 WHERE 1=1 $where ";
$stmt   = mysqli->prepare($query);
$stmt->execute();
$num_of_rows = $stmt->num_rows;

How to build a WHERE clause with multiple parameters in PHP

3 Answers3