如何在PHP中构建带有多个参数的WHERE子句[重复]

Question

This question already has an answer here:

• php - How to parametrize SQL query with multiple WHERE conditions (prepared statement)? 1 answer

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.

</div>

Answer 1

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