Jump to content

Archived

This topic is now archived and is closed to further replies.

michaellunsford

searching LIKE across numerous fields

Recommended Posts

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?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
Boolean mode is only supported as of MySQL 4.0.1, even though FULLTEXT indexing has been around since 3.23.

Share this post


Link to post
Share on other sites
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?

Share this post


Link to post
Share on other sites
No idea -- any takers?

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.