How to delete or remove spam, trashed or approved comments/trackbacks in bulk from wordpress database?

Do you want to remove thousands of spam comments and trackbacks in your wordpress blog or website ? When a blog has more than 1000 of comments as spam or trash or approved by mistake, it becomes very tough task to delete them from wordpress admin panel.

In this case you need to delete it from database. Its not a tough task and will take no more than 5 minutes. While in admin panel it may take hours! Also see how to remove comment meta in wordpress database?

Steps to delete comments in bulk:

First you need to log in to phpmyadmin. Those who don’t know how to login to phpmyadmin ask your hosting provider as every hosting is different. Although mostly you need to login to your hosting and find link to database or phpmyadmin.

It may ask for database username and password. For username and password to database you need to open wp-config.php . Copy username and password and log in.

Now you will see a list of tables on right side in phpmyadmin. If you don’t see it click on database name with “+” prepend to it. This will collapse the list of tables. Now select wp_comments.

In next step select “sql” tab from top. You will see a text area with heading “Run SQL query/queries on database your_database_name:“. This is where we write our MySQL queries to delete comments.

Now query for deleting spam, approved, pending and trash changes a bit for each. It is explained for each one below:

  1. To delete pending comments use query: delete from wp_comments where comment_approved = ‘0’    (  here ‘0’ = Zero )
  2. To delete spam comments use query: delete from wp_comments where comment_approved = ‘spam’
  3. To delete trash comments use query: delete from wp_comments where comment_approved = ‘trash’
  4. To delete approved comments use query: delete from wp_comments where comment_approved = ‘1’

This query is then executed by clicking go button.

So how this MySql query, to delete wordpress comments in bulk, works?

In wordpress comments data is stored in a table wp_comments and it has a column comment_approved this has values for each comment i.e

  • ‘spam’ for spam comments.
  • ‘1’ for approved comments.
  • ‘0’ for pending comments.
  • ‘trash’ for trash comments.

So we use my sql queries to select row in table with particular value for comment_approved column and apply delete operation on it.

 

Apr,24,2014

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: