How to bulk delete all unapproved comments in Drupal?

Sun, 06/08/2014 - 21:10 -- Sam

Do you have the problem of thousands of spam comments building up on your Drupal site? Ok, the best solution is to check it regularly and delete the unwanted comments, and just leaving the appropriate ones. However, what if you forget to look at your comments for a few days, if you go on holiday for a week and don’t check your blog or your site has been attacked? You can delete these comments manually but this is time consuming (Drupal admin interface only shows you a list of 25 items). This method will allow you to delete all the unapproved comments and leaves the approved ones intact.

1. You can use Views and Views Bulk Operations to delete all of them.

2. Delete them directly in phpMyAdmin using the SQL query (thanks to Philip Norton for the SQL queries):

DELETE FROM comment WHERE status = 0

Next, the comment body fields in the field_data_comment_body table also need to be removed. This was done by also cross referencing any missing comments from the comment table.

DELETE field_data_comment_body FROM field_data_comment_body
LEFT JOIN comment ON field_data_comment_body.entity_id = comment.cid
WHERE comment.cid IS NULL;

The comments also have revisions, which need to be deleted in the same way.

DELETE field_revision_comment_body FROM field_revision_comment_body
LEFT JOIN comment ON field_revision_comment_body.entity_id = comment.cid
WHERE comment.cid IS NULL;

and you're done!

Happy Drupalling! :)

Comments

Alan Peterson's picture

Yes that's right sometime when we are on holiday can not see what is happening. It could be very bad for the site. We can not delete it at once we have to check them manually.

Rosetta's picture

What's Happening i am new to this, I stumbled upon this I've discovered It absolutely useful and it has
helped me out loads. I'm hoping to contribute & help different users like its aided me.
Great job.

Add new comment

Tweets

@csscutie on

Azul