忽略特定的WHERE标准 [英] Ignore particular WHERE criteria
问题描述
我想执行参数化查询,以通过用户提供的参数进行搜索.有很多参数,但并非所有参数都会一直提供.如何进行标准查询以指定所有可能的参数,但是如果用户没有选择有意义的参数值,则忽略其中的某些参数?
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');
显然,这将不起作用,因为提供的参数数量与期望的参数数量不匹配.我是否必须每次仅在WHERE子句中仅包含指定参数的情况下进行查询,还是有办法让其中一些参数被忽略,或者在选中时始终返回true?
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)
传递参数时,为不需要的参数提供null
.
When passing parameters, supply null
for the ones you don't need.
请注意,要能够以这种方式运行查询,必须将PDO的emulation mode
设置为ON
Note that to be able to run a query this way, emulation mode
for PDO have to be turned ON
这篇关于忽略特定的WHERE标准的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!