PHP从数据库中选择行，其中ID在ID组中找到 [英] PHP select row from db where ID is found in group of IDs

问题描述

我有 3 个雇主 ID:1、2 和 3.我通过在数据库和for_emp"列中添加一行来为每个雇主生成任务，我插入了我想为其分配此任务的 ID，并且可以是全部 3 个它们用逗号分隔.假设我有一个任务，for_emp"是1,2,3"，即雇主 ID.如果我想为 ID 2 选择所有任务，我是否能够从 ID 为1,2,3"的行中进行选择，并在那里匹配2"?如果没有，您如何建议我将我的 emp ID 插入数据库中的一行?数据库是 MySQL.

I have 3 employers IDs: 1, 2 and 3. I am generating tasks for each one by adding a line in database and in column "for_emp" I insert IDs I want to assign this task for and could be all 3 of them separated by comma. So let's say I got a task and "for_emp" is "1,2,3", the employers IDs. If I would like to select all tasks for the ID 2, will I be able to select from the row that has "1,2,3" as IDs and just match "2" there ? If not, how do you suggest I insert my emp IDs into one row in database ? The db is MySQL.

有什么想法吗?谢谢.

推荐答案

不要那样做，你应该 规范化您的数据库.

Don't do it like that, you should normalize your database.

你想要做的是有一个表，比如task，然后是task_assignee.task_assignee 将包含字段 task_id 和 user_id.如果一个任务有例如.三个受让人(ID 1、2 和 3)，然后您将在 task_assignee 表中为该一个 task 创建三行，如下所示:

What you want to do is have a table such as task, and then task_assignee. task_assignee would have fields task_id and user_id. If a task has eg. three assignees (IDs 1, 2 and 3), then you'll create three rows in the task_assignee table for that one task, like this:

+--------+---------+
|task_id | user_id |
+--------+---------+
|   1    |    1    [
|   1    |    2    [
|   1    |    3    [
+--------+---------+

然后只需查询 task_assignee 表即可找到分配给给定用户的所有任务.

Then it's just a simple matter of querying the task_assignee table to find all tasks that are assigned to a given user.

以下是如何获取 user_id 2 的所有任务的示例:

Here's an example of how to get all the tasks for user_id 2:

SELECT t.* FROM task AS t INNER JOIN task_assignee AS ta WHERE ta.user_id = 2

编辑.

正如相关说明，即使您没有以正确的方式(我在之前的回答中描述过)，使用诸如 LIKE 之类的 hack 进行操作仍然与最优解.如果您确实存储了逗号分隔值列表，并且需要检查例如.值 2 在列表中，您可以使用 MySQL 的 FIND_IN_SET 函数:

Just as a related note, even if you didn't do it the right way (which I described in my answer previously), doing it with hacks such as LIKE would still be far from the optimal solution. If you did store a list of comma-separated values, and needed to check if eg. the value 2 is in the list, you could use the MySQL's FIND_IN_SET function:

SELECT * FROM task WHERE FIND_IN_SET(2, for_emp)

但是除非您别无选择，否则您不应该这样做(例如，您正在使用某人糟糕的数据库设计)，因为它的效率更低，并且不会让您索引员工 ID.

But you shouldn't do this unless you have no choice (eg. you're working with someone's shitty DB design), because it's way more inefficient and won't let you index the the employee ID.

这篇关于PHP从数据库中选择行，其中ID在ID组中找到的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！