PHP/MySQL Moderation Tutorial?

BeerNuts

BeerNuts

New member
Sep 4, 2008
1,865
25
0
www.beermonies.com
I am building a site and learning php/mysql along the way. I have a form where users can post data to the database and pages that display the data. Easy.

What I want to do now is build a sort of back end where all the form posts will go pending moderation. Once approved I want them to go to the database.

Simple enough.. but I can not find any resources on how to program something like this. Google has failed me.

So anyone have any tutorials or pages they can send me to for building such a thing?
 


What you should do is have an extra field on the table called "approved" and then have it put 0 in as default, then in your SQL query to display data you should set it to "select * from table where approved = "1"" and then it will only show comments that you have approved.

Then make a page to show them all where it is 0 and then have an approve button which will run an update query to change it to 1 or a delete button to delete it.

Simple :)

If you need any help feel free to PM
 
Also simple. When you construct the table that stores submitted data, add in a field that's a boolean. You can make its value 0 by default, so that when you finish moderating you set it to 1, and it displays.

In a nutshell:

  1. When creating the table add
    Code: 
    published BOOL DEFAULT 0
    to the table definition.
  2. When displaying data, do a SELECT... AND published = 1 . That way you're only displaying data that's been proactively published.
  3. When approving posts, simply set published = 1, from its default value of zero.

[edit: haha nick-harper said the same thing while I was typing]
 
wow awesome idea, for some reason I was thinking I would need to make a new table and then have pending in that table and move them to the live table.

This is a much much simpler way of doing that. Thanks.

What would I do if I wanted to moderate changes to existing data? I am making a wiki of sorts where users can post new information and update existing ones if they have errors.

But I need both of these changes to pend moderation so its not full of spam or fake posts.

Thanks
 
You might also want to consider building some sort of userApproved column in so you can count how many approved articles they have in the past, you could then do some sort of:

Code: 
if ($approved > 50)
{
     //Auto Approve
     mysql_query("insert into blah (title, approved) values ('$title', 1);
}
else
{
     //Moderate comment
     mysql_query("insert into blah (title) values ('$title');
}

Security always do:

Code: 
$title = mysql_real_escape_string($_POST[title]);
 
You are going to need another approach if you want to moderate updates to existing data.

If you're using a field in the table for 'approved' and set it to 0 when they update existing data, not only will their changes be in moderation, but their original data will no longer be viewable. The approved comments attached to it will probably also give you issues, depending on the logic you used to display them.

possible solution: Look at the things in the post that would cause you to not approve them and try to prevent those. This is more of a 'patch it as you go' approach and can be cumbersome to manage (essentially it's blacklisting character sequences).

another possible solution: treat each submission (posted to moderation) as its own record in the table, split the table into post_id, post and another that has post_id, revision_num and parent_post (and other fields you may pull from the original table) to tie the original post to any/all of its subsequent revisions. Do all your queries on the new table. This way is more efficient because one table will have nothing but ints (including unix style datestamps) and the other will hold the varchar/longtext fields.


This is also a good time for you to learn about sessions if you haven't already. You may not need it immediately, but could eventually make some hard things easier.
 
jryan21 said:
another possible solution: treat each submission (posted to moderation) as its own record in the table, split the table into post_id, post and another that has post_id, revision_num and parent_post (and other fields you may pull from the original table) to tie the original post to any/all of its subsequent revisions. Do all your queries on the new table. This way is more efficient because one table will have nothing but ints (including unix style datestamps) and the other will hold the varchar/longtext fields.
Click to expand...

This is the proper solution to allow people to modify any data in a web app
 
Thanks for all the help guys. I have finished the beta layout of my site and its has one live database, comments, and a moderation database.

I am sending all new data to the live table but setting approved to 0 (not active), all edited data is being sent to the mod_data table and is not displayed. Comments are in their own table they can not be edited so 1=approved 0=not.

The front end is done, I now need to build the admin gui so I don't have to always be in phpmyadmin. I am planing a display all where approved=0 and then have a way to mass read and update them to approved=1 for comments and new items. For the edits want to find a way to update the data in the live table with the mod table and also delete it from the mod table. This will be done by matching primary keys, but I need to figure out the method of copying the data and deleting the edit.

Advice is appreciated so far I am planing a combo of UPDATE and DELETE. For security all data is mysql_real_escape_string and my config/connect is blocked with the htaccess anything else I should worry about?
 
You must log in or register to reply here.
Top Bottom
Back