If any combination of these 3 (Name, Surname, DoB, Nationality) user inputs matches to database records then the query shouldn't stop to avoid duplication. If there any special function in MySQL because running 12 query wouldn't be nice for it!

Thanks in advance

5 Years
Discussion Span
Last Post by smantscheff

Your 4 (count them: four) user inputs can be tested for duplicate combinations just by one query:

select * from mytable where 
   name = <name>
or surname = <surname>
or DoB = <dob>
or Nationality = <nationality>

If you want to test for combinations with at least two factors, use

select * from mytable where 
   (name = <name> and surname = <surname>)
or (name = <name> and DoB = <dob>)
or (name = <name> and Nationality = <nationality>)
or (surname = <surname and DoB = <dob>)

Thanks for reply.

(name = $name AND surname = $surname AND dob = $dob) OR
(name = $name AND surname = $surname AND nationality = $nationality) OR
(name = $name AND dob = $dob AND nationality = $nationality) OR
(surname = $surname AND dob = $dob AND nationality = $nationality)

Obviously clause above will work for my case cos I have only 4 columns. However in future if I have more than 10 columns to be checked surely it wouldn't be a good to type one by one.

So I guess MySQL guys don't provide a function for us to use something like this:

blah_function(name of the columns, how many should match)

blah_function(name, surname, dob, nationality, 3)

I don't know of such a function.
I'd rather code a routine outside of mysql which generates the mysql query from an array of fieldnames and then poses it explicitly to mysql. mysql does not lend itself easily to meta operations on database object names.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.