How to update database record and keep old values ​​in MySQL?

Question

I want to update a column by adding a new value along with the old ones. Therefore, if the column “fruits” has the value “apples” and I run my query, it should then have the value “apples, oranges”.

Right now when I am doing the upgrade instructions

UPDATE tableName SET fruits='oranges' WHERE id=1; 

He just rewrites apples with oranges. How can I make it add a new value along with the old, separated by commas?

Answer 1

 UPDATE tableName SET fruits=CONCAT(fruits, ', oranges') WHERE id=1; 

or

 UPDATE tableName SET fruits=CONCAT_WS(', ', fruits, 'oranges') WHERE id=1; 

Answer 2

 $reason_old = mysql_query("SELECT $col_name FROM `table` WHERE `unique_field` =$id"); $reason_fetch = mysql_fetch_array($reason_old); $reason_box = $reason_all[$reason_fetch ]; $anyvariable= "UPDATE `$table_name` SET `$col_name ` = '$new_data , $reason_box' WHERE `unique_field` =$id"; $yes_good = mysql_query( $anyvariable, $conn ); 

Answer 3

I would recommend you keep two tables. The first table looks like this.

 TableFruit -> FruitID (PRIMARY KEY) + general fruit attributes 

And then another table in which such versions are supported.

 TableFruitVersions -> VersionID (PRIMARY KEY), FruitID (FOREIGN KEY), IsActive (BOOLEAN) + specific fruit attributes 

You can add one record for the first time in both tables. The general attributes of the fruit, or the context in which it appears, will fall into the first table; and a specific fruit ("apple") will move on to the next.

Next time you add a certain fruit (“orange”), make the previous entry in the second table false, and add a new entry immediately after.