superdezign wrote:Do they just append to the end, or can they update the entire thing?
They can update the entire thing.
superdezign wrote:The unapproved version and the approved version should have the same id number. When content is added, the whole updated version is inserted into the table for unapproved content. When content is approved, it is moved to the table with approved content, using the same id and, if the approved version already exists, overwriting it. Then, you'll probably want to delete it from the unapproved table.
I think this is working the same way as I suggested in 2:
galbus wrote:Creating a new hidden row in the appropriate table. When admin approves the change the original row is deleted and the new row is activated with the ID of the deleted row (as to keep all existing links working).
If I explain this process in a bit more detail:
- When a change is made by a content manager a new row with the data is added to the appropriate table with the row 'update' set to '1'.
Administrators log in and are presented with a list of all updates (" where update=1' ").
If a change is refused then the new row is deleted.
If a change is accepted then the old row is deleted and the new row is set as "update='0' " (to stop it from being hidden at the front end) and is given the ID of the old deleted row (this will preserve existing links).
I was keen on saving the SQL commands as then I could keep track of all changes that had occurred and I thought it would be easier to keep a history and roll back any changes by reverting to the previous SQL statement for that row. The escaping doesn't matter as I escape everything that goes in the database anyway.
The idea is that the administrators would never have to see and read the SQL. They would see a dynamic page that presented what the changes
would look like after the proposed SQL statement had been commited.
My problem with this was that I didn't know if I could show an administrator what the result of an INSERT/UPDATE statement would look like
without actually commiting the statement to a table. I've never had to use temporary tables yet but I think I'm thinking from the name that they might be suitable so I'm going to look into it tomorrow and see if they would do the job.
Although I see what you are saying about it being wierd storing the SQL in the database, unless anyone still thinks it is a very bad idea it still seems like a good option to me. If I worked this way then all I would need to create would be one extra table called 'sql_updates' and that would cater for the whole system. I wouldn't have to alter any of my existing tables or my queries adding bits like " where update=0 " everywhere (we already have around 40 tables in the schema). Unconventional maybe, but it just might save me quite a bit if time and effort?
Thanks for your help so far. I'm still open to ideas and suggestions...