Spell Check / Words similar to word - MYSQL

russellharrower · 08-09-2009, 07:34 AM

Hi all,
I am having a few issues, when a user does a search, my database sql is not searching for words that might be misspelt for e,g, In my DB someone has spelt development this way developement is there away that when a user searches for development it show both development and developement.

I need to make this to be the case for all words and if they misspell a word in search it needs to offer them the correction.

Thanks

JaoudeStudios · 08-09-2009, 10:32 AM

Yes there is, it is known as soundex.

MySQL has a built in function to do it http://dev.mysql.com/doc/refman/5.0/...nction_soundex

russellharrower · 08-09-2009, 11:26 AM

If you don't mind can you show me an example, of how you would use this in a MySQL

Code:

$sql = "SELECT * FROM sh WHERE sitename LIKE '%".$quary."%' OR keywords LIKE '%".$quary."%' OR title LIKE '%".$quary."%' OR body LIKE '%".$quary."%' OR writtenby LIKE '%".$quary."%' ORDER BY datetime DESC LIMIT $start, $limit";

JaoudeStudios · 08-10-2009, 06:53 AM

Just like in the example on the MySQL link I posted above...

$sql = "SELECT * FROM sh WHERE SOUNDEX(sitename) = '". SOUNDEX( $quary)."'";

NB: You should protect your query from mysql injection

russellharrower · 08-10-2009, 12:27 PM

Thank you, after adding a bit more code, it worked great.
Just wondering if you would be able to let me know, is there away to show the correct spelling to the user?

just like Google and Microsoft does?

JaoudeStudios · 08-10-2009, 12:43 PM

Yep.

But you would need a database of all the english words. I think there is one on the net floating around which is only a few megs (ish).