[Solved] Querying comma separated field?

Question

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";

Accepted Answer

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";