Query duplication check

[veledrom]

Hi,

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

[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>)
...

[veledrom]

Thanks for reply.

[B]WHERE[/B]
(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:

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

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

[smantscheff]

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.