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
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.
source to share
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
work
1 - Offset - Blablablabla - Blablablabla - Chris
2 - Plotter - Blablablabla - Blablablabla - Nick
3 - Design - Blablablabla - Blablablabla - John
users
1 - Chris
2 - Peter
3 - Maria
user_job
1 - 1
1 - 2
2 - 2
2 - 4
2 - 5
3 - 4
source to share
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"
source to share
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);
$job_types=$row['jobassigned'];
$result = mysqli_query($con,"SELECT * FROM jobs_data where id IN (".$job_types.")");
source to share
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.
source to share