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.
