phpBB old topics locker

I had this request to implement a feature on a phpBB board whereby topics older than one month to get deleted. A quick search on phpbbhacks.com revealed a result - Auto Lock hack, but it had a disturbing note saying that it's not recommended for larger boards, although I couldn't figure out why. Anyway, the hack looked too involved, with DB changes, new Admin Panel options, etc., so I decided to hack something together quickly. Turned out to be pretty easy. Here' s the result.

The SQL query

UPDATE phpbb_posts AS p, phpbb_topics AS t
  SET t.topic_status = 1
WHERE p.post_id = t.topic_last_post_id
  AND FROM_UNIXTIME(p.post_time) + INTERVAL 1 MONTH < NOW()
  AND t.topic_status = 0

Basically the logic is so simple that it can be done in one query. The topics table contains the id of the last post. I join the posts table to figure out the date/time of the last post. And if it's old enough, I set the topic status to 1 (locked).

I like the INTERVAL MySQL thing so I used it here, converting the Unix timestamps to human dates. It makes it much more easier to see at a glance that the period is one month and quickly change it afterwards if need be, without starting the old and tired calculation: "OK, 60 seconds times 60 minutes times 24 hours... Wait a sec! Is it seconds or milliseconds?"

The common.php

Instead of dealing with cron jobs, I decided to execute the query in common.php, a script that is always loaded on every page. I execute the query and send myself an email of something wrong happens.

<?php
$sql = 'UPDATE phpbb_posts AS p, phpbb_topics AS t
            SET t.topic_status = 1
        WHERE p.post_id = t.topic_last_post_id
            AND FROM_UNIXTIME(p.post_time) + INTERVAL 3 MONTH < NOW()
            AND t.topic_status = 0';
if (!$db->sql_query($sql))
{

    @mail('me@example.org',
          '[thesite] Locker',
          'Error while locking old topics',
          'From: meagain@example.org'
         );
}
?>

A probabilistic touch

Now, executing this query is not necessary on every page load. So I added a touch of probability.

<?php
if (mt_rand(1, 10) == 5) { //chance one in ten to run
    // do the stuff
}
?>

That means that this code has a chance one in ten of being executed on a page load. Even this is a bit high, but can always be changed to 1 in a 100 for example.

Distribution?

This was done only for internal purposes, not designed to be made available to the larger phpBB community. Otherwise it would be better if it doesn't hard-code the "phpbb_" table prefix, as well as the table names in general and the topic status value. Instead, phpBB constants must be used - like POSTS_TABLE, TOPICS_TABLE and TOPIC_LOCKED.

Tell your friends about this post on Facebook and Twitter