Delete Wordpress Spam comments Posted by Stranger on 7:24 AM Digg it Bookmark it Stumble it Email to friend

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”.

Image: How To Delete Wordpress Spam Comments Using phpMyAdmin.

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 yourblog, 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 yourWordpress 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 yourblog. It allows wildcard matching too.