問題描述

我想執行參數化查詢，以通過用戶提供的參數執行搜索.有相當多的參數，并不是所有的參數都會一直提供.如果用戶沒有選擇有意義的參數值，我如何進行指定所有可能參數的標準查詢，但忽略其中一些參數?

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 標準的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！