在select查询中使用逗号分隔值

I'm new to PHP and i'm trying to create a small app for my work but i'm stuck few days now because i have a problem and i can't find a solution.

我是PHP新手，我想为我的工作创建一个小的应用程序，但是我现在被困了几天，因为我有一个问题，我找不到解决方案。

I have 2 tables first jobs_userac that contains 3 fields:

我有两个表，首先jobs_userac包含3个字段:

id, user, jobsassigned

and the second jobs_data that contains 5 fields:

第二个jobs_data包含5个字段:

id, job_type, description, comments, client_name

Every row in jobs_data contain a task

jobs_data中的每一行都包含一个任务

1 - Offset  - Blablablabla - Blablablabla - Chris
2 - Plotter - Blablablabla - Blablablabla - Nick
3 - Design  - Blablablabla - Blablablabla - John

For every user i have one row in jobs_userac that contains the user id, username, and the assignes jobs in one field comma separated.

对于每个用户，我在jobs_userac中都有一行，其中包含用户id、用户名和在一个字段中分隔的assignes作业。

1 - Chris - 1,2
2 - Peter - 2,4,5
2 - Maria - 4

Every time a user is connected i store the user id into a variable $user. I want to create a query like this

每次连接一个用户时，我将用户id存储到一个变量$user中。我想创建这样的查询

$result = mysqli_query($con,"SELECT * FROM jobs_data");

But i want to check if the connected user has a row in jobs_userac for example if the connected user is Chris to show him only the jobs from task_data where the job_type is 1 or 2 and if Maria is connected to show her the rows from jobs_data where the job_type=4. I want to add the WHERE clause into my query and if the user has more that one joobs assigned in jobs_userac--> jobsassigned to add the OR clause.

但我想检查是否连续连接用户例如如果连接用户在jobs_userac克里斯只给他的工作从task_data job_type玛丽亚是1或2,如果连接到显示她的行jobs_data job_type = 4。我想将WHERE子句添加到我的查询中，如果用户有更多的joobs分配到jobs_userac—> jobs赋值添加OR子句。

For user Maria

为用户玛丽亚

$result = mysqli_query($con,"SELECT * FROM jobs_data WHERE job_type=4");

For user Chris

为用户克里斯

$result = mysqli_query($con,"SELECT * FROM jobs_data WHERE job_type=1 OR job_type=2");

For user Peter

为用户彼得

$result = mysqli_query($con,"SELECT * FROM jobs_data WHERE job_type=2 OR job_type=4 OR job_type=5");

Any help will be very much appreciated.

如有任何帮助，我们将不胜感激。

7 个解决方案

#1

Your data structure is not ideal - your jobs_userac should have one row per user<->job link.

您的数据结构并不理想——您的jobs_userac每个用户应该有一行<->作业链接。

But you can work with what you have:

但是你可以利用你所拥有的:

SELECT * FROM `jobs_data` WHERE FIND_IN_SET(`job_type`, "2,4,5")

#2

I am not sure for the column of user at jobs_userac whether it is storing a userId or username

我不确定jobs_userac的用户列是存储用户id还是用户名

here it is assuming that user are storing username or if not then kindly replace the username with the user id.

这里假设用户正在存储用户名，或者如果不存储用户名，请将用户名替换为用户id。

select * from jobs_data where IN(select jobsassigned 
                                 from jobs_userac 
                                 where user = 'Perter');

I hope this helps you.

我希望这对你有帮助。

#3

Additionally store jobsassigned of each user on connection in $jobsassigned Now you can do the following query:

另外，将每个用户在连接上分配的jobsassign存储在$jobsassigned中，现在您可以执行以下查询:

$queryString = 'select * from jobs_data where id IN ('.$jobsassigned.')';

By the way... it is better to add an extra table where you store youre relations between user and jobs

顺便说一下…最好添加一个额外的表来存储用户和工作之间的关系

jobs

工作

1 - Offset  - Blablablabla - Blablablabla - Chris
2 - Plotter - Blablablabla - Blablablabla - Nick
3 - Design  - Blablablabla - Blablablabla - John

users

用户

1 - Chris
2 - Peter
3 - Maria

user_job

#4

As others noted your current schema is sub-optimal and generally speaking not the best way to exploit a relation data base however you can use your current structure in a query with the somewhat clumsy :

正如其他人注意到的，您的当前模式是次优的，一般来说，不是利用关系数据库的最佳方式，但是您可以在查询中使用您的当前结构，但有些笨拙:

"SELECT * FROM JOBS_DATE WHERE id = '2' or id like '2,%' or id like '%,2,%' or id like '%,2'"

You need all thee likes as you have to copy with "2,5,7", "1,2,3" and "1,2" plus the equal for plain "2"

你需要所有你喜欢的东西，就像你必须复制2 5 7 1 2 3 1 1 2 1 2 + = 2一样

#5

First you have to get the job type assign to the link user:

首先，您必须获得分配给链接用户的作业类型:

$result = mysqli_query($con,"SELECT * FROM jobs_useracc where id=".$user);
$row = mysql_fetch_array($result);
$job_types=$row['jobassigned'];
$result = mysqli_query($con,"SELECT * FROM jobs_data where id IN (".$job_types.")");

#6

If each job is only every associated with one user, the id of that user should be stored against the job. so your tables should be id, user and id, job_type, description, comments, client_name, user_id. Then you can get the jobs for Chris by doing SELECT * FROM jobs_data WHERE user_id = 1. This is a normal pattern for where you have many entries in one table associated with one entry in another.

如果每个作业只与一个用户关联，则该用户的id应该存储在作业中。表应该是id, user和id, job_type, description, comments, client_name, user_id。然后，您可以通过在user_id = 1的jobs_data中执行SELECT *来获得Chris的作业。这是一个正常的模式，在一个表中有许多条目与另一个表中的一个条目相关联。

#7

You are referring to job_type instead of id.

您指的是job_type而不是id。

You should follow :

你应该遵循:

$result = mysqli_query($con,"SELECT * FROM jobs_data WHERE id=2 OR id=4 OR id=5");

And same for others i.e. use id at place of job_type.

对于其他人也一样，例如在job_type中使用id。

#1