Comma separated values ​​into selected query

I am new to PHP and I am trying to create a small application for my work, but I got stuck for a few days because I have a problem and I cannot find a solution.

I have 2 tables first by jobs_userac which contains 3 fields:

id, user, jobsassigned


and the second jobs_data strong> containing 5 fields:

id, job_type, description, comments, client_name


Each line in jobs_data contains a task

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


For each user, I have one line in jobs_userac that contains the user id, username and job job in one comma separated field.

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


Every time a user is connected, I store the user ID in the $ user variable. I want to create a query like this

$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 jobs from task_data where job_type is 1 or 2 and if Maria is connected to show her rows from jobs_data where job_type = 4. I want to add a WHERE clause to my query and if the user has more than one joobs assigned in jobs_userac -> jobsassigned to add an OR clause.

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 would be much appreciated.


source to share

7 replies

Your data structure isn't perfect - yours jobs_userac

should have one line per link user<->job


But you can work with what you have:

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




I'm not sure if for the user column on jobs_userac it will store userId or username

this assumes the user is storing the username, or if not, then kindly replace the username with the user ID.

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


Hope this helps you.



In addition, the positions assigned to each user when connected to $ jobsassigned are saved.You can now make the following request:

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


By the way ... it's better to add an extra table where you store your relationship between user and jobs


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



1 - Chris
2 - Peter
3 - Maria



1 - 1
1 - 2
2 - 2
2 - 4
2 - 5
3 - 4




As others have pointed out, your current schema is not optimal and generally not the best way to use a relationship database, but you can use your current structure in a query with a somewhat awkward one:

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


You need whatever you need, as you need to copy "2,5,7", "1,2,3" and "1,2" plus the equal for a simple "2"



First you need to assign the job to the job to the link user:

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




If each job is associated with only one user, that user ID must be saved against the job. so your tables should be id, user

and id, job_type, description, comments, client_name, user_id

. You can then get the tasks for Chris by completing SELECT * FROM jobs_data WHERE user_id = 1

. This is a normal pattern where you have many records in one table related to one record in another.



You mean job_type

instead of id


You should follow:

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


And the same for others, i.e. use id

in place job_type




All Articles