I Have a string foo = "a,b". Now I want to search in the mysql database to get user_id while comparing the string to the likes field. The likes field has data in the format interest => a c v d b. The different characters are seperated by a space. I tried Using like but the result was not upto the mark. How can I go about it?
This is my code
select user_id from users where interest like %foo%;
MySql does not support multiple keyword search in set like field, you should add OR condition of each search keyword with REGEXP
if your format interest like=> a,c,v,d,b then you can use FIND_IN_SET() function otherwise REGEXP provide to exact search.
SELECT user_id FROM users
WHERE interest REGEXP '[[:<:]]a[[:>:]]' AND interest REGEXP '[[:<:]]b[[:>:]]'
this query search only a and b in field not aa, bbax
LIKE does not support exact search.
WHERE interest LIKE '%foo%' OR interest LIKE '%bar%' OR interest LIKE '%oof%'etc. Unless you use an SQL builder you cannot use arrays directly like this (and SQL builders only work because they've made the code to do it for you).