Mass Deleting Spam Comments from WordPress Blog

So you have a wordpress blog which has hundreds or even thousands of spam comments? You don’t wish to install plugins like Akismet as it requires you to register with wordpress and get an ID for your site? No problem. I have faced a similar problem and came up with a solution to this problem.

What are spam comments?

For me, they are the ones who are not related to my article. They consists lots of hyperlinks to stupid bag sites or some medicine blogs. They can be any language, English, Chinese etc. The comment that looks genuine but can be used on any article, something like “Nice article”, “Good article”, “Lovely post”, “I feel enlightened”.

How to distinguish a spam comment?

Most of the spam comments are easily identified as they contain lots of links and the content is totally irrelevant to your website or article, but something the comment looks genuine yet it is a spam. Check out for the author website link, author email and the comment content. If the email contains a (stupid) brand name or it is hosted by a free email providers which you are not commonly used then it MIGHT be a spam comment. Then check for the author website, if it points to website that is totally irrelevant to your article then it can be a Spam comment. Last but not the least, the comment content, if the comment is something like a universal comment which can be posted on almost any article then it might be a spam comment.

Why people post spam comments?

It’s quite simple, when you approve the comment, it gets published on your website. There is a huge possibility that your template displays the author name and author website on the article page. Spammers use this loophole to insert their brand names (site names) upon your page. They also get a backlink to their website from your website. Not only that but some of your visitors might just click the link and visit their page, this brings traffic to their website. This is definitely bad for your website as most likely the hyperlinks will be irrelevant and so will be the comment and author name. This decreases your article’s value from search engine optimization’s perspective. As the content size on the page increases and the keyword density decreases. Moreover the link juice (another SEO jargon) is transferred from your website to an irrelevant website, thus decreasing your webpage’s value.

How to delete SPAM comments in WordPress?

WordPress, by default, uses the table wp_comments to store comments. Now, to mass delete thousands of comments from your wordpress blog, you will require accesss to your database and must be able to fire SQL delete queries on your wordpress tables. If you are using MySQL database, you must be most likely having access to PHPMyAdmin. Open PHPMyAdmin and select your database. Go to SQL and Run the respective query to delete the comments. Let us take various cases and the queries used to delete selective comments.

Note : I am filtering comments which are not yet approved, hence I will be using the filter comment_approved = 0 to select only those comments which are not yet approved. If you wish to filter approved comments, use the filter comment_approved = 1 in the WHERE clause. If you wish to filter all the comments, just remove comment_approved = 0 AND  part from the query.

Case 1 : Deleting comments from authors who have a website and whose comment content contains hyperlinks

DELETE FROM wp_comments WHERE comment_approved = 0 AND comment_author_url LIKE '%http://%' AND comment_content LIKE '%http://%';

Case 2 : Deleting comments from authors who have provided a hyperlink to their website

DELETE FROM wp_comments WHERE comment_approved = 0 AND comment_author_url != '';

OR

You can even check if the hyperlink begins with http://

DELETE FROM wp_comments WHERE comment_approved = 0 AND comment_author_url LIKE '%http://%';

Case 3 : Deleting comments whose contents contain hyperlinks

DELETE FROM wp_comments WHERE comment_approved = 0 AND comment_content LIKE '%http://%';

Case 4 : Deleting all unapproved comments

DELETE FROM wp_comments WHERE comment_approved = 0;

Case 5 : Deleting all approved comments

DELETE FROM wp_comments WHERE comment_approved = 1;

Case 6 : Deleting all the comments from your wordpress blog

DELETE FROM wp_comments;

You can take the sample cases and customize your queries accordingly to filter and delete comments, which you find as spam, from wordpress blog.  Hope this article was helpful. If you have any doubts or have any more advanced queries or ideas on how the filtering process can be improved, please drop a comment using the form below.