php and mysql query

[tonynoriega]

what is the best method for using a search function used for searching records by last name....that can match any records with the input characters...

i.e.

if the user puts "mcgw"

it will find records for: mcgwire, mcgwen, mcgwise

my current query only returns exact matches.

$sql = 'SELECT * FROM registration_table WHERE lname=\'' . $lname . '\'';

[piznac]

I think this should work:

$sql = 'SELECT * FROM registration_table WHERE lname LIKE '%$lname%';

[eelixduppy]

This thread may also be of help to you: [webmasterworld.com...]

[piznac]

oops sorry left off a quote,.. I like doubles anyways:

$sql = "SELECT * FROM registration_table WHERE lname LIKE '%$Lname%'";

[Demaestro]

tony

check your database docs for mention of a "Levenshtein distance" function.

Almost all DBs have it in one form or another.

Basically it compares two strings and returns how many characters are different

So let's use McDonald an example.

Levenshtein('McDonald', 'MacDonald')
This would return 1

Levenshtein('McDonald', 'MicDonild')
This would return 2

Levenshtein('McDonald', 'Donald')
This would return 2

So you can write a query like this

select * from table where Levenshtein(lower(user_input), lower(table.last_name)) < 4

You can change increase the value so it finds more things,or lower it so it finds less. You can even pass it in as a variable so you can define how "fuzzy" the search is.

[edited by: Demaestro at 6:31 pm (utc) on Dec. 4, 2007]

[tonynoriega]

couldnt find levenstein in mysql 5.0 docs...

so the %lname% seems to be working fine...

'hugh' returns: mchugh, hughs

so that seems to be sufficient.
thanks all

[coopster]

Just for future reference ...

SOUNDEX [dev.mysql.com] you will find in the MySQL String Functions, but levenshtein [php.net] is in the PHP functions.