Jump to content


Photo

searching LIKE across numerous fields


  • Please log in to reply
6 replies to this topic

#1 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 13 March 2006 - 04:39 PM

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 13 March 2006 - 07:48 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 14 March 2006 - 12:24 AM

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

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


#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 March 2006 - 12:23 AM

Boolean mode is only supported as of MySQL 4.0.1, even though FULLTEXT indexing has been around since 3.23.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 15 March 2006 - 03:25 AM

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?

#6 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 March 2006 - 05:34 PM

No idea -- any takers?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#7 michaellunsford

michaellunsford
  • Members
  • PipPipPip
  • Advanced Member
  • 1,023 posts
  • LocationLouisiana, USA

Posted 30 March 2006 - 04:00 PM

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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users