Recently I was working on a client WordPress site which was migrated from BlogSpot to WordPress, and after migration she didn’t configure Akismet plugin and neither configured WordPress discussion settings to prevent comment spam. This resulted in numerous spam comments getting approved and were live on her blog. Eventually, this resulted her blog to be penalised by Google penguin penalty for spam links. One easy way to get rid of those links by removing website link from comment form using this plugin, but this was might have turned out to be a negative solution for her loyal readers. Also, manually re checking all approved comments was not a feasible solution. So here is the workaround which I did to ensure her blog approved comments get checked by Akismet plugin, and her blog is spam free. This method requires you to run SQL command, and I suggest you to do it when you are sure of what you are doing. This tutorial guides you in step by step method to recheck all approved WordPress comments for spam comments.
How to find spam comments from Approved WordPress comments:
This method totally relies on effectiveness of Akismet plugin and it is quite effective to remove previously approved spam comments. You should defiantly take backup of your WordPress database before running any SQL command mentioned in this article. You can run the SQL command from PHPMyAdmin or simply use DB-manager WordPress plugin to run SQL command from dashboard.
Now, follow the steps mentioned below and re-check all your blog comments for spam:
- Moderate all pending comments
- Remove all pending spam comments
- Empty your trash comments.
- Take a backup of your WordPress blog database
Once you have done above mentioned steps, now we will move all approved comment to moderation queue and will use Akismet check for spam method to recheck for spam. If you have a huge number of comments and your hosting is underpowered, you might face issue with “error connecting to database”. In such case, simply hit the back button and re-check your blog for spam comments. If your hosting is underpowered, I suggest you to move to a more renowned hosting companies like Bluehost, WPEngine which are optimised for WordPress blogs.
To move all approved comments to moderation queue, we will use this SQL command:
update wp_comments set comment_approved=’0′ where comment_approved=’1′
Once you have successfully run the above command, all your comments will be unapproved, and will held in moderation queue. Now, we will use “Check comment for spam” to re-check all blog comments for possible spam. Depending upon the number of comments on your blog, it might take some time.
Once Akismet is checking all the comments for spam, we will simply run another SQL comment which will move all approve all comments which are in moderation queue. Here is the SQL command which we will run to move all comments in moderate queue to approved queue.
update wp_comments set comment_approved=’1′ where comment_approved=’0′;
Using the above method, I was able to get rid of over 1000 spam comments; which is a lot if you consider that even a single link can penalise your blog for having bad links. Apart from the above method, I also removed all trackbacks and pingbacks to further clean up the blog for useless comments and links. Here are two articles which will be helpful for you to further cleanup your blog comment section:
- How to delete WordPress Trackback in bulk using SQL Query
- How to delete all comments from a specific WordPress post