View Full Version : loose mysql searching

07-01-2006, 01:37 AM
i want to figure out how to search my database loosely so if someone misspells a part of a name they are searching for it will still find similar results... kinda like how when you misspell something on Google and it says "Did you mean.... ?".... how would i go about doing this?

07-01-2006, 04:42 AM
this could help

07-01-2006, 09:09 AM
mysql's SOUNDEX function is probably what you'll want

07-01-2006, 10:54 AM
im not familiar with SOUNDEX, could you give me an example query for the following instance

I'm searching table `profiles` for `fullname` LIKE 'Joshua Johnston'.... but there's only a Joshua Johnson in the database... how would i get it to show Joshua Johnson for the above search?

07-01-2006, 11:32 AM
SELECT * FROM profiles WHERE fullname SOUNDS LIKE 'Joshua Johnston';

You might have to do separate matches per word though, in which case you'd need to split the strings, or have separate firstname and surname fields...

07-01-2006, 01:46 PM
yea i guess i can do a search like this

$name = explode(" ",$_GET['q'],10);
$firstname = $name[0];
$lastname = $name[1];
$sql = mysql_query("SELECT * FROM `profiles` WHERE `firstname` SOUNDS LIKE '$firstname' AND `lastname` SOUNDS LIKE '$lastname'") or die(mysql_error());

07-01-2006, 03:03 PM
ok it all works perfectly... Thanks!

btw i also found this

// input misspelled word
$input = 'carrrot';

// array of words to check against
$words = array('apple','pineapple','banana','orange',

// no shortest distance found, yet
$shortest = -1;

// loop through words to find the closest
foreach ($words as $word) {

// calculate the distance between the input word,
// and the current word
$lev = levenshtein($input, $word);

// check for an exact match
if ($lev == 0) {

// closest word is this one (exact match)
$closest = $word;
$shortest = 0;

// break out of the loop; we've found an exact match

// if this distance is less than the next found shortest
// distance, OR if a next shortest word has not yet been found
if ($lev <= $shortest || $shortest < 0) {
// set the closest match, and shortest distance
$closest = $word;
$shortest = $lev;

echo "Input word: $input\n";
if ($shortest == 0) {
echo "Exact match found: $closest\n";
} else {
echo "Did you mean: $closest?\n";