PHP select row from db where ID is found in group of IDs

Question

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.

Any ideas ? Thanks.

Answer 1

The following query should do what you want:

SELECT * FROM tasks WHERE for_emp LIKE '%2%';

However, be aware that that would also match employers 12, 20, 21 etc; so take care if you expect you might end up in double-digits.

However, the other answers about renormalising your database are definitely preferable.

Answer 2

You're doing it wrong. Create a relation table with two fields: employee id and task id. If one task should be assigned to three employees, insert three rows in the relation table.

You then use JOIN to join the task, employee and relation tables.

Answer 3

then its no proper relation...

I would suggest a "mapping table" for the n:m relation

employee
  id

task
  id

employeetask
  task_id
  employee_id

Answer 4

Make a table for your employers. Insert your three rows in it.

Then make a table for mapping tasks to employers. If a task is assigned to three employers, insert three rows into this table. This is basic entity-relation work.

Answer 5

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

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    [
+--------+---------+

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

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

EDIT.

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)

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.

Answer 6

I would make 2 different tables.

1 with employees, and 1 with tasks.

then make another table which combines the two tables, I will call it Assigned Tasks.

Then in assigned tasks I make a assigned id, a employeenumber which is a FK to the employee table and a taskid which is a FK to the Tasks table.

If an employee has more than 1 task. Just insert another row in the assigned table. ;)

When its about Databases, try to think in solo entities! Combining those entities is able in antoher table.

sql example:

Select * from Assignedtasks where employeeID = 1 will give you all his/her tasks. :)

Answer 7

You could use a LIKE '%,2,%' clause in your SELECT statement.

eg:

SELECT * FROM table where for_emp LIKE '%,2,%'

However performance of such non-sargable queries is usually quite bad.

I would suggest that you insert a row each for each employee who is assigned to the task using a separate TASK_EMPLOYEE_MAPPING table with taskId, employeeId as a composite primary key.

With such a design, your query will be

SELECT * FROM TASK_EMPLOYEE_MAPPING WHERE employeeId = '2'