How to compare CSV in db table field to the variable which also contain comma seperated values?

I Have MySQL DB table tbl_users, which has two uid, name, usergroupid. usergroupid contains the comma separated integers (IE: 1,2,3).

I want all the users which contain the user group ids say 1or 2 or 3 or 1,2 or 2,3 or 3,1 or 1,2,3. I am getting the value to compare in database from a variable which itself contains the comma separated values say, 1.2.3 or 1,2 or 2,3 or 3,1 or 1,2,3. Now what would be the select query to fetch the result.

I am using below select query to find the result but its wrong.

SELECT user_id, user_fname, user_lname 
  FROM tbl_u开发者_如何学Pythonsers 
 WHERE user_group_id IN (".$usergroupid.")

here $usergroupid is the variable which contains the CSVs

The best thing to do in this situation is to use pattern matching to find the required values.

SELECT user_id, user_fname, user_lname  FROM tbl_users WHERE user_group_id LIKE (%".$usergroupid."%)

This is if you want the exact pattern in the csv, else you have to break your csv according based on , and run it in a loop to get the desired result.

But i think the best thing for something like this is having another table usergroup with user and group fields which will be a mapping from user to group. So in this case you can just do

SELECT userID FROM usergroup WHERE groupid IN $usergroupid

LIKE has the potential to pick up on completely the wrong usergroup if you do LIKE '%1%' then if you've a group 10, 11, 12 etc. it'll match.

Assuming that all of your strings are of the form x,y,z,... then a less naive LIKE would be more robust.

SELECT user_id, user_fname, user_lname
FROM tbl_users
WHERE (',' + user_group_id + ',') LIKE '%,".$usergroupid.",%'

Have you tried using FIND_IN_SET? I've been using it sometimes and it works pretty well in those cases.

"FIND_IN_SET(user_group_id,".$user_group_id".)"

Some more info: here