I'm looking for an effective way to do the following search in MySQL/PHP...
Imagine I have a number of fields in my DB I wish to search on:
User.username
Name.first_name
Address.line1
Phone.number
Email.email_address
I also have the following variables (with example data) in PHP to search with:
$username = "john123";
$name = "john";
$address = "10 fake street";
$phone = "23456789";
$email = "john@johnsemail.com";
Assuming there are 0 complete matches, how would I write a query which could see partial matches and then return results ordered by the number of matches?
For example, using my example data I'd might expect to see a result look something like this,
username | name | address | phone | email | matches
----------------------------------------------------------------------------
john123 | john | 12 new street | 23456789 | john@johnsemail.com | 4
tim123 | tim | 10 fake street | 23456789 | tim@timsemail.com | 2
Just to note, I'm not looking for a wild card search here. I want to return results which have exact matches, just not necessarily a complete match using all DB field. And also want to prioritize the results by the number of matches.
I can think of a very inefficient way of doing it by running each as a separate query, loading that into a PHP array then counting which IDs are found in the most arrays. However, the database running this has millions of records per table, so this wouldn't be feasible at all.
SELECT *,
if (username = '$username', 1, 0) +
if (name = '$name' , 1, 0) +
if (phone = '$phone' , 1, 0) +
if (address = '$address' , 1, 0) AS matches
FROM tab
ORDER BY matches DESC