searching LIKE across numerous fields

[michaellunsford]

Working with importing data from a tab delimited file. Recently, I'm wanting to add a new search feature that would search an address -- which is spread across numerous fields.

For example, 123 E. Main St. is separated into four separate fields. The number, direction (east), street name, and street type (like street, boulevard, avenue, etc).

So, if I have a single search field where they can type "123 Main" I'd like it to combine the four fields in a single search. Does that make sense?

The other option is to make a separate "combined" field in the database where I run the four address fields together on import.

Is there a mysql solution, or do I need to create the separate field?

[fenway]

The most ideal way to handle this is to create a FULLTEXT index which contains all of the columns that make up your semantic address field. There are plenty of examples in the refman -- once you have it set up, you can issue a "MATCH...AGAINST" query to find the desired rows. There are some subtleties to using this versus using "LIKE" (e.g. stopwords, word length, etc.), so read carefully!

Hope that helps.

[michaellunsford]

Hmm... Getting very close. Any idea why Boolean mode would be giving me an error?

[code] SELECT *
FROM  `listings`
WHERE MATCH (
STREET_NUM, STREET_NAME
)
AGAINST (
'+7653 +Cameron'
IN BOOLEAN
MODE
);

#1064 - You have an error in your SQL syntax near 'BOOLEAN MODE)' at line 1
[/code]

[fenway]

Boolean mode is only supported as of MySQL 4.0.1, even though FULLTEXT indexing has been around since 3.23.

[michaellunsford]

Ouch, I'm running 3.23.58 -- wow, that's ancient.

I'm running plesk over linux. I see somewhere I should use YUM, but that's the extent of the instructions. Any good info on how to accomplish this?

[fenway]

No idea -- any takers?

[michaellunsford]

okay -- I dropped a line on the plesk forum and got an answer rather quickly. It just took me this long to build up the courage to do it.

It was a single command on the command line ("yum upgrade mysql"), followed by a tweak of the /etc/my.cnf file.

After much nail biting, and hair standing up on the back of my neck, etc, it actually turned out to be very simple and completely pain free.

Anyway, the indexing works better than I could have imagined, thanks for the help.