Reputation: 914
So, I have this table, where I store some numbers separated by ",". For me, it is ok to store them like this, because when I echo them, I need them in this exact order.
Is there any way through which I can add some numbers at the end of the existing ones?
Here is the table:
id | mat_id | note |
-----------------------------------
1 | 1 | 1,2,3,4 |
I want, through a form, to be able to add a number at the end, as follows:
id | mat_id | note |
-----------------------------------
1 | 1 | 1,2,3,4,5 |
I did not find anything about such thing on google...It would be very useful. Thanks a lot!
Upvotes: 1
Views: 75
Reputation: 49089
It is often not a good idea to store comma separated values in your columns. But if you really can't change your database structure, you can use CONCAT_WS:
UPDATE tableName
SET note = CONCAT_WS(',', note, '5')
this will automatically insert a comma after the note if there's some data already, otherwise it will just add the string '5':
SELECT CONCAT_WS(',', '1,2,3,4', '5');
1,2,3,4,5
SELECT CONCAT_WS(',', NULL, '5');
5
of course, you need to make sure that you are not using empty strings otherwise it will fail:
SELECT CONCAT_WS(',', '', '5');
,5
or it will complicate the query a little:
UPDATE tableName
SET note = CONCAT_WS(',', CASE WHEN note<>'' THEN note END, '5')
Upvotes: 1