0

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.

OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
renevdkooi
  • 1,515
  • 1
  • 17
  • 42
  • can you explain all the rules you want to implement. let me re-phrase the question for my understanding...If all the user input values are found in the table then matchPrct = 100, if 2 values are found then its 80 it only value is found then some other percentage value is this what is required ? – Ajith Sasidharan Nov 26 '12 at 04:39
  • I suggest using the `score` from [full text search criteria](http://stackoverflow.com/questions/230129/mysql-fulltext-search-score-explained) – OMG Ponies Nov 26 '12 at 04:40
  • I have a big list of names with details (such as fields above) A user will then enter some information into a form. The results are based on what a user has entered. The thing is, it's not a single result in the end. What i'm trying to achieve is to find not only the BEST match but also matches that MIGHT be the person they are looking for. With other words it could be possible that the user entered the name details wrong, but the date of birth was correct. I know the chances are likely, but due to laws I MUST show these as well and let the user choose if it was that person or not – renevdkooi Nov 26 '12 at 06:21
  • so any field found would be a point, the more fields matched the more points. (as the more likely its the person they are looking for) Also, I require pretty fast query times, as users might be searching for a lot of names at a time. – renevdkooi Nov 26 '12 at 06:23

1 Answers1

0

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.

mu is too short
  • 426,620
  • 70
  • 833
  • 800
  • Thanks for the quick reply. Never thought of putting it in the select. How is this performance wise? Any idea? – renevdkooi Nov 26 '12 at 04:46
  • @renevdkooi: You'd have to ask the database, it might be smart enough to recognize the repeated components and it might not be. – mu is too short Nov 26 '12 at 04:49