Append value to a comma separated column of multiple rows MYSQL

Question

Is there a simple way to skip the comma if column is empty?

I need to add value to a comma separated column of all rows that match the same task_id

============================
id | task_id | comma_sep_col
============================
 1 | 20      |2,3
============================
 2 | 18      |
============================
 3 | 18      |1,3
============================
 4 | 18      |2,3
============================

I am using the follow:

$user_id = '1';
$comma_user_id = ','.$user_id;

$sql = "UPDATE tableName set `comma_sep_col` = CONCAT(`comma_sep_col`,'$comma_user_id') WHERE find_in_set('$user_id',`comma_sep_col`) = 0 AND `task_id` = $task_id";

RESULT:

============================
id | task_id | comma_sep_col
============================
 1 | 20      |2,3
============================
 2 | 18      |,1
============================
 3 | 18      |1,3
============================
 4 | 18      |2,3,1
============================

EXPECT RESULT:

============================
id | task_id | comma_sep_col
============================
 1 | 20      |2,3
============================
 2 | 18      |1
============================
 3 | 18      |1,3
============================
 4 | 18      |2,3,1
============================

SOLVED:

$sql = "UPDATE tableName set `comma_sep_col` = IF(`comma_sep_col` = '','$user_id',CONCAT(`comma_sep_col`, ',', '$user_id')) WHERE find_in_set('$user_id',`comma_sep_col`) = 0 AND `task_id` = $task_id";

$comma_user_id = $user_id ? ','.$user_id :$user_id use simple ternary operator — saurabh kamble
– saurabh kamble, Commented Jan 6, 2016 at 9:09
I don't know how it works inside the mysql statement which it doesn't. Can you explain further? — Michael Eugene Yuen
– Michael Eugene Yuen, Commented Jan 6, 2016 at 9:21
Yes. And I updated $comma_user_id = ','.$user_id; to $comma_user_id = $user_id ? ','.$user_id :$user_id; with the mysql statement untouched and it didn't work. I am assuming that means $sql = "UPDATE tableName set comma_sep_col = CONCAT(comma_sep_col,'$user_id ? ','.$user_id :$user_id') WHERE find_in_set('$user_id',comma_sep_col) = 0 AND task_id = $task_id"; — Michael Eugene Yuen
– Michael Eugene Yuen, Commented Jan 6, 2016 at 12:38
That was exactly what I did (with my mysql query statement untouched) — Michael Eugene Yuen
– Michael Eugene Yuen, Commented Jan 6, 2016 at 13:56

fqxp · Accepted Answer · 2016-01-06 10:15:25Z

4

Try This

$user_id = '1';
$comma_user_id = ',' . $user_id;

$sql = "UPDATE tableName
  SET `comma_sep_col` = IF(
    comma_user_id = '',
    $user_id,
    CONCAT(`comma_sep_col`, '$comma_user_id'))
  WHERE find_in_set('$user_id',`comma_sep_col`) = 0
    AND `task_id` = $task_id";

edited Jan 6, 2016 at 10:15

fqxp

8,0193 gold badges28 silver badges42 bronze badges

answered Jan 6, 2016 at 9:07

Vipin Jain

3,77618 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

saurabh kamble Over a year ago

not need of doing it in mysql best way is manipulate it inside php only by using ternary operator check my comment in the question

Michael Eugene Yuen Over a year ago

$comma_user_id == '' should it be comma_sep_col == '' ? Either way, it doesn't seems to work.

Vipin Jain Over a year ago

@MichaelEugeneYuen yes

Michael Eugene Yuen Over a year ago

It should be comma_sep_col = ' '. And it working great. Thanks for giving me the direction. Can you correct your answer so I can accept that?

Collectives™ on Stack Overflow

Append value to a comma separated column of multiple rows MYSQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related