How to sort MySQL results by most matches when using LIKE
I am currently working on a query that should display a list of all articles from a specific table, but it should sort the list according to the search form so that the articles containing the most / best matches are shown first, and those that have no matches at all will be shown last sorted in alphabetical order.
I made this code which works fine, although I can't seem to 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 contains the matches, so that the ones that contain the most matches are sorted first.
You can see the script I Done here: http://www.genius-webdesign.com/test/querytest.php
source to share
Here's the SQL that does it:
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, and true as 1
. So this counts the number of hits.
By the way, if your data is of any size, you may find full text search more efficient than using like
.
EDIT:
Counting the number of keywords is a little tricky, but you can do it like:
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 times a keyword appears is more cumbersome than just finding where it is or not.
source to share
Another way to do it with 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;
Modify the table this way if necessary
ALTER TABLE articles ENGINE = MYISAM;
ALTER TABLE articles ADD FULLTEXT(title, content);
source to share