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?
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..
A normalised schema might look like this…
id username first 1 (email) Lisa 2 (email) John 3 (email) Frank 4 (email) Sam 5 (email) Smith 
With your current schema, a statement like this would probably work:
SELECT * FROM `names` WHERE ( `names`.`first0` = "Lisa" OR `names`.`first1` = "Lisa" OR `names`.`first2` = "Lisa" OR `names`.`first3` = "Lisa" OR `names`.`first4` = "Lisa" ) AND ( `names`.`first0` = "Sam" OR `names`.`first1` = "Sam" OR `names`.`first2` = "Sam" OR `names`.`first3` = "Sam" OR `names`.`first4` = "Sam" ) But as other people point out, you should have a different normalized schema like this:
CREATE old_firstnames ( username VARCHAR(255), firstname VARCHAR(255), timestamp DATETIME, PRIMARY KEY (username, firstname, timestamp) ) Your data is then probably be:
username firstname timestamp (email) Lisa 1998-02-01 (email) John 2001-03-02 (email) Frank 2006-01-04 (email) Sam 2009-11-13 (email) Smith 2018-09-01 And you can search your user by:
SELECT * FROM old_firstnames n1 LEFT JOIN old_firstname n2 ON (n1.username = n2.username) WHERE n1.firstname = "Lisa" AND n2.firstname = "Sam" 
