如何在使用LIKE时按大多数匹配排序MySQL结果 [英] How to sort MySQL results by most matches when using LIKE

问题描述

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

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.

以下是我的代码：

$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.

您可以看到我已在这里生活的脚本：
http://www.genius-webdesign.com/test/querytest.php

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

推荐答案

以下是执行此操作的SQL：

Here is the SQL that does this:

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

MySQL将布尔表达式视为数字，真值为 1 。

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

顺便说一下，如果你的数据有任何大小，你可能会发现全文搜索比使用<$ c $更有效率c> like 。

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

编辑：

计算关键字数但是你可以这样做：

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.

这篇关于如何在使用LIKE时按大多数匹配排序MySQL结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！