The problem. True story: a friend of mine recently created his own blog and started to promote it everywhere on the Internet. After some weeks of intensive work, he spent some days on vacation without Internet access.
When he came back home, he looked at his blog and saw… 5000+ comments awaiting moderation! Of course, most of them were spam, but he was actually about to check them all to make sure he did not delete a valid comment made by one of his regular readers.
The solution. Happily, my friend told me about his spam problem. He had already spent 45 minute manually deleting spam when I showed him this useful SQL tip.
- Log in to phpMyAdmin and select your WordPress database.
- Click the “SQL” button. Paste the following code in the SQL command window:
DELETE from wp_comments WHERE comment_approved = '0';
- Goodbye bad comments! Enjoy your spam-free database!
Explanation. The wp_comments table contains a field named comment_approved, which is a boolean value (1 or 0). Approved comments have a value of 1, and comments awaiting moderation have a value of 0. By running the above command, we simply delete any comments that haven’t been approved yet.
Be careful. While this solution can be pretty useful if you have millions of spam comments to delete, it will also erase valid unapproved comments. If you don’t already use Akismet, install it now to prevent spamming.