PHPBB Inactive Member Removal Cron Job

A commenter to a previous article asked exactly how I delete inactive members from a PHPBB forum that I run. So I'll try to explain. This solution runs on Linux/Unix systems...I'm sure it could be done for Windows, but I'll leave the particulars to you.

It's really two separate steps. First, you need a script which will handle the deletion of inactive members. I called mine cron.php. It deletes all inactive PHPBB users who don't activate within 48 hours. It looks like this:

#!/usr/bin/php -q
<?php
  // cron job to delete inactive users older than 48 hours
  $db=mysql_connect('server','user','password');
  mysql_select_db('your_phpbb_database_here',$db);
  $strSQL="DELETE phpbb_users u, phpbb_user_group ug, " .
    "phpbb_groups AS g FROM phpbb_users u, " .
    "phpbb_user_group ug, phpbb_groups g WHERE " .
    "u.user_active=0 AND u.user_id>0 AND " .
    "u.user_id=ug.user_id AND ug.group_id=g.group_id " .
    "AND g.group_single_user=1 AND " .
    "FROM_UNIXTIME(u.user_regdate)<" .
    "DATE_SUB(NOW(),INTERVAL 2 DAY);";
  mysql_query($strSQL,$db) or die(mysql_error());
  mysql_close($db);
?>

You'll need to make sure the /usr/bin/php points to the location of PHP on your system, and replace the MySQL server name, user, and password with yours.

Now that you have a script, you need to tell the system to run it daily. You can do this with a cron job. If you have command line access to your website, you might be able to do this with "crontab -e". But my webhost has an administrative panel that lets you set up cron jobs on the web. If you can't set up a cron job, you could put the script into a web-accessible folder and periodically call its URL, either manually or through an automated process on your local PC.

This idea works great if the majority of your spam registrations don't activate their account. Usually they just want their spam links in your member list. But I'm finding that more and more spammers are activating and posting, so it remains that we want to stop spammers from registering in the first place. I'm experimenting with another method, which I'll post about when I see some results.

Update 2007-11-28: I replaced my original SQL statement with the SQL in comment #1 below, which I finally tested and it seems to work well.