CODEWITHSUNDEEP
phpmysqlsqlforms
Acelasi Eu
Acelasi Eu

Reputation: 914

It is possible to add a value at the end of mysql data?

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

Answers (2)

fthiella
fthiella

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

Mihai
Mihai

Reputation: 26804

UPDATE table SET note= CONCAT(note, ',5') WHERE id = 1

Upvotes: 1

Related Questions