MySQL Select Where IN Comma Separated List

The IN operator matches a single table value against any of a list of provided values. It doesn’t work in reverse like you want it to. SQL doesn’t see the values in assigned_users as a list of possible integers. It’s simply a string of characters. You might think of using the LIKE operator to match a substring in the column values. This means using wildcards to match the rest of the string once the substring is matched. This where you run would run into trouble, as individual numbers do not have unique delimiters. Matching LIKE '%3%' will match 3 OK, but also 13, 33, 31, etc. Matching LIKE '%,3,%' will match an internal 3 and not 13, 33, etc., but will not match a terminal 3 like in ‘1,2,3’ or a beginning 3 as well. LIKE is no good ??

In order to match the beginning or end of a list as well as internal occurrences, you need to use REGEXP or RLIKE to match a regular expression pattern. I’m not sure what the correct REGEXP is offhand. You can use one of several RegExp “fiddle” sites (such as regexr.com) to work out the right pattern.

Sorry this is not a complete answer, but at least it should get you pointed in the right direction.

You will have an easier time performing database queries if you can change the structure of the data. For example, instead of having users as a comma-separated list of users in a single cell, have another table that relates users to groups.

So groups would look like:

+----+-----------+---------+
| id | entity_id |  name   |
+----+-----------+---------+
|  1 |         1 | Group 1 |
|  2 |         1 | Group 2 |
+----+-----------+---------+

Then you’d assign users to groups in a table, user_groups like:

+----+---------+----------+
| id | user_id | group_id |
+----+---------+----------+
|  1 |       1 |        1 |
|  2 |       1 |        2 |
|  3 |       2 |        1 |
|  4 |       3 |        2 |
+----+---------+----------+

Then if you wanted to get the names of all groups that a user belongs to, the query would be:

SELECT 
	groups.name 
FROM 
	groups
LEFT JOIN
	user_groups ON
		groups.id = user_groups.group_id
WHERE
	user_groups.user_id = 1

I agree that you should consider rebuilding your table, but if that is not possible, the following query should give you the results you desire:

$sql = "SELECT group_name FROM mytable WHERE FIND_IN_SET('{$user_id}', assigned_users) <> 0";

Please make sure that the assigned_users column has an index. If there are a pre-set number of users, then the assigned_users column should be of type SET. That will make the query run much faster.