MySQL query with extra manual output based on WHERE

Question

Maybe it's a dumb question, but imagine having a table with fields like:

wholename, lastname, firstname, dateOfBirth

Now I want to search that table based on user input, but I would like to give a match % to the result. Meaning:

If lastname + firstname + dateOfBirth are all found in the database matchPrct = 100. If lastname + dataofBirth are found in the databse matchPrct = 80. And a few other rules (With other words the field matchPrct is an auto generated field which is not really in the db).

SQL for what I would like to achieve is:

SELECT * FROM table 
WHERE firstname="%mike%" AND lastname="%tysson%" AND dateOfBirth="01/01/2012"
(create matchPrct=100) OR ....

Hope what I mean is clear.

Answer 1

LIKE is an operator that returns a boolean value, = behaves similarly. Booleans in MySQL are 1 for true and 0 for false. That means that you can say this:

select *,
      ((firstname like '%mike%') + (lastname like '%tysson%') + (dateOfBirth = '01/01/2012')) / 3.0 as score
from table
where firstname like '%mike%'
   or lastname like '%tysson%'
   or dateOfBirth = '01/01/2012'

And then read your "how well did it match" values out of score. You can also apply individual weights to each component so that a last name match would count for more than a first name match.

In databases that don't use 1 and 0 as booleans you can cast them or use a CASE to convert booleans to the numbers you want. This doesn't apply to MySQL of course but it is worth keeping in mind.