PHP & MySQL: Similar entries

[Pytho]

Hi all

Microsoft has what I want. When you write to their forums and you type the title for your thread, a list will appear under the input box. It looks like the image attached.

Well, I have a MySQL database which contains a name field. There is something like "Mother's meatballs with brown sauce" and "Granny's sausages". How can I search the top 5 most similar results from the database, when user inputs his/her title? The text user entered will be transmitted with AJAX to the search page using GET method.

Is there any simple MySQL query that does this? I know there's LIKE, but I think it's not perfect for this...

Thanks.

[pritaeas]

I think your best bet is to add a tagging to your system. When an entry is added, the tags will be automatically added from the title. This way you can remove the most common words like 'and', 'with', etc. I've posted a way to do tagging somewhere in this PHP forum. Perhaps it will help.

[Pytho]

So basically I split the name and remove the unnecessary words like "and", "with" etc? Then I do a database search with those words, but how? If I have a tag field in my database table which contains the tags seperated by commas, how do I do the search? If I compare "Mother's meatballs with brown sauce" and "Mrs. Smith's meatballs", I just can't say WHERE X = Y in this case. I need LIKE. But how can I use it when I have a several words to compare?

Edit.

I just remembered that it's not so easy to do this with my native language. We don't have prepositions like in English, we bend words... But let's try.

[pritaeas]

You shouldn't use one column with comma separated tags. You should use a link table. Search this forum, I replied with a solution somewhere (recently).

[diafol]

Personally, for these types of searches, I'd use FULLTEXT - i.e. MATCH... AGAINST...

LIKE %...% will only match spot on hits of the string you search for and won't sort in relevance. Fulltext searches should offer the sorting by relevance.

Yep, this has been covered recently - don't know if it's the same one Pritaeas was referring to though:

http://www.daniweb.com/web-development/php/threads/400479/1715176#post1715176

But as Pritaeas notes in the thread, it can only be used on MyISAM tables - which should be your default type anyway.

[pritaeas]

Thanks, yes, that one and this one , finally found it.