SELECT * FROM table WHERE (my_id) IN table_column (assigned

I have a table called tasks Where has a column named assigned_id which has comma separated values like (1, 2, 3, 4, 5). I need to count of the tasks user has assigned.

In Example: My name is Imdad and my user_id is 5, and the tasks table has column assigned_id which values are like 1, 3, 4, 5, 7... I need to count all the number of rows which assigned_id contain my user_id which is 5.

I have tried so many methods but not working till now.

I have tried

SELECT * FROM tasks WHERE $user_id IN (assigned_id); Here the user_id is 5

It's returning if the assign_id value start with 5 (Example: 5, 3, 6, 7...)

Here is my code

<?php
$data = "SELECT COUNT('1') FROM task WHERE $user[id] IN (assigned_id)";
$get_data = mysqli_query($conn, $data);
$show = mysqli_fetch_array($get_data);
echo $show[0];
?>

I am attaching a screenshot for better understand!!!

Here I have marked the data which I want

But Here is the image with query, I got the first value

CodePudding user response：

As per my understanding on the above query this should work for you in MYSQL

if you want to get based on task_title

Select count(*) from tasks where assignee_id LIKE (CONCAT('%', (Select id from tasks where task_title = 'Imdad' LIMIT 1), '%'));

if you want to get based on id

Select count(*) from tasks where assignee_id LIKE (CONCAT('%', (Select id from tasks where id = 5 LIMIT 1), '%'));

Hope this helps.

CodePudding user response：

In such case you can use find_in_set function like:

select * 
from tbl
where find_in_set(5, assigned_id);

SQL online editor