At work I'm making a PHP web app that uses a database to store several thousands of records about individuals. I want to allow the user to easily find an individual by name. The names are stored in the following format: Last,First Middle. Users can also search by (at the same time) driver's license # and social security #.
How would I write the query or the application so that the results are in order from most likely to least likely? I was thinking of the following, but if anyone has any suggestions or better ideas I'd be more than happy to hear them.
1. I could search by the first few (maybe 3) characters of their last name and also the first 3 of their first name (e.g., Smith,Jonathan Joesph = Smi%,Jon%). This might show too many results though. I could also use the entire string they enter rather than the first 3 characters and just tack on a wildcard.
2. I could have more than one query. The first would query look for an exact match of the driver's license # OR social security #. The second would search by exact name. The third would do the wild card search mentioned in step 1. With this I would run into the problem of showing the same person more than once. But I could make build a string after each query containing the ID #s of the individuals shown so far (each individual has an autonumber not known by the users), then in successive queries I could just exclude those ID #s.