CODEWITHSUNDEEP
mysql
Michael Eugene Yuen
Michael Eugene Yuen

Reputation: 2528

Append value to a comma separated column of multiple rows MYSQL

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";

Upvotes: 0

Views: 2404

Answers (1)

Vipin Jain
Vipin Jain

Reputation: 3756

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";

Upvotes: 4

Related Questions