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?
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.
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