CODEWITHSUNDEEP
mysql
user1001176
user1001176

Reputation: 1165

search query to compare a particular word from a string to values of another string in mysql

I am making a search module in which i require to search details of users matching the details of other user for that i need to compare user details . for details like age, gender etc i have used like '%search_term%' and was able to get the results i wanted but i am not able to make a query for details like user 1 speaks english,french,spanish so its stored in database like words seperated by comma as the same markup. and suppose other user just have one language in common latin,hindi,french ie french in common then how to filter that user too according to user 1's details as such he/she still have something in common. The following query is for normal values such as age 

select * from users_profile 
where age like '%$search_term%' 
order by created_time DESC

how to make that query within this one using AND and get the results?

Upvotes: 1

Views: 72

Answers (1)

Shawn Balestracci
Shawn Balestracci

Reputation: 7540

You can do a self join and then replace the commas in one of them with pipes and then use that regular expression with rlike. Performance is likely to be horrible.

select users_profile.*, users_profile2.id as other_id, 
  users_profile2.language as other_lang from users_profile
  inner join users_profile as users_profile2 on
    users_profile.language rlike 
      replace(users_profile2.language,",","|") 
    and users_profile.id != users_profile2.id;

You can see for yourself: http://sqlfiddle.com/#!2/9549f/6

Upvotes: 3

Related Questions