Mysql LIKE Search query - How to get most accurate row first.

On my search result page, I wanted to search for "Nintendo DS Lite - Pink"

I used following code:

Ex:

$search_text = "Nintendo DS Lite Pink";

$kt=split(" ",$search_text);//Breaking the string to array of words

// Now let us generate the sql 
while(list($key,$val)=each($kt))
{
	if($val<>" " and strlen($val) > 0)
	{
		$q .= " name like '%$val%' or ";
	}
}// end of while

//Remove the last 'OR'
$q=substr($q,0,(strlen($q)-3));

Code (markup):

Than the $q would be:

SELECT * FROM `products`
WHERE  
name like '%Nintendo%' or  
name like '%DS%' or  
name like '%Lite%' or  
name like '%Pink%'

Code (markup):

And i am getting Mysql Output given below:


1) Activity Meter - DS.
2) Nintendo DS Red.
3) Nintendo DS Lite Pink.
4) Nintendo DS Lite Turquoise.

But the third result is most accurate/relevant then first two result.

Please help me out to get the most accurate row first then the relevant rows as per their relevancy with search term "$search_text"

Many Thanks in Advance.

 

Hmm this isn't the best solution, but maybe you can do this. From those four results, compare each word of one result to the search term. Get the percentage (eg. "Activity Meter - DS" vs "Nintendo DS Lite - Pink", only one keyword match, so 1/3 is its percentage). Then just sort to the highest percentage.

 

I recently cobbled together a similar mysql search. I created an array of scores to gauge relevance. That is, the score of each database entry returned by the query is increased by the number of times it matched the words in the user's search string. I was doing a multi-field search, so I weighted the score depending on the field in which the match was found. I'd also suggest that you do a strtolower() on the user's search string and LOWER() in the mysql query to make the searches case-insensitive. You might also consider removing punctuation marks and <space> characters. And don't forget to pass the user's search string through mysql_real_escape_string() for security.

 

I was going to advise you to use the levenshtein algorithm, but based on experience it will give you complications (many times it will give same number to closely related searches). Use oliver's algorithm instead:

<?php

$searchTerm = 'Nintendo DS Lite Pink';
$results = array('Activity Meter - DS','Nintendo DS Red','Nintendo DS Lite Pink','Nintendo DS Lite Turquoise');

foreach($results as $sub => $name)
{
	$distance = similar_text($searchTerm,$name);

	$finalArray[$distance] = $name;
}

krsort($finalArray);
print_r($finalArray);
?>

PHP: