How do you use MqSQL update to append to a row?

Question

I am making a basic CMS system which allows for page edits by registered users.

I wish to store a list of users who have submitted some page content to each a specific page. In my database table for pages, I have a column called contributed, and in there I want to have all the user id’s of each user who contributed to each page, separated with a common. Looking something like this ...

Page Name | Author | Comments | Contributed
---------------------------------------------------------------
Home      | 1      | 0        |1, 2, 3, 4
About     | 1      | 0        |1, 3, 4, 7, 9
Contact   | 2      | 0        |2, 4
Download  | 8      | 0        |8

Using MySql, how can I write an update query that appends another user id to this column when a user makes a contribution instead of updating the entire row, removing the ids of those who have previously contributed?

Answer 1

For this, it would be best to create some sort of history table. Example:

**page_history**

id
page_id //ID of the page
user_id //ID of the user making the change
date //Date and time of the change
from //The old content
to //The changed content

Then you'd insert a new row into page_history every time a user edits a page. Note: You should read up on the concept of Database Normalization.

Answer 2

I might end up doing this on one level up - without any knowledge of size of your CMS:

CREATE TABLE test(pageid INT, contributed VARCHAR(1024));
INSERT INTO test(pageid, contributed) VALUES (1, "kala");
UPDATE test SET contributed = CONCAT((SELECT contributed FROM (SELECT * FROM test) as a WHERE pageid = 1),',','new_user_name') WHERE pageid = 1;

For SELECT * FROM test there is good description at You can't specify target table for update in FROM clause