WordPress Multi Site (WPMU) close all old posts script.

by

I'd previously closed all comments on posts over 30 days old, across all 700 blogs on the installation, so I was dismayed to be getting comment spam still. How was it sneaking in? After looking through my logs for POST comments I pulled out a bunch of likely looking pages.

grep wp-comments-post access.log

OK so it seemed that although all the WP Posts were locked down, some Pages and some Media posts were still not locked down — the 30 day expiry thing must only apply to Posts. Grrr. The internet revealed no easy way to fix this so I looked in the database and crafted a query which would do this for me on one table …

UPDATE `wp_1_posts` SET `comment_status` = "closed", `ping_status` = "closed" WHERE `post_modified` < "2013-10-31 11:59:59"


This closed all posts which were modified before October 31st last year. You can of course alter the date accordingly … 🙂

OK, so now to get a list of relevant databases:

SHOW TABLES LIKE "wp\_%\_posts"

You'll need those backslashes in there to escape the underscore character. This will give you a list of databases. Then use a bit of text editor search and replace (thank you jedit), to create an SQL script like this:

UPDATE `wp_100_posts` SET `comment_status` = "closed", `ping_status` = "closed" WHERE `post_modified` < "2013-10-31 11:59:59";
UPDATE `wp_101_posts` SET `comment_status` = "closed", `ping_status` = "closed" WHERE `post_modified` < "2013-10-31 11:59:59";
UPDATE `wp_102_posts` SET `comment_status` = "closed", `ping_status` = "closed" WHERE `post_modified` < "2013-10-31 11:59:59";
UPDATE `wp_103_posts` SET `comment_status` = "closed", `ping_status` = "closed" WHERE `post_modified` < "2013-10-31 11:59:59";
UPDATE `wp_104_posts` SET `comment_status` = "closed", `ping_status` = "closed" WHERE `post_modified` < "2013-10-31 11:59:59";
UPDATE `wp_106_posts` SET `comment_status` = "closed", `ping_status` = "closed" WHERE `post_modified` < "2013-10-31 11:59:59";
UPDATE `wp_107_posts` SET `comment_status` = "closed", `ping_status` = "closed" WHERE `post_modified` < "2013-10-31 11:59:59";
UPDATE `wp_10_posts` SET `comment_status` = "closed", `ping_status` = "closed" WHERE `post_modified` < "2013-10-31 11:59:59";

… etc.

Don't forget the ; at the end of the line. Then you can just run the script via the import function of PHPmyadmin, or pipe it into mysql with the command line.

mysql -u user -pxxxxxx database_name < script.sql

Sweet deal. Locked down.

Leave a Comment