[How-To] Delete WordPress Spam Comments Using phpMyAdmin

If your blog runs on WordPress and although you do have Akismet plugin for spam protection installed, you still might be interested in knowing what goes under the hood when you hit Delete all Spam button, this article will introduce you the SQL Queries involved in getting the total number of Spam comments in your blog and how to delete the spam comments using phpMyAdmin. Essentially, the blog post assumes that you use phpMyAdmin, if not, the SQL queries can be entered through the database management software or console line.

In WordPress, table wp_comments holds the details related all the comments on your blog. It has specific columns related to the information for the comments. One of the most important column is the comment_approved which holds the status of each comment.  The column can take one of the three possible values for any comment. The possible values are 1, 0 and spam.

You can also get the possible values by running the below mentioned query against the database.

select distinct(comment_approved) from wp_comments;
+------------------+
| comment_approved |
+------------------+
| 0                |
| 1                |
| spam             |
+------------------+
3 rows in set (0.00 sec)

Below is the legend for the various values that can be present in the comment_approved column of the wp_comments table.

0 = Comment Awaiting Moderation.
1 = Approved Comment
spam = Comment marked as Spam.

Now, lets type in a query which fetches us all the comments and groups them in specific categories.

select count(comment_approved), comment_approved from wp_comments group by comment_approved ;
+-------------------------+------------------+
| count(comment_approved) | comment_approved |
+-------------------------+------------------+
|                       1 | 0                |
|                  179591 | 1                |
|                     452 | spam             |
+-------------------------+------------------+
3 rows in set (0.41 sec)

The query above tells us that we have 452 comments marked as Spam. Now, in order to delete these comments, we need another query, which targets the spam comments and deletes them from the wp_comments table.

delete from wp_comments where comment_approved="spam";

452 rows in set (0.0843 sec)

The above mentioned SQL query, deletes all the comments from the wp_comments table which have a value of “spam” in comments_approved column.

Now, in order to delete the comments using phpMyAdmin, follow the steps below

1.) Login to your phpMyAdmin application.

2.) Select the database specific to your WordPress Blog.

3.) Click on the tab which says “SQL”.

4.) In the input text area, you can enter any SQL statement and analyze the output. If you wish to delete the spam comments from your blog, enter the appropriate SQL mentioned earlier in the post.

If you are someone who is fed up of the amount of Spam comments that you get on your blog, i would recommend that you download and install WP-Ban plugin on your WordPress blog. WP-Ban allows you to ban users by IP, IP Range, host name and referer url from visiting your WordPress’s blog. It will display a custom ban message when the banned IP, IP range, host name or referer url trys to visit you blog. You can also exclude certain IPs from being banned. There will be statistics recordered on how many times they attemp to visit your blog. It allows wildcard matching too. You can read more about this plugin in my previous post titled [Wordpress] How do you take care of Spam?

You can follow me on Twitter at http://twitter.com/vaibhav1981

Do stay tuned to Technofriends for more, one of the best ways of doing so is by subscribing to our feeds. You can subscribe to Technofriends feed by clicking here.

Follow me on Twitter at @vaibhav1981 Tagged with: blog, Columns, Console Table, Database Management Software, Delete, Delete Spam, Groups, Moderation, Phpmyadmin, Select Count, Spam comments, Spam Protection, Sql Queries, Understanding Wordpress comments