michaellunsford Posted March 13, 2006 Share Posted March 13, 2006 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? Quote Link to comment https://forums.phpfreaks.com/topic/4846-searching-like-across-numerous-fields/ Share on other sites More sharing options...
fenway Posted March 13, 2006 Share Posted March 13, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/4846-searching-like-across-numerous-fields/#findComment-17115 Share on other sites More sharing options...
michaellunsford Posted March 14, 2006 Author Share Posted March 14, 2006 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 BOOLEANMODE);#1064 - You have an error in your SQL syntax near 'BOOLEAN MODE)' at line 1[/code] Quote Link to comment https://forums.phpfreaks.com/topic/4846-searching-like-across-numerous-fields/#findComment-17200 Share on other sites More sharing options...
fenway Posted March 15, 2006 Share Posted March 15, 2006 Boolean mode is only supported as of MySQL 4.0.1, even though FULLTEXT indexing has been around since 3.23. Quote Link to comment https://forums.phpfreaks.com/topic/4846-searching-like-across-numerous-fields/#findComment-17621 Share on other sites More sharing options...
michaellunsford Posted March 15, 2006 Author Share Posted March 15, 2006 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? Quote Link to comment https://forums.phpfreaks.com/topic/4846-searching-like-across-numerous-fields/#findComment-17672 Share on other sites More sharing options...
fenway Posted March 15, 2006 Share Posted March 15, 2006 No idea -- any takers? Quote Link to comment https://forums.phpfreaks.com/topic/4846-searching-like-across-numerous-fields/#findComment-17869 Share on other sites More sharing options...
michaellunsford Posted March 30, 2006 Author Share Posted March 30, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/4846-searching-like-across-numerous-fields/#findComment-22316 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.