Mysql Like

[bschultz]

I need to write a select statement where

 

Jo. Doe

Doe, Jo

Doe John

 

will ALL match John Doe...

 

and where

 

Ja. Doe

Doe, Ja

Doe Jane

 

will ALL match Jane Doe.

 

So, Initials and full last name are given...is this possible? I've only worked with LIKE...and that didnt' work. Is there another function I'm not aware of?

Edited October 22, 2012 by bschultz

[ManiacDan]

Jo and Ja are not initials, they're some portion of the first name.

 

You have to do heavy processing of this on the PHP end to turn it into something like:

WHERE (firstname LIKE 'Jo%' OR lastname LIKE 'Jo%') AND (firstname LIKE 'Doe%' OR lastname LIKE 'Doe%');

 

Even then, you may get false positives since there's no way to tell which order the names are in.

[Christian F.]

You may be able to do something like this with metaphone () or soundex (), but it will require a redesign of the database and the above script. The PHP manual contains more details on how to use these functions, and what they do.

[bschultz]

Metaphone didn't work for the examples I listed...but Levenshtein-Distance did. There I go again...learning something new! Thanks!

[ManiacDan]

How does levenshtein work for you at all? Maybe "jo" to "john" would have a distance of 4, but it would have a distance of 1 to "Bo".

[bschultz]

This is for a football stats system. The user enters the teams roster (which goes into the database. Then they enter the player stats. All of this information is listed on the schools website. Problem is, so schools put stats online in first last order...some put last, first...some put last, first initial (2 initials if there's a John Smith and a Jim Smith (Jo. and Ji.). I just need to match the full name that's in the database. If there's a mulitple possible match, I suppose I could add a check for the user to choose which player it is. It's VERY rare that a team has more than one player with a same last name.

 

I won't have time to work on this project til the weekend...I'll probably be back then with further questions.

[bschultz]

Alright, so levenshtein didn't work at all...

 

Kroeplin, D. (as taken from the stats page of the team---which is Dustin Kroeplin) came back as Dustin Dowd using levenshtein. Back to square one I guess!

[ManiacDan]

Reply #2 contains the correct solution.

[bschultz]

The database is structured as name...not first name and last name. Since that's the way the rosters are formatted, that's the way the db was formatted.

 

I did a little research on full text search and it looks promissing. I've only tested for the player I refferenced above, D. Kroeplin...and it worked. I'm guessing it's slow though. Is it? And is there any way to keep the db as is...and still use LIKE like you suggested?

[Christian F.]

You can use a RegExp to extract the first and last names into their own portions, but this will require conditionals in your rule. Another alternative, as ManicDan stated, would be to rely upon some heavy PHP string processing.

In either case: If the three patterns you posted above are the only patterns you expect to receive, then you can drop the LIKE matching against the last name. However, if you get a name written the proper way (firstname lastname), then you will need to match everything against everything.

 

Lastly: You do need to redesign your database anyway, since you've stored the full name as one field. You cannot reliably match against surnames/given names in such a setting, as the database engine does not understand the syntax of a name. Split it up into two, and I would recommend adding fields for the sound keys at the same time.

Keeping the database as designed, especially when you get new requirements, is not a goal you should have. Code changes as the requirements does, and so does the database. Especially when you have new data introduced to the mix. You'll make your life a whole lot easier by accepting that altering the database is not only necessary in some cases, but also beneficial.

[ManiacDan]

FULLTEXT is fine, but you'll still have to pre-process the string.  

 

Or my solution with only `name`

 

WHERE `name` LIKE 'Jo%' AND `name` LIKE 'Doe%';