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

[rahulephp]

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));

Than the $q would be:

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

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_te xt"

Many Thanks in Advance.

[Atli]

Hey.

Try using a Fulltext search. In Natrual Language mode you can have it return a score for each match and sort them based on that. The downside to this method is that you can only use them with MyISAM tables, and you will have to slightly alter your tables.

You might find this article at the Zend Developer Zone helpful: Using MySQL Full-text Searching.