I'm working on a search feature to search for model numbers and I'm trying to get MySQL to show me results similar to what I'm asking for but LIKE %$var% doesn't do it.
Example (we'll call table, "tbl_models"):
id model
+-------+--------------------+
| 1 | DV6233SE |
| 2 | Studio 1440 |
| 3 | C762NR |
+-------+--------------------+
When searching using a search box I'm currently using:
SELECT id, model FROM tbl_models WHERE model LIKE %$var% ORDER BY id DESC
If I search for "C7" it'll return "C762NR" which is fine, but say I were to search for "C760" or "C700" or a typo of "C726NR"? Is there a way in MySQL (or PHP, JS, jQuery) that I can expand the limit of what results are returned to include different variations or close matches?
I'm not looking for someone to write it for me either, just a push in the right direction would be very helpful!
If I were to apply logic for your question, I will go this way.
To find close matches -
I will take input parameter originally typed by user. e.g. 'ABCDEF' Then I will create multiple parameters from it by replacing each character in input parameter by '_'.
I.e. 'ABCDEF' will produce following input parameters for me.
'_BCDEF' , 'A_CDEF', 'AB_DEF', 'ABC_EF', 'ABCD_F', 'ABCDE_'
Then I will pass input parameters to SQL query and use OR operation to find data.
Above approach will give me words differing by 1 characters.
I can extend this a bit by replacing 2 characters with UNDERSCORE, then 3 characters then 4 and so on.
Upto how many characters I have to replace, should depend on the length of string.