查询逗号分隔的字段? [英] Querying comma separated field?
问题描述
我正在尝试基于逗号分隔的字段输出一个选择下拉选项,例如,如果用户被分配了 1 和 2 的 ID,那么我想输出位置 id 有 1 和 2:
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:
用户表:id(等于 1)和 usergrouplocid,这是一个带有 (1,2) 的文本字段
Users table: id (which equals 1) and usergrouplocid which is a text field with (1,2)
位置表:id 其中 auto_increment 和 loc_id 见下面的图像:
Location Table: id which auto_increment and a loc_id see iamge below:
我的查询如下:
$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";
上面的查询工作正常,但它只输出 1 的 id 在 usergrouplocid 中存在的位置,所以我如何让查询找到 1、2、3 或 4 等是否在 usergrouplocid 中.
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.
推荐答案
以防万一你真的需要它现在排序而不重新设计你的数据库(我会)...
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";
这篇关于查询逗号分隔的字段?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!