PHP SQL SELECT，其中像带有多个单词的搜索项 [英] PHP SQL SELECT where like search item with multiple words

问题描述

我有一个选择，例如查询以下形式的搜索表格:

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

我的问题是，如果有人搜索苹果和梨"，那么我没有得到任何包含任何单词的结果，我只能使其返回包含所有单词的结果(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).");

这将为您提供一个查询，例如:

this will give you a query like:

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

如果要匹配任何搜索词而不是全部搜索词，请将AND更改为OR.进一步的改进可能涉及定义一些停用词(例如"and")，以获得更好的结果.

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.

这篇关于PHP SQL SELECT，其中像带有多个单词的搜索项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！