PHP SQL SELECT where like search item with multiple words
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3621310/
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 SQL SELECT where like search item with multiple words
提问by Dan
I have a select where like query for a seach form which is as follows:
我有一个 select where like 查询搜索表单，如下所示：
<?php
$bucketsearch = sanitizeone($_POST["bucketsearch"], "plain");
$bucketsearch = strip_word_html($bucketsearch);
?>
if(isset($_POST['search'])){
$result=MYSQL_QUERY( "SELECT * FROM buckets where bucketname like '%$bucketsearch%' order by bucketname");
}else{
$result=MYSQL_QUERY( "SELECT * FROM buckets order by bucketname");
}
My problem is that if someone searches for instance for "apple and pear" i do not get any results that contain any of the words, i can only make it return results (well 1 result) with all the words in it.
我的问题是，如果有人搜索例如“apple and pear”，我没有得到任何包含任何单词的结果，我只能让它返回包含所有单词的结果（1 个结果）。
Can anyone help me make this search a bit more versitle?? Thanks in advance.
谁能帮我让这个搜索更加多才多艺？？提前致谢。
回答by Tim Fountain
So you want an AND search using each of the words entered, rather than the exact string? Howabout something like this:
所以您想要使用输入的每个单词进行 AND 搜索，而不是使用确切的字符串？这样的事情怎么样：
$searchTerms = explode(' ', $bucketsearch);
$searchTermBits = array();
foreach ($searchTerms as $term) {
$term = trim($term);
if (!empty($term)) {
$searchTermBits[] = "bucketname LIKE '%$term%'";
}
}
...
$result = mysql_query("SELECT * FROM buckets WHERE ".implode(' AND ', $searchTermBits).");
this will give you a query like:
这会给你一个查询，如：
SELECT * FROM buckets WHERE bucketname LIKE '%apple%' AND bucketname LIKE '%and%' AND bucketname LIKE '%pear%'
change the AND to an OR if you want to match any of the search terms rather than all. Further improvements could involve defining some stop words like 'and' to give better results.
如果您想匹配任何搜索词而不是全部，请将 AND 更改为 OR。进一步的改进可能涉及定义一些停用词，例如“和”，以提供更好的结果。
回答by Ondrej Bozek
I think that the best solution would be to use Regular Expressions. It's cleanest and probably the most effective. Regular Expressions are supported in all commonly used DB engines.
我认为最好的解决方案是使用正则表达式。它是最干净的，也可能是最有效的。所有常用的数据库引擎都支持正则表达式。
In MySQL there is RLIKEoperator so your query would be something like:
在 MySQL 中有RLIKE运算符，因此您的查询将类似于：
SELECT * FROM buckets WHERE bucketname RLIKE "(?=.*apple)(?=.*and)(?=.*pear)"
Did't tested it, hope that my expression is right for MySQL regexp "dialect".
没有测试过，希望我的表达适合 MySQL regexp“方言”。
More on MySql regexp support:
http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp
更多关于 MySql 正则表达式支持：http:
//dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp
回答by Sukanya Sen
I wanted something with basic SQL for my multi word search in MySQL database. So I come up with the following.
我想要一些带有基本 SQL 的东西，用于在 MySQL 数据库中进行多词搜索。所以我想出了以下内容。
$terms=explode(" ",$search);
$count=count($terms);
$sql="SELECT * FROM product WHERE";
for($i=0;$i<$count;$i++)
{
if($i!=$count-1)
$sql = $sql.
" (pname LIKE '%$terms[$i]%' OR category LIKE '%$terms[$i]%' OR
sub_category LIKE '%$terms[$i]%' OR
description LIKE '%$terms[$i]%') AND ";
else
$sql = $sql.
"(pname LIKE '%$terms[$i]%' OR category LIKE '%$terms[$i]%' OR
sub_category LIKE '%$terms[$i]%' OR
description LIKE '%$terms[$i]%')";
}
回答by MikeTheReader
For your simple case here, you could replace your "and" with a "%" (but I'm guessing you're looking for a more comprehensive answer. (This would also be order specific, as apple would have to come before pear.)
对于这里的简单情况，您可以将“和”替换为“%”（但我猜您正在寻找更全面的答案。（这也是特定于订单的，因为苹果必须在梨之前出现） .)
回答by CogitoErgoSum
Er. Not the best solution I'd think but you can break up the words into an array and loop them out into multiple LIKES. Do some replaces to yank out ANDs, ORs etc and then run an explode.
呃。这不是我认为的最佳解决方案，但您可以将单词分解成一个数组并将它们循环到多个 LIKES 中。做一些替换来删除 AND、OR 等，然后运行一个爆炸。
Then just loop.
然后只是循环。
$sql = SELECT * from Buckets where";
$sql = SELECT * from Buckets where";
Loop the array, $sql .= " bucketname LIKE '%" . $arrayEl[i] . "% OR'. Just make sure on the last iteration to not include the last OR or append a last line of 0=1 etc.
循环数组 $sql .= "bucketname LIKE '%" 。$arrayEl[i] 。“% OR'。只需确保在最后一次迭代中不包含最后一个 OR 或附加最后一行 0=1 等。
Not elegant, not efficient but in this case it'll work. You'd honestly be better off running a full text search if its a text field.
不优雅，效率不高，但在这种情况下它会起作用。老实说，如果它是一个文本字段，您最好运行全文搜索。
回答by Rocket Hazmat
You can split "apple and pear" into 2 strings; "apple" and "pear". Maybe, then you can do WHERE bucketname LIKE '%apple%' OR bucketname LIKE '%pear%'. I don't know if this helps.
您可以将“apple and pear”拆分为 2 个字符串；“苹果”和“梨”。也许，那么你可以做到WHERE bucketname LIKE '%apple%' OR bucketname LIKE '%pear%'。我不知道这是否有帮助。
