Suppose I have a table of a few thousand people, with a FirstName field
and a LastName field. Sadly, my people are not so regular. Some names
have three parts (e.g., due to marriage) crammed into the two fields
("Hillary Rodham Clinton"). Some even have titles ("Dir, gastroent.
dept., Fubar hosp. OurTown") wedged in there. I want to make a web app
that searches this table incrementally as I type into a web page in my
browser. I am thinking I will have to do something like continuously
display the top 10 matches to what I have typed so far. Of course, when I
am typing I do not know exactly what is in the database. I generally know
only some of the parts of the name when I am typing (e.g., I am looking up
"Mary Jones" without knowing whether Jones is her maiden name). Sometimes
I am even typing something that is a spelled a bit wrong ("Schiller" vs.
"Shiller") or variantly ("Lizzie" vs. "Elizabeth"). This seems pretty far
from what MySQL can do directly. I know about "LIKE" matching. I know
the wider SQL community has something called "soundex", but I have not yet
found it in MySQL. I have a hard time imagining what will help me with
variants on a name. I do not see any easy way to find the "top 10"
matches. Am I missing anything that can help me here?
Thanks in advance!