CODEWITHSUNDEEP
mysqlsql
Ered
Ered

Reputation: 497

ignore last comma from MYSQL query

SQL TABLE: products

+--+------------+
|id| product_id |
+--+------------+
|1 | 689        |
|2 | 616,22,38  |
|3 | 666        |
|4 | 234,789    |
+--+------------+

SQL QUERY:

$id_pro= 23;
$sql = "UPDATE products SET product_id = CONCAT_WS(',',product_id, '$id_pro') WHERE id=3";

RESULT:

+--+------------+
|id| product_id |
+--+------------+
|1 | 689        |
|2 | 616,22,38  |
|3 | 666,23     |
|4 | 234,789    |
+--+------------+

PROBLEM: If product_id ends with a comma:

+--+------------+
|id| product_id |
+--+------------+
|1 | 689        |
|2 | 616,22,38  |
|3 | 666,       |
|4 | 234,789    |
+--+------------+

the Result is this:

+--+------------+
|id| product_id |
+--+------------+
|1 | 689        |
|2 | 616,22,38  |
|3 | 666,,23    |
|4 | 234,789    |
+--+------------+

how can i fix this, and make the query detect if the row ends with a comma and ignore it?

Upvotes: 0

Views: 126

Answers (1)

John Conde
John Conde

Reputation: 219834

Try using REPLACE()

 REPLACE(CONCAT_WS(',',product_id, '$id_pro'), ',,', ',')

Upvotes: 3

Related Questions