help finding search syntax to return expected results

[JoeyB]

Hello,

 

I'm new to MySQL and having trouble setting up a search. Right now I've inherited a little perl script that does a flat file search using AND if multiple terms are entered. I'm trying to replace it with a MySQL DB search so I can avoid having to keep my flat file ordered and so I can search for characters with accents or no accents transparently (flat file has the accented characters encoded as HTML entities which makes things worse)

 

Let's say I have the following three entries in a field called title in MySQL:

 

Fountain of the Four Rivers

Four Seasons

Trevi Fountain

 

Using the old flat file search where each of those titles would be on a separate line, suppose I type "fountain four"  (without the quotation marks, I can also type "four fountain" and there's no difference) I only get the first line returned.

 

I can't seem to figure out the search syntax for MySQL to return the same results.

 

I tried Full text searching but discovered that "four" is a stop word (seems I've got a few stop words which people might search for so fulltext seems like it won't be a good solution.) As such, I'm stuck using a LIKE search and searching across 6 or 7 columns and 2200 lines.

 

Seems like I've tried every combination of fountain%, %fountain%, fountain% and %four%, fountain% and four%, fountain% or four%, etc, etc, with WHERE title LIKE ... but I can't seem to get what I want. I can't pull out only that single result (I tried REGEXP but that didn't seem to get my much further unless I used it wrong)

 

In short, I want to perform an AND search if there are multiple terms (and be able to change it to OR easily if I want, this is all hardcoded, user can't choose) and find results based on finding the first part of a word. I seem to be finding results based on all the data in the title field for a given record, not based on the words in the title field for that record. Is there a solution or am I back to my flat file search?

 

Thanks

[beebum]

...WHERE title LIKE '%fountain%' AND title LIKE '%four%'...

[JoeyB]

Did a quick little search with just your little piece of code on that one field and that worked so I went back to my really long select statement and found a problem with (). Fixed that and things seem better now.

 

Tried a few more searches and ran into an issue using the new syntax.

 

If I search for something like France using '%france%', I end up getting results which I don't want (I see things like francesco which is really a wrong result in this context.) Is there a way around this? If not, this might be a show stopper and I'll have to go back to the flat file search.

 

Thanks

 

[beebum]

You need to use "Full-Text"

 

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

[JoeyB]

Words like one, two, four, seven, etc are part of the stopwords for MySQL as I learned, which kinda stinks in this case. Fountain four is just like fountain so instead of 2 results, I'll get 20.

 

I may have to live with it. I'll try more searches to see what other issues turn up.