Mysql "like"获取最相关的多个搜索词结果

Question

我想要一个能够返回最接近搜索字段关键字的查询。我在 4 列中搜索。标题，描述，作者，take_place。这是当前代码，它返回所有列的结果。我想要更具体的搜索结果。

$v = explode(' ', strtolower($searchwords));
if($c != ''){ $and = 'AND cat_id = '.$c; }
foreach($v as $val){
  $query parts[] = "'%".mysql_real_escape_string($val)."%'";
}
$title = implode(' OR title LIKE ', $query_parts);
$desc = implode(' OR desc LIKE ', $query_parts);
$author = implode(' OR author LIKE ', $query_parts);
$take_place = implode(' OR take_place LIKE ', $query_parts);

$sql_res=mysql_query("select * from nt_seminars where ((title like {$title}) or
 (description like {$description}) or (author like {$author}) or 
(take_place like {$take_place})) $and ORDER BY date;");

此查询返回包含任何单词的每个结果，我想检索最相关的组合搜索关键字。就像:

如果结果为 author 和 take_place，则只检索它们。不知何故，将 OR 替换为 AND，但它根本不会返回任何结果。此查询由 ajax 触发。

得到了更好的东西，但仍然不是我想要的..

$q = $_POST['searchword'];
  $arraySearch = explode(" ", $_POST['searchword']);
  // table fields to search
  $arrayFields = array(0 => "title", 1 => "city", 2=> "author");
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT * FROM ".$table." WHERE (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
   {
    while ($b < $countSearch)
    {
      $query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query.")";
  $query_result = mysql_query($query);

  if(mysql_num_rows($query_result) < 1)
  {
   echo '<span class="no-borders">No results found</span>';
  }
  else
  {
   echo '<ul class="list-group no-radius m-b-none m-t-n-xxs list-group-lg no-border">';
    // output list of articles
    while($row = mysql_fetch_assoc($query_result))
    {
//output loop
    }
  }

这将返回仅在其中一列中找到的关键字。我需要一个消除类型的查询..

Answer 1

找到了!为了搜索多个列，使用多个关键字使用我使用的 ajax keyup 函数

    $table = "nt_seminars";
// explode search words into an array
$q = $_POST['searchword'];
if($_GET[cat_id] != ''){ $and = 'AND cat_id='.$_GET[cat_id]; }
      $arraySearch = explode(" ", $_POST['searchword']);
  // table fields to search
  $arrayFields = array(0 => "title", 1 => "city", 2=> "author");
  $countSearch = count($arraySearch);
  $a = 0;
  $b = 0;
  $query = "SELECT *, CONCAT_WS(' ',title,author,city) FROM ".$table." WHERE (";
  $countFields = count($arrayFields);
  while ($a < $countFields)
  {
    while ($b < $countSearch)
    {
      $query = $query."CONCAT_WS(' ',title,author,city) LIKE '%$arraySearch[$b]%'";
      $b++;
      if ($b < $countSearch)
      {
        $query = $query." AND ";
      }
    }
    $b = 0;
    $a++;
    if ($a < $countFields)
    {
      $query = $query.") OR (";
    }
  }
  $query = $query." $and AND visible = 1 ORDER BY date desc)";
  $query_result = mysql_query($query) or die ($e = mysql_error());

  if(mysql_num_rows($query_result) < 1)
  {
   echo '<span class="no-borders">No results found</span>';
  }
  else
  {
    while($row = mysql_fetch_assoc($query_result))
    {
      // output whatever you want here for each search result
    }
  }

基本上，它是关于合并所有列，用空格分隔它们，以便按字母而不是单词(FULLTEXT)检索结果。我用它来实时搜索输出列表希望对您有所帮助!