How to approve user changes?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
galbus
Forum Newbie
Posts: 10
Joined: Sun Jun 03, 2007 1:07 pm

How to approve user changes?

Post by galbus »

Hello,

First post here - hopefully someone will be able to help me. :)

I am writing a script that has a role based access system. There are 2 roles: content managers and administrators.

The content managers can make changes to the web site but any changes need to be approved by an administrator before going live.

Examples of changes are:
adding a new case study;
editing a page;
deleting a news story.

The 2 ways I can imagine this being done are:

1. Storing the SQL statement in a table. When admin approves the change the SQL statment is performed.

2. 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).

I like the simplicity of the first method and in my head it is the one that 'seems' right, though I am not sure how I can get the administrator to see the changes that the SQL statement will perform without altering what the user sees at the front end of the site.

I'd like to know if anyone has any suggestions on which method would be best - or if you can suggest another method?

Cheers. :D
Last edited by galbus on Sun Jun 03, 2007 2:35 pm, edited 1 time in total.
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

id just have a 'validated' column... and only display the rows that are validated... so to approve an item, all you have to do is change the value of validated from 0 to 1, or somthing like that
galbus
Forum Newbie
Posts: 10
Joined: Sun Jun 03, 2007 1:07 pm

Post by galbus »

Thanks for your suggestion but that doesn't account for changes (UPDATE queries).

If I were to only show rows where validated == true, when a change occurs and the validated status is set to false then the original value of the unaltered row would not appear in the front end of the site.

And if the change was rejected by admin then the original data will have been lost.

The 2nd method I desribed was a way of working around this - creating a new 'temp' row with the new information in there.

Logic is still telling me that the first method is the best way but I don't know how to show the results of a query to admin without altering the row and changing what the front end of the site will display.
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

Maybe you're thinking about this correctly, and not saying it correctly. Or, maybe you're just not thinking correctly. :lol:

I do see what you're saying with updates, but when it comes to updates, you have to be aware of the ways that updates will work in the first place. Do they just append to the end, or can they update the entire thing?

If they can update the entire thing, then you'll want to have a whole second table for unapproved content. 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.
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

superdezign wrote:If they can update the entire thing, then you'll want to have a whole second table for unapproved content. 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.
...was going to be my second suggestion.

I dont like the concept of storing the SQL data, simply because it makes it harder for the admin to see whats going to happen. I mean, do you really want to read SQL statments, instead of some table or report-type format?
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

maliskoleather wrote:I dont like the concept of storing the SQL data, simply because it makes it harder for the admin to see whats going to happen. I mean, do you really want to read SQL statments, instead of some table or report-type format?
Yeah. Plus, how strange is it to store SQL using SQL? It's possible, but... weird. :P
User avatar
maliskoleather
Forum Contributor
Posts: 155
Joined: Tue May 15, 2007 2:19 am
Contact:

Post by maliskoleather »

the other problem i see with storing SQL to a database is in storing it itself. you'd have to escape it all, otherwise the database would probably freak about having multiple statements inside a statment. I can just see that going goofy sooner or later :?
galbus
Forum Newbie
Posts: 10
Joined: Sun Jun 03, 2007 1:07 pm

Post by galbus »

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...
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

galbus wrote:
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).
Right. Which is why I said you might be thinking about it right, but saying it wrong.

The point of databasing is storage, and the goal of databasing is organization. Rather than put it all into the same table, you can separate them. This will make it easier to determine which unapproved version corresponds to which approved version, as well as simplify the displaying process of both the unapproved items and the approved items.
galbus wrote: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?
I'm completely against that.

SQL tables don't take up that much space, and it's completely natural to have a lot of them. The more you have (usually), the more organized your data is. However, tables with too many columns are not at all what'd be considered "organized." The added table wouldn't force you to change anything in the database other than adding the table. Think about it... I'm pretty sure it's the most solid option you've got.

I don't see any downside to the suggestion we've given you. But saving SQL queries? Why run two SQL queries in a row to accomplish something that one SQL query could have done in the first place?
Post Reply