views:

110

answers:

6

I'll explain my problem:

I have a database table called country. It has two columns: ID and name.

When I want to search for 'paris', but misspelled the word: 'pares' ('e' instead of 'i'), I won't get any result from DB.

I want the the system to suggest similar words that could help in the search.

So, I am looking for help writing a script that makes suggestions from the DB that contain similar words like: paris, paredes, ... etc.

+2  A: 

Basically you need to check similarity against a valid array of names when you got no results from your db.

My idea:

  • User searching some name
  • No exact results
  • Fetch all names from db
  • Using levenshtein calculate the most exact tip for user to return
fabrik
+5  A: 

soundex will return a numerical code for a word that represents its sound. Words that sound similar will have the same soundex code. You could have a table with words and their soundex codes that you could use to look up similar sounding words. You could then sort them using their levenshtein distance.

If you're looking for something simpler and you just want to handle typos in your DB queries, you can do

select * from country where city SOUNDS LIKE 'Paris' instead of select * from country where city='Paris'

bemace
+9  A: 

In PHP you should use metaphone it is more accurate than soundex.

But your problem is getting the data from the database. You've not mentioned the DB. In MySQL you can make use of the SOUNDEX function. You just need to change your where clause in the query from

...where city = '$input_city'

to

... where soundex(city) = soundex('$input_city')

or even better you can use SOUNDS LIKE operator as

... where city sounds like '$input_city'
codaddict
+2  A: 

If you're using MySQL, you'll want to use a MATCH() AGAINST() statement, where MATCH() is given a comma-delimited list of FULLTEXT columns and AGAINST() is given your string to match against. The statement returns the relevance of your match (between 0 and 1) which you can use to determine whether or not to return rows.

More info on the MySQL site.

Edit: the sound suggestions are good ideas, however certain misspellings will completely change the pronunciation of a word and thus you may not be able to provide good suggestions if you use that method.

mway
+2  A: 

Since most of the PHP internal methods are already covered, you can also take a look at the Yahoo Boss Spelling Suggestion Service, its quite usefull -> http://developer.yahoo.com/search/boss/boss_guide/Spelling_Suggest.html

Hannes
+1  A: 

select * from tbl_name where city LIKE 'par%' u shud try this

Prateek