finding similar matches

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
titaniumdoughnut
Forum Commoner
Posts: 33
Joined: Wed Jul 13, 2005 2:02 pm
Location: Manhattan

finding similar matches

Post by titaniumdoughnut »

Is there a way to query a MySQL database (through PHP) and ask it to use some kind of "similar matching" algorithms or something? I'm basically inventing terms for a technology I imagine might exist right now. Something that would search on a first and last name, and then present similar name combinations or variants of the names searched?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Possibly using the SOUNDEX() function.. but really it's often better to have a separate table with similarity information.. even then, it's complicated. I've seen this referred to as fuzzy searching, or similar names, stemming from fuzzy logic.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

also look at the levenshtein() function as well as similar_text() and metaphone().

I've used the levenshtein() distance of two soundex() strings before, but it's expensive resource-wise.

MySQL supports soundex as well, which is nice, but I seem to remember there was some issue about the length of the resulting string or something...

metaphone() is supposedly better than soundex() but MySQL doesn't support it to my knowledge.

SOUNDEX in SQL is faster than php, IIRC. You'll likely want something like:

Code: Select all

SELECT * FROM table WHERE SUBSTRING(SOUNDEX(`haystack`),1,4) = SUBSTRING(SOUNDEX({$needle}),1,4)
ok... I just went and looked it up. I did the above, then further ranked them by comparing the levenshtein() distances of the metaphone() of the two strings in PHP.
User avatar
titaniumdoughnut
Forum Commoner
Posts: 33
Joined: Wed Jul 13, 2005 2:02 pm
Location: Manhattan

Post by titaniumdoughnut »

amazing! thanks so much. i'm going to read up on these magical bits of code.
Post Reply