Mysql LIKE Search query - How to get most accurate row firs

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
rahulephp
Forum Commoner
Posts: 28
Joined: Mon Oct 05, 2009 11:05 am

Mysql LIKE Search query - How to get most accurate row firs

Post by rahulephp »

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

I used following code:

Ex:

Code: Select all

$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:

Code: Select all

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.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Mysql LIKE Search query - How to get most accurate row

Post by Eran »

There is no relevancy for a LIKE search. You can order by relevancy using a fulltext index - http://dev.mysql.com/doc/refman/5.0/en/ ... earch.html
Post Reply