php mysqli 准备语句 LIKE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18527659/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
php mysqli prepared statement LIKE
提问by user2493164
How can I with mysqli make a query with LIKE and get all results?
如何使用 mysqli 使用 LIKE 进行查询并获得所有结果？
This is my code but it dosn't work:
这是我的代码，但它不起作用：
$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$stmt->bind_result($id,$username);
$stmt->fetch();
This code it doesn't seem to work. I have searched it a lot. Also it may return more than 1 row. So how can I get all the results even if it returns more than 1 row?
这段代码似乎不起作用。我已经搜索了很多。它也可能返回超过 1 行。那么即使返回超过 1 行，我怎样才能获得所有结果？
回答by roninblade
Here's how you properly fetch the result
以下是正确获取结果的方法
$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$stmt->bind_result($id,$username);
while ($stmt->fetch()) {
echo "Id: {$id}, Username: {$username}";
}
or you can also do:
或者你也可以这样做：
$param = "%{$_POST['user']}%";
$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE ?");
$stmt->bind_param("s", $param);
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_array(MYSQLI_NUM)) {
foreach ($row as $r) {
print "$r ";
}
print "\n";
}
I hope you realise I got the answer directly from the manual hereand here, which is where you should've gone first.
回答by Martin
Updated
更新
From comments it is found that LIKE wildcard characters (_and %) are not escaped by default on Paramaterised queries and so can cause unexpected results.
从评论中发现 LIKE 通配符 (_和%) 默认情况下不会在参数化查询中进行转义，因此可能会导致意外结果。
Therefore when using "LIKE" statements, use this 'negative lookahead'Regex to ensure these characters are escaped :
因此，当使用“LIKE”语句时，请使用这个“负前瞻”正则表达式来确保这些字符被转义：
$param = preg_replace('/(?<!\\)([%_])/', '\$1',$param);
As an alternative to the given answer above you can also use the MySQL CONCATfunction thus:
作为上述给定答案的替代方案，您还可以使用 MySQL CONCAT函数，因此：
$stmt = $db->prepare("SELECT id,Username FROM users WHERE Username LIKE CONCAT('%',?,'%') ");
$stmt->bind_param("s", $param);
$stmt->execute();
Which means you do not need to edit your $paramvalue but does make for slightly longer queries.
这意味着您不需要编辑您的$param值，但确实需要稍长的查询。
