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_text"

 

Many Thanks  in Advance.

 

[jcbones]

Couple of things.

 

$kt=split(" ",$search_text);//Breaking the string to array of words => ***This function has been DEPRECATED as of PHP 5.3.0. Relying on this feature is highly discouraged.***
//Instead use:
$kt=explode(" ",$search_text);//Breaking the string to array of words

 

If you only wanted rows relevant to the actual search parameters, you could include them all into 1 parameter separated by the wildcards.

 

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