Query to find people with multiple names

I’m working on a fairly big project that allows users of my client’s site to search for people they used to know who might’ve changed their names, primarily due to marriage. Basically, users sign up with all the names they’ve ever used and the data gets entered into a MySQL database. The next step is actually creating the search, which allows users to put in the names they remember someone by.

This is an example registration in the database:

username first0 first1 first2 first3 first4
(email)  Lisa   John   Frank  Sam    Smith

Right now, my query looks like this:

SELECT * FROM NAMES
WHERE (
    `names`.`first0` IN ("Lisa", "Sam", "")
    AND `names`.`first1` IN ("Lisa", "Sam", "")
    AND `names`.`first2` IN ("Lisa", "Sam", "")
    AND `names`.`first3` IN ("Lisa", "Sam", "")
    AND `names`.`first4` IN ("Lisa", "Sam", "")

**Substitute out the exact names there for data entered in the search form.

This query doesn’t work because first2 = Frank, which isn’t included in the list. If a person doesn’t know every name someone entered in the database, everything breaks.

However, I don’t think I want to use OR statements, because I don’t want all the results where a name matches Lisa to be returned if someone puts Lisa as just one of the names.

Does anyone know how I could go about doing this?

Answer

You’re constrained unnecessarily here. Consider instead:

User
UserId, OtherData

UserNames
UserId, Name, Kind

Data like:

UserId, OtherData
1, Blahblah

UserId, Name, Type
1, Lisa, First 
1, Jane, Middle
1, Smith, Last   
1, Leese, First
1, Jones, Last

Lisa Jane Smith, also known as Leese, married and changed name to Lisa Jane Jones

If you present the website user with a “Enter any number of names and indicate their kind” you can then run a query that finds the userids that have all the name kinds mentioned

This can be as simple as dynamically joining, or doing conditional aggregation

Eg if the web user enters “Leese” as firstname and “Jones” as lastname then you can:

SELECT UserID FROM UserNames 
WHERE 
  (name = 'Leese' AND type = 'First') OR
  (name = 'Jones' AND type = 'Last')
GROUP BY
  UserID 
HAVING count(*) = 2 --2 is the number of names entered, leese and jones.  If your where clause had 5 names, this would be 5

Or you can use a join approach (and this for if the web user enters 3 names/kinds):

SELECT u1.UserID 
FROM
  UserNames u1 
  INNER JOIN Usernames u2 ON u1.UserID = u2.UserID
  INNER JOIN Usernames u3 ON u1.UserID = u3.UserID
WHERE 
  u1.name = 'Leese' AND u1.type = 'First' AND
  u2.name = 'Jones' AND u2.type = 'Last' AND
  u3.name = 'Smith' AND u3.type = 'Last' AND
  

When you go to form your dynamic queries, don’t concatenate the values into SQL. Just because you’re making a query dynamic built is not an excuse to throw parameterization out of the window. You build the SQL by concatenating together the SQL string with parameter placeholders in and then give the parameters values separately

Pseudocode (i don’t know php) in a language that interpolates its strings with {placeholders}:

string fromclause = ""
string whereclause = ""
for i = 0 to webrequest.form.values.length - 1

  string name = webrequest.form.values[i].name
  string type = webrequest.form.values[i].type


  if i == 0 then
    fromclause = fromclause + "Usernames u{i}"
  else
    fromclause = fromclause + " INNER JOIN Usernames u{i} ON u1.UserID = u{i}.UserID

  whereclause = whereclause + " u{i}.Name = @paramName{i} AND u{i}.type = @paramType{i}"

  sqlcommand.parameters.add("@paramName{i}", name)
  sqlcommand.parameters.add("@paramType{i}", type)

next i

sqlcommand.commandtext = "SELECT UserID FROM {fromclause} WHERE {whereclause}"

You finish up with a sql command that is all parameterized and the parameters are all given values..