PHP / MySQL relevance in searching

[gregerly]

Hello,

I've got a problem with a website search function. PHP for the front end, MySQL for the backend. The search function searches a field of keywords. The problem I'm having is that when someone searches for "Italian Food" it's bring back results for other resteraunts that are not italian, but do have the word Food in their keywords. Is there a way of adding relevance to a search, in a way that would make sense for the above senario?

Thanks in advance,

Greg

[pbmods]

Heya, Greg.

If your database uses MyISAM tables, you can use MySQL fulltext searching.

[dafodil]

fulltext is good because it offers different format of searches and if you're using the like keyword in your search remove it so that you can direct search....

[gregerly]

This looks like what I've been looking for. One thing I still don't understand about FULLTEXT searches, lets say I have four records:

1. Italian Food
2. Mexican Food
3. German Food
4. Chinese Food

Lets say my search is "Italian Food", where the word "Food" is in all results, but "Italian" is in only one. Does mysql filter out the three records that do not have "Italian" in the result?

I'm thinking this is definelty the way to go, I just am trying to wrap my head around exactly how it works.

Thanks,

Greg

[Botondus]

A natural language search interprets the search string as a phrase in natural human language (a phrase in free text). There are no special operators. The stopword list applies. In addition, words that are present in more than 50% of the rows are considered common and do not match. Full-text searches are natural language searches if the IN NATURAL LANGUAGE MODE modifier is given or if no modifier is given.

So if you use the IN NATURAL LANGUAGE MODE option it should give you only 1 result: Italian Food, from that particular list.

Anyway you can use something like:

SELECT ... MATCH () AGAINST() as score from .... WHERE MATCH () AGAINST () order by score desc;

And use only the first x rows, that contain the x most relevant results, if you don't want to show results with low relevance.

[dafodil]

This looks like what I've been looking for. One thing I still don't understand about FULLTEXT searches, lets say I have four records:

1. Italian Food
2. Mexican Food
3. German Food
4. Chinese Food

Lets say my search is "Italian Food", where the word "Food" is in all results, but "Italian" is in only one. Does mysql filter out the three records that do not have "Italian" in the result?

I'm thinking this is definelty the way to go, I just am trying to wrap my head around exactly how it works.

Thanks,

Greg

Yes mysql filters out the other foods... So what have you decided did you use the full text or the direct search? in fulltext you use the keyword match() and against()...

[gregerly]

Yes mysql filters out the other foods... So what have you decided did you use the full text or the direct search? in fulltext you use the keyword match() and against()...

I think i'm going with the full text search. Seems to be what I'm looking for. I'll post back here when I'm done and let yall know how it works.

Thanks again for the help. You guys/gals are a wealth of info.

Greg

[pbmods]

Heya, Greg.

Thanks again for the help. You guys/gals are a wealth of info.

We aim to please.

Good luck with your project, and if you ever need anything, post back anytime :)