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.
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
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
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);
$job_types=$row['jobassigned'];
$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
.