homepage Welcome to WebmasterWorld Guest from 107.20.25.215
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Soundex Queries
soundex queries
Brandie




msg:4405596
 5:01 pm on Jan 10, 2012 (gmt 0)

I need to look-up records in a MySQL table using a name field that has been extracted from an SEO-friendly URL and where any apostophes in the name have been removed.

For example, my source field might be "archers place" for a record which actually stores the name as "Archer\'s Place".

I do seem to be getting the results I want with the following query...

SELECT * FROM mytable
WHERE SOUNDEX(table_Name) = SOUNDEX('archers place')

However, I would appreciate some guidance as to whether this is the corret/best way or whether there are other more efficient/accurate ways of treating this situation.

Thanks in anticipation of your advice.

 

Brandie




msg:4405617
 5:52 pm on Jan 10, 2012 (gmt 0)

Ooops, found that that doesn't work. I've now refined it down to the following:

SELECT field1, field2, REPLACE(field3,'\','') as NewName
FROM mytable HAVING NewName LIKE 'archers place'

This seems to work fine but my original question still applies - is this the correct/best way of doing it.

I have hit one further problem. Some names include an & in the table, but are excluded in the friendly-URLs. So, the database might contain "Town & Country Supplies" and my source data which I search on will be "town country supplies".

Have I bitten off more than I can chew with this one? I can see no way of my php script 'guessing' where apostrophes and ampersands might be needed, so it is very much a matter of the query having to manipulate the table. Having spent the afternoon on this, I think I need something stronger than coffee!

coopster




msg:4405680
 9:18 pm on Jan 10, 2012 (gmt 0)

You could always try the MySQL Full-Text Search Functions [dev.mysql.com].

And if that is too cumbersome you may consider your own site search server and indexing process. I prefer using an indexer and my own site search engine. Sphinx Search [sphinxsearch.com] is a whole new animal but well worth the effort once you get it nailed down. It indexes your data, in this case your MySQL data, and once the search terms are in the index you can use Sphinx to do the grunt work.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved