問題描述

我有一個像這樣準備好的 mysqli 查詢:

I have a prepared mysqli query like this:

$query = $database->prepare("SELECT * FROM items WHERE inStock > ? AND size < ? AND name LIKE ?");            
$query->bind_param('iis', $inStock, $size, $name);              
$query->execute();  

WHERE 子句中有很多不同的條件可以過濾結果.問題是，這些參數是在搜索表單中提供的，它們不是強制性的.例如，某人可以僅使用名稱進行搜索，或者僅使用尺寸和名稱進行搜索，或者同時使用尺寸、名稱和庫存.

There are many various conditions in the WHERE clause which filter out the results. The problem is, that those parameters are supplied in a search form and they aren't mandatory. For example, someone can search by using only the name, or only by using the size and name, or by using the size, name and inStock, all at the same time.

我需要某種方式來調整查詢，以便我可以只提供我想要的參數.我能想到的唯一解決方案是制作一個巨大的 if..else 結構，其中存在包含所有搜索選項組合的準備好的查詢，但這是不可能的，因為有數千種組合.

I need some way to adjust the query so I can supply only the parameters I want. The only solution I can think of, is to make a huge if..else structure where prepared queries with all combinations of the search options exist, but that is out of the question as there are thousands of combinations.

我能想到的唯一實際可行的解決方案是使用未準備好的查詢，我將條件與諸如 $query .= "AND name LIKE '%".escapestuff($_POST['name'])."%'"

The only actual realistic solution I can think of, would be to use a not prepared query, where I glue the conditions together with from pieces like $query .= "AND name LIKE '%".escapestuff($_POST['name'])."%'"

但這非常難看，我非常希望繼續使用準備好的查詢系統.

But that is very ugly and I would very much like to stay with the prepared query system.

推薦答案

您可以建立一個條件列表并將綁定值和類型添加到列表中，這里是一個快速模型，它使用您的兩個字段參考...

You can build up a list of the criteria and add into a list the bind values and types, here is a quick mock up which uses two of the fields you refer to...

$data = [];
$params = "";
$where = [];
if ( !empty($name)) {
    $data[] = $name;
    $params.="s";
    $where[] = "name like ?";
}
if ( !empty($size)) {
    $data[] = $size;
    $params.="i";
    $where[] = "size < ?";
}
$sql = "SELECT * FROM items";
if ( count($where) > 0 ){
    $sql .= " where ". implode ( " and ", $where);
}
$query = $database->prepare($sql);
$query->bind_param($params, ...$data);
$query->execute();

請注意，bind_param() 使用 ... 來允許您傳遞數組而不是單個字段.

Notice that the bind_param() uses the ... to allow you to pass an array instead of the individual fields.

這篇關于如何忽略 PHP 中準備好的 mysqli 查詢中的參數?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！