Accounting for misspelled words in search

[tonyawelch]

    * your MySQL server version -- 5.0.7.5

 

    * the raw MySQL statement in question [in a CODE block, and without any PHP variables]

$get_users_query="SELECT user_id, first_name, last_name, r_date FROM users WHERE first_name LIKE 'SOUNDEX(%$_POST[byname]%)' || last_name LIKE 'SOUNDEX(%$_POST[byname]%)' AND confirmed='Y' ORDER BY last_name, first_name";

 

    * any errors that MySQL returns to the client [from mysql_error()]

No errors - but no results are returned.

 

    * the table structure & column indexes of the relevant tables [via SHOW CREATE TABLE is preferred]

 

CREATE TABLE IF NOT EXISTS `users` (
  `user_id` int(10) unsigned NOT NULL auto_increment,
  `first_name` varchar(30) NOT NULL,
  `last_name` varchar(30) NOT NULL,
  `dob` varchar(10) NOT NULL,
  `email` varchar(40) NOT NULL,
  `password` varchar(41) NOT NULL,
  `tandc` char(1) NOT NULL,
  `r_date` datetime NOT NULL,
  `ip` varchar(40) NOT NULL,
  `activity` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `confirmed` varchar(10) NOT NULL,
  `access` tinyint(1) unsigned NOT NULL default '1',
  `ban_reason` text,
  PRIMARY KEY  (`user_id`)
)

 

 

    * a clear and concise description of what you want this statement to achieve

 

I'm creating a search function in my administrative backend for searching users.  I would like the search to account for misspelled names.  For example, if I have the last name "Welch" in the database, and put "Walch" in the search box, I'd like the query to return the "Welch" record as part of its return.

 

    * a description of what it's currently doing that's not to your liking

 

Right now, with the last name "Welch" in the database and a search for "Walch" made, I get no results.

 

    * a brief listing of the types of things you've attempted so far

 

I've read up on LIKE, FULLTEXT and SOUNDEX and the query I posted above is all I can think of that might accomplish what I'm trying to do.

[kickstart]

Hi

 

Soundex will return a string that is a standard representation of how it sounds and isn't likely to look much like the original string. As such I don't think you can look for the soundex representation of a string in the middle of another string.

 

For example a Soundex for "John Smith" is J5253 while for just "Smith" it is S530. As you can see you will not find S530 in J5253.

 

Secondly, even if you could search like this, are the first_name and last_name columns stored already in soundex format?

 

Think the best you will get is something like:-

 

$get_users_query="SELECT user_id, first_name, last_name, r_date 
FROM users 
WHERE (SOUNDEX(first_name) = SOUNDEX($_POST[byname]) OR SOUNDEX(last_name) LIKE SOUNDEX($_POST[byname]))
AND confirmed='Y' 
ORDER BY last_name, first_name";

 

All the best

 

Keith