Need to remove 1000 spam comments within a data range without removing legit comments

  • are you familiar with databases? – tpaksu Mar 24 '12 at 9:13
  • Does any one know if Akismet works on comments submitted before you activate it? does it scan through old comments? – Ole Henrik Skogstrøm Mar 24 '12 at 11:05

Unfortunately, WordPress does not provide any functions to select comments by date range, as far as I know. And although you could wp_delete_comment in a loop, it would take unnecessarily long.

As the WordPress database entity relationship diagram shows, comments are limited to being in the wp_comments table and the wp_commentmeta table, which is convenient.

Use the MySQL command line client or phpMyAdmin to issue queries directly. If you:

SELECT * FROM `wp_comments` LIMIT 1

...you'll see the structure of the comment data. comment_date and comment_date_gmt are the fields that you'll play BETWEEN with. comment_date is the date of the comment offset by the timezone setting you set in Settings/General. comment_date_gmt is the UTC+0 time, the universal truth.

SELECT * FROM `wp_comments` WHERE `comment_date` BETWEEN '2010-01-15 00:00:00' AND '2012-01-15 00:00:00';

No need to explain. Once you've backed up your database (I really mean it, back it up, please, things can go wrong), you are free to:

DELETE FROM `wp_comments` WHERE `comment_date` BETWEEN '2010-01-15 00:00:00' AND '2012-01-15 00:00:00';

Now, as the database structure shows us we'll be left with lingering metadata in the wp_commentmeta table.

SELECT * FROM `wp_commentmeta` WHERE `comment_ID` NOT IN (SELECT `comment_ID` FROM `wp_comments`);

A subquery will take care of these. Once you feel comfortable, substitute SELECT * for DELETE FROM.

To schedule such cleanups, like "delete all comments that older than a year", you would couple these queries together with different flavours of wp_schedule_event and the wpdb class to issue queries. To construct the date you would use the date functions offered by WordPress.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.