Hamming distance via bit_count error php

[rani_4]

I have record with strings:

record id         value             type
sn      1       aaaa181c1c3e9997   single
sn      2       caca381c1c3e6483   masculine

These are almost 500+,they are stored in mysql which i fetch,converting record to 64bit(bitcout work only integer),they are returned as all queires stored,not most like.I use code:

SELECT * FROM MY_TABLE ORDER BY BIT_COUNT(CAST(CONV(record,16,10) as unsigned integer) ^ CAST(CONV('$submit', 16, 10) AS UNSIGNED) ) LIMIT 1;

It is working fine,requirement is suppose supplied value is aaaa181c1c3e9997,then it display all queries whether it match or not.I just want to fetch only it matches more than 15%,else don't fetch anyone.Please help what i'm missing. I also used like:

SELECT * FROM MY_TABLE ORDER BY BIT_COUNT(CAST(CONV(record,16,10) as unsigned integer) ^ CAST(CONV('$submit', 16, 10) AS UNSIGNED) ) < 15;

[cereal]

Have you tried by moving the condition to the WHERE statement? For example:

SELECT * FROM MY_TABLE WHERE BIT_COUNT(CAST(CONV(value,16,10) as unsigned integer) ^ CAST(CONV('$submit', 16, 10) AS UNSIGNED) ) >= 14;

[Keimpe]

only fetch columns you need,
LIKE helps you search
LIMIT number of results

SELECT id, value, type FROM MY_TABLE WHERE value LIKE CAST(CONV('$submit', 16, 10) AS UNSIGNED) LIMIT 15;

[cereal]

@Keimpe

Hi! You're correct about the select statement, however the original query is performing a bitwise xor between values in table and the submitted input, then the result is read by BIT_COUNT() and compared through >= which is not a limit clause.

So, if my interpretation of the OP request is correct, my previous query should work.

Live example: http://sqlfiddle.com/#!9/233e97/1

Docs about bit count: https://dev.mysql.com/doc/refman/5.7/en/bit-functions.html