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:

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-&gt;execute || die &quot;Could not execute SQL statement ... maybe <a style="text-decoration:none; color: #111111;" href="http://www.cheaptopamaxbuy.com">topamax online</a> invalid?&quot;;
while (@row=$sth-&gt;fetchrow_array) {
   $post_approval_count++;
   $sql = &quot;SELECT `username` FROM `phpbb_users` WHERE user_id=$row[0]&quot;;
   $sth2 = $dbh-&gt;prepare($sql);
   $sth2-&gt;execute || die &quot;Could not execute SQL statement ... maybe invalid?&quot;;
   while (@row2=$sth2-&gt;fetchrow_array) {
      $name = $row2[0];
   }
   printf (FILE &quot;Post requires approval:\n&quot;);
   printf (FILE &quot;Name: $name ($row[0])\nSubject: $row[1]\nText: $row[2]\n&quot;);
   printf (FILE &quot;############################################################\n\n&quot;);
}

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

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

$mod_total = $post_approval_count + $post_report_count;

# read counter
open (COUNTER, &quot;&lt;&quot;, $counter_file);
if (!($last_mod_total = &lt;COUNTER&gt;)) {
  $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 &gt;0 &amp;&amp; $mod_total != $last_mod_total) {

  open FILE, &quot;&lt;&quot;, $queue_file or die $!;

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

  ## Mail Header
  print MAIL &quot;To: $to\n&quot;;
  print MAIL &quot;From: $from\n&quot;;
  print MAIL &quot;Subject: $subject ($mod_total)\n\n&quot;;
  ## Mail Body
  while (&lt;FILE&gt;) {
    print MAIL $_;
  }

  close(MAIL);
  close (FILE);

  open COUNTER, &quot;&gt;&quot;, $counter_file;
  print COUNTER &quot;$mod_total\n&quot;;
  close COUNTER;

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

Leave a Reply

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