-2

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%;
Ajay Ganvir
  • 367
  • 2
  • 7
  • 23
  • 1
    You need to change the query to `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). – h2ooooooo Dec 02 '14 at 07:12
  • Asked and answered already http://stackoverflow.com/questions/9435225/array-in-mysql-where-like – Chris Caviness Dec 02 '14 at 07:13
  • you have an string "a,b,c,d,e,f...." and one interest field in DB which has space separated values (a b c d e f......). You want to compare each value of the string to each value of the interest field??? – MixedVeg Dec 02 '14 at 07:25

1 Answers1

1

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.

Girish
  • 11,907
  • 3
  • 34
  • 51