php - Querying comma separated field?

Question

I am trying to output a select dropdown options based on a comma separated field where if the user has been assigned an ID of 1 and 2 for example then I want to output the locations id has 1 and 2:

Users table: id (which equals 1) and usergrouplocid which is a text field with (1,2)

Location Table: id which auto_increment and a loc_id see iamge below:

My query is as follows:

$query = "SELECT room_location.*, client_room.*, users.* FROM room_location INNER JOIN client_room ON room_location.user_loc_id = client_room.id INNER JOIN users ON room_location.user_loc_id = users.userGroupLocID WHERE 1 IN (userGroupLocID) ORDER BY room_location.location";

The query above works fine but it only outputs where the id of 1 exists in usergrouplocid, so how do I get the query to find if 1, 2, 3 or 4 etc is in the usergrouplocid.

Answer 1

Just in case you really need it sorted now without redesigning your database (which I would)...

$ids = array(1,2,4);

$query = "SELECT room_location.*, client_room.*, users.* FROM room_location INNER JOIN client_room ON room_location.user_loc_id = client_room.id INNER JOIN users ON room_location.user_loc_id = users.userGroupLocID WHERE userGroupLocID REGEXP '(^|,)(".implode('|',$ids).")(,|$)' ORDER BY room_location.location";