Get best matches from mySQL using PHP

[asif49]

This is for a website search function, I do the following to get several similar results from the mysql database:

$var = "words words words";
    $str = explode(' ', $var); 
    foreach ($str as $i)             
    {
    $match[] = " e LIKE '%$i%' ";//to search each word within searched phrase separately
    }	
	
    $query = "SELECT * FROM db WHERE a LIKE '%$var%' OR b LIKE '$var%' OR c LIKE '%$var%' OR " . implode(' OR ', $match);

But using this method isn't good since all the data is in no order, I would like to order it by relevance. Please reccommend the simplest methods of doing this. Especially if there's one which doesn't require the redesigning of the whole database, something simple would be good (if a simple method exists)

If the database contained values such as:
"wordy" and "word and word" and "word word word word word"

I would want them in the order of:
"word word word word word" then "word and word" then "wordy"

Hope the above isn't difficult to understand

[asif49]

???

[thorin]

You would need quite an advanced mysql query to do what you want if it is possible in the first place.

From what I understand you need to count the occurrences of every keyword in a field and then order the results by that number. If it were in a column rather than a field it would have been easier yet still tough.

From what I know you will need something like a user defined function and a lot of knowledge of mysql to count words in fields. Eventually the query may still perform slowly. The text handling capabilities of MySQL aren't good enough for what you want.

Why don't you use good old PHP and preg_match for something like this?

[asif49]

Hmm, I didn't know it was going to be that difficult. I guess I should just try google some more...