Database Reporting? SQL? Access? HELP!

[DementedElf]

I assist in running an online Bulletin Board/Forum. I have no access to change the forum itself, but I have administrative rights to it. Basically, we have about 45000 members, but we're trying to report violations on some members... 2300 users (and growing) that we're trying to report. We only report users that break rules, so that we may assign them disciplinary action later. We have several categories, including "foul language", "spam", and "advertising". We have about 25 Moderators to the website, and about 10 Administrators.

Basically, when Moderators see a violation, they report the username and violation, and we then update the report and update the username thread, and that takes a few minutes. I would like to create an Access Database, or SQL, or something, so that we can have the following:

**Administrators can add new usernames (ALL usernames are unique)

**Moderators can submit a report that says WHO commit a violation, and WHAT the violation was for, that would be pending the acceptance of the Administrator.

**Administrators would then ADD the violation (confirm the Moderator submission) to a running total. If that total reaches a certain number, a "red flag" so to speak would come up indicating that their violation total has reached a certain number.

**Mods could submit requests, Admin could edit, subtract, add, and close them

**This would need to be user-friendly for non-programmers, maybe a form.

**This would need to be on a website, and password protected, but accessible by certain authenticated users.

Example:
Jane goes to a website.
Jane logs in as a moderator.
Jane submits to a form: Inputs "user123" to a textbox, and chooses from a dropdown list "Foul Language", then submits a website URL in a "description" textbox.

Sean logs in as an administrator, and sees that user123 has a pending violation to be added. Sean accepts/confirms the violation, and user123's data comes up.

Sean sees user123's data, and his foul language violation has been updated from 4 to 5. Sean also can see the rest of the violation types & tallies also.

Sean would also have access to the entire database and totals, whereas Jane would only have access to submit a form.
----------
Is this possible? If so, how, and using what program or language, or both? What would I need?

 

[TheHeadFL]

It would help to know what forum software and SQL/DB software you are using.

I am mostly familiar with phpBB, so I'll give you the rundown there...

You NEED access to the SQL server to make changes. You will need to be able to create a table and add, update, and delete rows from it.

You also NEED access via FTP or some other means to upload php scripts to within the forum directory, or otherwise some other location on the server.

In phpBB, moderator status can be gleaned by grabbing the users cookie, running a DB query for that user, checking the cookied hash (password check), and comparing the user_level field. If user level is 0, user has no priveleges. User level 1 is admin, User level 2 is admin.

Then you basically write a custom PHP script/form that does what you're talking about. Each report gets added as a new row in the new table. Each row has a few status boxes for various things. One of which could be admin_approved, which is default 0.

When admins log in to the script, they see a list of pending (admin_approved == 0) reports. They can choose to individually (or collectively) approve them.

Then you have another script that can run a check down the current reports, and if a certain user has a certain number of confirmed reports, they get whatever disciplinary actions. Then, you take all these reports and set a flag (action_taken or something) to 1 so they are not counted for future disciplinary actions.

The best way to do this would also have access to the forum software, where you can go add a button near each post so you can submit a report that way. Also, you could have it check the 'reports' tally against each user everytime they log in, saving you from having to manually apply the actions.

 

[DementedElf]

I have no access to change the forum itself, but I have administrative rights to it.

Like I said, unfortunately, I have no access to change the forum. It's UBB.threads™ 6.4.1 powered my InfoPop, but that doesn't matter. I have no access to make any changes. I would be hosting it on a seperate website, on a seperate server. The site owner does not grant permission to change the UBB. I would have to make it a standalone site.