如何在PHP中准备好的mysqli查询中忽略参数? [英] How to ignore a parameter in a prepared mysqli query in PHP?

问题描述

我有一个准备好的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子句中有许多条件可以过滤出结果. 问题在于，这些参数以搜索形式提供，并且不是必需的.例如，某人可以同时使用名称，或者仅使用大小和名称，或者使用大小，名称和inStock进行搜索.

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查询中忽略参数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！