mysql like 匹配排序,如何在使用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.

解决方案

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 treats boolean expressions as numbers, with true being 1. So, this counts the number of matches.

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.