Social Network for Web Professionals

Webmaster Blog

Members blog at WebmaisterPro. Covering topics related to online marketing, SEO, web development as well as software reviews.

Advanced WordPress Comment Management Through SQL

Without a doubt WordPress is the most popular PHP based CMS. Additionally WordPress offer the most popular commenting system, however due to popularity, commenting system is abused by spammers. It is not a secret that WordPress based websites and blogs leave a lot of footprints, that are used by spammers. Actually some of these footprints are integrated into some of the black hat spam applications like ScrapeBox or simply can be used with Google as advanced search operators.

Delete All CommentsThere are number of anti-spam extensions that can reduce levels of spam like Akismet, GASP, integrating captcha images, etc... However bulk deleting or deleting comments from particular name, date, URL, etc can be very difficult and I don't think that there is plug-in that can do this job.

In this article, I will review some advanced techniques that can assist you with some time consuming tasks – deleting comments directly from database.

IMPORTANT!

  • Before you try any of these, please back-up your database, in case something go wrong!

  • Your database may use different prefix, in our example we are using the default “wp_”, replace the prefix to match your database prefix.

Delete All SPAM Comment

Yes, there is in-build function of WordPress to delete all SPAM comments, however if the number is very high, you will get an error. This is related to PHP memory error, which will lead to deletion stop when memory is exhausted.

With SQL query directly executed in phpMyAdmin, you can bypass this error. Here is the query:

DELETE from wp_comments WHERE comment_approved = 'spam' 

Delete Comments Between Two Dates

Troubles with anti-spam plug-in may lead to SPAM attach between two dates. This happen more often than you can think of. Here is what to do in this case:

DELETE FROM wp_comments
WHERE comment_date > '2014-01-18 01:10:04'
AND comment_date <= '2014-01-20 00:10:04'

Pending Comments Deletion

There are few possible scenarios when you would like to delete all pending comments:

  • Google algorithmic changes, when you don't want to accept new comments

  • You are on holiday for few days and don't have time to moderate comments and large amount is spam.

DELETE FROM wp_comments WHERE comment_approved = '0'

Older Posts – Disable Comments

Often SPAM-ers try to target older blog posts with high PR. It is recommended to close the comments for posts older than some date of your choice. In this example we will use December 1st 2013:

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2013-12-01' AND post_status = 'publish' 

All Posts - Disable Comments

It can be time consuming to disable comments on all posts, easier way to disable comments on all post is just to run this simple SQL query:

UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed' WHERE comment_status = 'open'

Delete Comments from Specific URL

Once you've spotted by SPAM-er most likely there will be number of comments from same specific URL. At WebmaisterPro for instance there were 2 companies in the past that were trying so hard, even trying to contact me to request to publish SPAM on our social network.

DELETE from wp_comments WHERE comment_author_url LIKE "%spammerURL%" ;

Hope this advanced comment and spam comment removal guide will be useful. It might looks too complicated to non-developers, but as a matter of fact, it is very easy and much faster way to deal with SPAM on WordPress.

Rate this blog entry:
1
Why American Shopping Malls Are Going the Way of t...
Secure Your Joomla Website with These Essential Ti...

Related Posts

Comments

 
No comments yet
Already Registered? Login Here
Guest
Thursday, 16 July 2020
If you'd like to register, please fill in the username and name fields.

Captcha Image