MySQL matching 2 out of 5 fields

Question

In MySQL I'm trying to select any row that matches at least 2 fields of the provided data

Eg. I have been given firstName, lastName, dob, website, email and I want any rows that match firstName and lastName, or firstName and email, or website and email etc.

I know I could write a very long winded statement along the lines of (this=this AND this=this) OR (this=this etc but this query could potentially get really large, especially if we decide we want to match on more than 5 fields.

We will also need to rank the matching rows, so if some rows match 3 instead of only the minimum 2 fields then they should show up higher in the returned results.

I could process this afterwards with PHP, or do multiple SQL queries, I'm just wondering if anyone knows an easier/cleaner way to match this data?

I appreciate any help! Jo

Answer 1

You could count up the matching expressions. MySQL returns 1 for true and 0 for false.

WHERE (FirstName = ?) + (LastName = ?) + (... = ?) > 2

You can also order using this as well. You will want to sort descending to ensure that the higher matches appear first.

ORDER BY ((FirstName = ?) + (LastName = ?) + (... = ?)) DESC