I have 3 columns in a mysql table. I'm using Innodb engine. I want to be able to search for some values on those columns and also anything close to those values. For example :
We have 3 columns as "Fast" "Smart" "Sharp" and they are of type boolean/int
I have 1000 records in the table, as an example, I list 5 here.
Fast | smart | Sharp
------------------------
0 1 1
1 1 1
0 0 1
1 1 1
1 0 0
and let's say we wanna search for people who are smart and sharp as 0 1 1 but if there
is no record with the value of 0 1 1 we want to get the closest to it which is 1 1 1
or 1 0 1 or etc. So now when I search for it, I get the exact value, if the value doesn't
exist, I won't get anything back from DB. So how can I achieve this and if I get a bunch of
result, how can I sort them from the closets to my query to the feartest ?
Best Answer
Assuming you want to match against 1 1 1 you could do something like this:
That should return the exact matches with score 3, people with 2 matches with score 2 and so on.