How to sort MySQL results by most matches when using LIKE

Question

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

Answer 1

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.