I want to execute a parameterized query to perform a search by user-supplied parameters. There are quite a few parameters and not all of them are going to be supplied all the time. How can I make a standard query that specifies all possible parameters, but ignore some of these parameters if the user didn't choose a meaningful parameter value?
Here's an imaginary example to illustrate what I'm going for
$sql = 'SELECT * FROM people WHERE first_name = :first_name AND last_name = :last_name AND age = :age AND sex = :sex';
$query = $db->prepare($sql);
$query->execute(array(':first_name' => 'John', ':age' => '27');
Obviously, this will not work because the number of provided parameters does not match the number of expected parameters. Do I have to craft the query every time with only the specified parameters being included in the WHERE clause, or is there a way to get some of these parameters to be ignored or always return true when checked?
SELECT * FROM people
WHERE (first_name = :first_name or :first_name is null)
AND (last_name = :last_name or :last_name is null)
AND (age = :age or :age is null)
AND (sex = :sex or :sex is null)
When passing parameters, supply null
for the ones you don't need.
Note that to be able to run a query this way, emulation mode
for PDO have to be turned ON
First, start by changing your $sql
string to simply:
$sql = 'SELECT * FROM people WHERE 1 = 1';
The WHERE 1 = 1
will allow you to not include any additional parameters...
Next, selectively concatenate to your $sql
string any additional parameter that has a meaningful value:
$sql .= ' AND first_name = :first_name'
$sql .= ' AND age = :age'
Your $sql
string now only contains the parameters that you plan on providing, so you can proceed as before:
$query = $db->prepare($sql);
$query->execute(array(':first_name' => 'John', ':age' => '27');
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With