Soundex Queries

[Brandie]

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]

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]

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.