如何忽略 PHP 中准备好的 mysqli 查询中的参数?
我有一个像这样准备好的 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.
相关文章