Home » questions » PHP Search help and suggestions?

PHP Search help and suggestions?

1
2006-08-01 14:38:22, Category: Programming & Design
I am building a search in PHP. A state is selected (hopefully) from a drop down list and a city is entered. I have it working with exact matches and if the city is short letters (on either or both ends). I am wanting it to find matches if there are extra letters. For example if someone types in Hollywood, CA in the city box or Hollywoodd. Here's my WHERE clause... "name LIKE '%$searched_city%' AND '$id' = parent" The id is the ID of the state selected and parent is in the table with the city that tells what state the city belongs to. The problem is in the LIKE statement. Also, any suggestions on other errors to check for.. besides the obvious. And does anyone know a way to catch errors in spelling besides having a table of alternate spellings. Aaaaand... any suggestions on how to check for FT (Fort), MT (Mount), ST (Saint), etc stuff... I am grateful for any ideas, help, and suggestions. Thanks.

Answers

  1. tedjn

    On 2006-08-01 14:59:05


    It is really annoying that this comparison needs to be done in mysql because PHP has nice functions like levenshtein() for this, but to use them, you'd have to grab all the results and use them from PHP which is terribly inefficient. Luckily, it seems Mysql supports the SOUNDEX() function. It says:: Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels. mysql> SELECT SOUNDEX('Hello');         -> 'H400' mysql> SELECT SOUNDEX('Quadratically');         -> 'Q36324' In which you can see how close two searches sound. You may also be able to find an add-on function such as levenshtein() for Mysql if you search a bit on Google. If you want a more indepth discussion, feel free to message me.