Need to remove 1000 spam comments within a data range without removing legit comments
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_gmt are the fields that you'll play
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
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
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.