PHP SQL SELECT where like search item with multiple words

2021-12-19 00:00:00 sql select php mysql

我有一个查询表单的 select where like 查询，如下所示:

I have a select where like query for a seach form which is as follows:

<?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");
          }

我的问题是，如果有人搜索例如apple and pear"，我没有得到任何包含任何单词的结果，我只能让它返回包含所有单词的结果(1 个结果).

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.

谁能帮我让这个搜索更通用一些??提前致谢.

Can anyone help me make this search a bit more versitle?? Thanks in advance.

推荐答案

所以您想要使用输入的每个词进行 AND 搜索，而不是使用确切的字符串?这样的事情怎么样:

So you want an AND search using each of the words entered, rather than the exact string? Howabout something like this:

$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).");

这会给你一个查询:

SELECT * FROM buckets WHERE bucketname LIKE '%apple%' AND bucketname LIKE '%and%' AND bucketname LIKE '%pear%'

如果您想匹配任何搜索词而不是全部，请将 AND 更改为 OR.进一步的改进可能涉及定义一些停用词，例如和"，以提供更好的结果.

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.

相关文章