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
解决方案
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