KrnlPanic's Linux Notes and Tips

Working with linux since kernel version 2.0.30

PHPBB Moderator Queue Notifications

PHPBB3 does not have a built-in moderation queue notification capability. So, if a new user posts in a forum that requires approval, then the post will sit there in the modification queue until a moderator either sees the icon on the post, or visits the Moderator Control Panel (MCP).

So, after bit of digging around, I couldn’t find a decent modification that would do what I wanted, but I did come across a handy script on MugginSoft.com.

The script is written in perl and is designed to run via a cron job that queries your phpBB forum database for unapproved an reported posts. I have it running at 15 minutes past every hour, currently via the following entry in root’s crontab:

[code]
15 * * * * /home/webadmin/mod_queue.pl
[/code]

I made a couple of minor modifications to the script to help with readability.

[code]
#!/usr/bin/perl

use DBI;

# origin : http://www.phpbb.com/community/viewtopic.php?f=72&t=832195&start=15

# The next six lines are the only ones you should need to change.
$database_name = "databasename"; # your database name, the same one phpBB uses
$database_username = "dbusername"; # your database username, the same one phpbB uses
$database_password = "dbpassword"; # your data base password, the same one phpBB uses
$forum_address = ""; # http://www.example.com/forums
$to=”; # email to address(es) – Comma delimited user@host.com,user2@otherhost.com, etc
$from= ”; # email from address

# optional changes
$subject=’PHPBB3 moderation required’; # Include your forum name here
$counter_file = "check_moderator_count.txt";
$queue_file = "check_moderator_queue.tmp";

$post_approval_count = 0;
$post_report_count = 0;

open FILE, ">", $queue_file or die $!;

printf (FILE "###############################################\n");
printf (FILE "Visit following link to view moderation queue\n");
printf (FILE "– $forum_address/mcp.php?i=main&mode=front\n");
printf (FILE "###############################################\n\n");

# No changes below this line.
$database_name = "dbi:mysql:".$database_name;
$dbh = DBI->connect($database_name,$database_username,$database_password);

# Check for Posts Needing Approval
$report_index = 0;
@col_titles[$report_index] = "Posts";
# Create a query of the data base.
$sql = "SELECT `poster_id`, `post_subject`, `post_text` FROM `phpbb_posts` WHERE post_approved=0";
# Give the data base a chance to prepare. Perhaps it will compile the request.
$sth = $dbh->prepare($sql);
# Run the query.
$sth->execute || die "Could not execute SQL statement … maybe invalid?";
while (@row=$sth->fetchrow_array) {
$post_approval_count++;
$sql = "SELECT `username` FROM `phpbb_users` WHERE user_id=$row[0]";
$sth2 = $dbh->prepare($sql);
$sth2->execute || die "Could not execute SQL statement … maybe invalid?";
while (@row2=$sth2->fetchrow_array) {
$name = $row2[0];
}
printf (FILE "Post requires approval:\n");
printf (FILE "Name: $name ($row[0])\nSubject: $row[1]\nText: $row[2]\n");
printf (FILE "############################################################\n\n");
}

# Check for Reported Posts
$report_index = 0;
@col_titles[$report_index] = "Posts";
# Create a query of the data base.
$sql = "SELECT `poster_id`, `post_subject`, `post_text` FROM `phpbb_posts` WHERE post_reported=1";
# Give the data base a chance to prepare. Perhaps it will compile the request.
$sth = $dbh->prepare($sql);
# Run the query.
$sth->execute || die "Could not execute SQL statement … maybe invalid?";
while (@row=$sth->fetchrow_array) {
$post_report_count++;
$sql = "SELECT `username` FROM `phpbb_users` WHERE user_id=$row[0]";
$sth2 = $dbh->prepare($sql);
$sth2->execute || die "Could not execute SQL statement … maybe invalid?";
while (@row2=$sth2->fetchrow_array) {
$name = $row2[0];
}
printf (FILE "Reported Post:\n");
printf (FILE "Name: $name ($row[0])\nSubject: $row[1]\nText: $row[2]\n");
printf (FILE "############################################################\n\n");
}

printf (FILE "$forum_address/mcp.php?i=main&mode=front\n");
printf (FILE "Posts to Approve: $post_approval_count\n");
printf (FILE "Posts to Review: $post_report_count\n");
close (FILE);

$mod_total = $post_approval_count + $post_report_count;

# read counter
open (COUNTER, "<", $counter_file);
if (!($last_mod_total = <COUNTER>)) {
$last_mod_total = 0;
}
close(COUNTER);
unlink($counter_file);

# send email only if moderation total has changed.
# this keeps the noise down while allowing regular checks.
if ($mod_total >0 && $mod_total != $last_mod_total) {

open FILE, "<", $queue_file or die $!;

# use sendmail – works on postfix configured system also
open(MAIL, "|/usr/sbin/sendmail -t");

## Mail Header
print MAIL "To: $to\n";
print MAIL "From: $from\n";
print MAIL "Subject: $subject ($mod_total)\n\n";
## Mail Body
while (<FILE>) {
print MAIL $_;
}

close(MAIL);
close (FILE);

open COUNTER, ">", $counter_file;
print COUNTER "$mod_total\n";
close COUNTER;

# silence is best here if we are running this from the crontab
# as output gets forwarded to the machine admin.
# print "A message has been sent from $from to $to\n";
} else {
# print "Moderation queue is empty\n";
}
[/code]

Leave a Reply

www.buycheap-pillsonline.com www.buyantibioticshere.com www.ordergenericpropeciaonline.com www.genericpropeciabuyonline.com