Just to set the expectations right, this article is for the people who already have a WordPress blog and are facing the issue of spam comments in already approved comments. Spam comments could get approved in a lot of scenarios like when you forget to configure Akismet plugin, or when you approve all the spam comments by mistake. The situation worsens when you have a really famous blog with 100s of comments written each day. So what we have in hand is a situation where spam comments were approved previously and now you wish to remove those spam comments. Removing spam comments from your blog is also necessary because it directly affects the ranking of your blog. You could even be penalized by Google penguin penalty for spam links.
One of the solution to this problem is to manually check each comment. For obvious reasons if you have a famous blog on which people regularly comment, this approach is out of question. On the other hand, if you just started with your blog and have limited number of approved comments on your blog, manually checking all the comments could be a viable option.
Another solution to this problem is to run some simple SQL commands with the help of PHPMyAdmin user interface. Note: Always remember to take backup of WordPress database before running any SQL commands. An incorrect SQL command can cause irreversible damage to the whole blog. So here are the steps to be followed. For this you can use one of many excellent plugins like “WP Database backup”.
Step 1: Clear all pending comments: If there are any comments pending for you approval, clear them. Approve the genuine comments and delete the spam comments. You can use Akismat for removing the spam comments as well.
Step 2: In this step we are going to move all the already approved comments to moderation queue. We do this so that we can re-scan all the comments again. To do this, run the following SQL command.
Update wp_comments set comment_approved =’0’ where comment_approved=’1’
Step 3: Once the previous step is completed, all comments on your blog will now come under moderation. Now we are going to check them for spam using Akismet. Use “Check comment for spam” to recheck all the comments on your blog. Depending upon the number of comments, this step might take time. So be patient.
Step 4: Once the previous step is completed, we are going to approve all the comments which are still present in the moderation queue. We can confidently go this because all the spam comments must have been removed by Akismat already. So to do this we are going to again execute an SQL statement which will be reverse of the previous SQL statement.
Update up_comments set comment_approved=’1’ where comment_approved=’0’.
Once this last step gets completed, your blog will be free from any spam in already approved comments as well.
One of the problems users sometimes face after doing the above steps is incorrect count of the comments. As this is a common problem, so a common solution in the form of another plugin is readily available. The plugin is “Web Ninja Comment Count Fixer”. It allows you to recheck the comment count in the database and updates it accordingly.
So if you need to filter out the spam comments which have already made their way into your blog somehow, this article should have helped you to get rid of them. Also it is worthwhile to again mention not the forget taking backup of your WordPress database before executing any SQL command in this tutorial.