使用LIKE时如何按大多数匹配对MySQL结果进行排序

I´m currently working on a query that must show a list of all articles from a specific table, but it must sort the list according to a search form, so that the articles that contain most/best matches are shown first and those that do not have any matches at all will be shown last sorted alphabetically.

我目前正在处理一个必须显示特定表中所有文章列表的查询,但它必须根据搜索表单对列表进行排序,以便首先显示包含最多/最佳匹配的文章,然后显示根本没有任何匹配将按字母顺序排序。

I have made this code which is working fine, though I cannot find a way to sort the matches by most hits / relevance.

我已经使这个代码工作正常,但我找不到按大多数匹配/相关性对匹配进行排序的方法。

Here is my code:

这是我的代码:

$search = $_POST["searhwords"];
$search = preg_replace('/\s+/', ' ',$search);

$SearchQueryArray = str_replace(",", "", $search);
$SearchQueryArray = str_replace(" ", ",", $SearchQueryArray);
$SearchQueryArray = explode(',', $SearchQueryArray);


$outputtt1 = '';
$outputtt2 = '';
foreach ( $SearchQueryArray as $queryword )
{
    $outputtt1 .= "title LIKE '%".$queryword."%' OR ";  
    $outputtt2 .= "title NOT LIKE '%".$queryword."%' AND ";  
}

$outputtt1 = rtrim($outputtt1, ' OR ');
$outputtt2 = rtrim($outputtt2, ' AND ');




$query_for_result = mysql_query("SELECT * from mytable 
WHERE ".$outputtt1."
union all
SELECT * from mytable 
WHERE ".$outputtt2."
");

So I need to find a way to sort the article that contain matches so that those that contain most matches are sorted first.

所以我需要找到一种方法来对包含匹配项的文章进行排序,以便首先对包含大多数匹配项的文章进行排序。

You can see the script i Have made live here: http://www.genius-webdesign.com/test/querytest.php

你可以看到我在这里发布的脚本:http://www.genius-webdesign.com/test/querytest.php

2 个解决方案

#1

Here is the SQL that does this:

这是执行此操作的SQL:

select t.*
from mytable
order by ((title like '%keyword1%') +
          (title like '%keyword2%') +
          (title like '%keyword3%') +
          . . .
          (title like '%keywordn%')
         ) desc;

MySQL treats boolean expressions as numbers, with true being 1. So, this counts the number of matches.

MySQL将布尔表达式视为数字,其中true为1.因此,这会计算匹配数。

By the way, if your data has any size, you might find full text search is more efficient than using like.

顺便说一句,如果您的数据有任何大小,您可能会发现全文搜索比使用类似更有效。

EDIT:

Counting the number of keywords is a bit more challenging, but you can do it as:

计算关键字的数量更具挑战性,但您可以这样做:

order by ((length(replace(title, 'keyword1', 'x')) -
           length(replace(title, 'keyword1', '')
          ) +
          (length(replace(title, 'keyword2', 'x')) -
           length(replace(title, 'keyword2', '')
          ) +
          . . .
          (length(replace(title, 'keywordn', 'x')) -
           length(replace(title, 'keywordn', '')
          )
         );

Counting the number of appearance of a keyword is more cumbersome than merely looking for where or not it is present.

计算关键字的出现次数比仅仅查找它存在的位置更麻烦。

#2

Another way to do it using full-text search

另一种使用全文搜索的方法

SELECT *, 
  MATCH('title') AGAINST($_GET['query']) * 10 as score1, 
  MATCH('content') AGAINST($_GET['query']) * 5 AS score2
FROM articles
WHERE MATCH (title, content) AGAINST($_GET['query'])
ORDER BY (score1) + (score2) DESC;

Alter your table like this if needed

如果需要,可以像这样改变你的桌子

ALTER TABLE articles ENGINE = MYISAM;
ALTER TABLE articles ADD FULLTEXT(title, content);

#1