MySql: add data to column without delete the previous ones

Question

Im trying to make a note saving system. I have an html login page, and when user logins, a form appears to enter some notes to store. I have 3 columns in my mysql database: username, password and storedData.

For example lets say that a user has these : Username:david | Password:david | StoredData: apple,orange.

Is there a way to execute a query, so when user comes back and add another note NOT to overide the previous notes(apple, orange)

instead, to add another note to the database like: I add banana. Now the database should be StoredData:apple,orange,banana.

Answer 1

You can use string concatenation: concat_ws() comes handy for this, because it skips null values (so this takes in account the case when the column is initially empty):

update mytable 
set stored_data = concat_ws(',', stored_data, 'banana')
where name = 'david'

Note, however, that your design is not properly normalized. You have a 1-N relationship between a user and its notes: you should have a separate table to store the notes, with each note on a separate row, rather than storing a delimited list:

table users

name    | password
david   | ...

table notes

user_name  | note
david      | apple
david      | orange
david      | banana

Related reading: Is storing a delimited list in a database column really that bad?.

Answer 2

You may have to consider if the value already exists in the column and not update:

update tablename
set storeddata = case 
  when find_in_set('banana', storeddata) then storeddata
  else concat_ws(',', storeddata, 'banana')
end
where username = 'David';

See the demo.