忽略特定的 WHERE 标准

时间:2023-05-06
本文介绍了忽略特定的 WHERE 标准的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

限时送ChatGPT账号..

我想执行参数化查询,以通过用户提供的参数执行搜索.有相当多的参数,并不是所有的参数都会一直提供.如果用户没有选择有意义的参数值,我如何进行指定所有可能参数的标准查询,但忽略其中一些参数?

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 标准的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

上一篇:PHP、ORM、MSSQL 和 Unicode,是否可以让它们一起工作? 下一篇:检查用户名是否存在 PDO

相关文章