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:

15 * * * * /home/webadmin/mod_queue.pl

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

#!/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";
}