I have literally thousands of unpublished comments. I am wondering what is the easiest way to git rid of them altogether.

Thanks

  • 1
    WARNING: before going with the accepted answer check out the comments and other answers – User Oct 11 '16 at 12:10
up vote 0 down vote accepted

For Drupal 6, run the following PHP code:

db_query('DELETE FROM {comments} WHERE status = 1');

For Drupal 7, run the following PHP code:

db_query('DELETE FROM {comment} WHERE status = 0');

You can also execute the query using Drush's sql-query command, for example

drush sql-query "DELETE FROM {comment} WHERE status = 0"
  • How to execute the query using drush? – alfish Aug 24 '11 at 12:40
  • @alfish I edited my answer to explain how it works in Drush. – Oswald Aug 24 '11 at 12:45
  • 1
    There are possibly other tables to check. node_comment_statistics for example contains data related to comments although they could be just for published comments. I would attempt to use views bulk operations and create a view that selects all the unpublished comments. – Stewart Robinson Aug 29 '11 at 17:20
  • 2
    Sorry, voting this one down, since as Stewart Robinson says here, just deleting stuff from {comments} is not enough. {field_data_comment_body} and {field_revision_comment_body} are going to be needed, and there will be lots of other places where comment data is stored. – Chris Cohen May 1 '13 at 9:30
  • 1
    Can't vote down (not sufficient reputation), but I totally agree with @ChrisCohen here - as deleting records just from {comment} table does not delete the appropriate's field tables content, hence database inconsistency and orphan data. – petiar Oct 8 '14 at 14:54

The better way I think is to use Views and Views Bulk Operations and you can do a simple admin interface to delete all of them.

Otherwise, you still can delete directly throw a SQL query.

  • 1
    Can you please elaborate on how to make such Views? – alfish Aug 24 '11 at 12:40
  • You install Views and Views Bulk Operations, after it you add a new Views base not on nodes but on comments and on the Style you will able to choose Views Bulk Operations. Finally, the next steps you'll able to understand easily how it's works. The main part, it's that this both modules will create a kind of admin page to allow you apply different actions on all selected comments. – yvan Aug 24 '11 at 12:50
  • Ok here is the complete guide drupal.org/node/350629#comment-4659500, thanks Charles: Install the views bulk operations module, create a page view of comments, and choose "Bulk Operations" as the view style. Make sure you select "Delete comment" as one of the selected operations. When you view the view page you created, you'll be able to bulk delete comments. – alfish Jul 25 '12 at 23:43

For those using Drupal 7, using VBO has changed and the above comments about choosing Bulk Operations as the view style is not correct. Here are the steps to create a VBO page with Drupal 7 (I'll use the example of deleting all comments)

  • Create a view where you show Comments and create a page
  • You'll need to format it in a way that allows you to add fields (table is a handy format)
  • Add a Bulk operations: Comment field and for the settings for this field, under SELECTED BULK OPERATIONS choose Delete item (you can opt to skip the confirmation step here)
  • If you have a lot of comments, under Pager for this view display something like 200 at a time (it will be slow but you won't have to repeat your deletes lots of times)
  • Save your view and then go to the page you created. You should be able to select all the items and then choose Delete under operations. You can then confirm the delete and repeat as necessary.
  • 1
    For me I couldn't get the "Select All" html checkbox to appear on the view unless I did a ctrl-F5 to refresh the view – User Oct 11 '16 at 12:06

you can review this steps

  • Install the views bulk operations module,
  • create a page view of comments, and choose "Bulk Operations" as the view style.
  • Make sure you select "Delete comment" as one of the selected operations.
  • When you view the view page you created, you'll be able to bulk delete comments.

Regards

For Drupal 7, this is really easy to achieve.

  1. Navigate to Content > Comments > Unapproved comments.
  2. Select the upper left radio button to select all rows in the table.
  3. Select "Delete the selected comments" in the drop down list at the top.
  4. Hit the 'Update' button.

I would usually recommend to use VBO to delete comments or nodes in bulk but if you are in a situation when you have hundreds of thousands of comments and you don't have too much time, here is a sql query which will delete all unapproved comments along with all the revisions and data related to those comments which in my case was taking 1.2Gb of space in DB

DELETE c, rcb, dcb
FROM
    comment AS c
JOIN field_revision_comment_body AS rcb ON (c.cid = rcb.entity_id)
JOIN field_data_comment_body AS dcb ON (rcb.entity_id = dcb.entity_id)
WHERE
    c. STATUS = 0

Actually this worked for me:

drush sql-query "DELETE FROM comment WHERE status = 0"

i.e. no curly braces required.

However as explained elsewhere there are other tables in the db that reference comments so this approach is not advisable.

maybe there needs to be a module to do this properly, including field_data_comment_body, field_revision_comment_body etc Would be useful if you have a gazillion unpublished comments.

In the meantime this post here shows a better way:

DELETE FROM field_data_comment_body USING field_data_comment_body JOIN comment ON comment.cid=field_data_comment_body.entity_id AND comment.status=0;

DELETE FROM field_revision_comment_body USING field_revision_comment_body JOIN comment ON comment.cid=field_revision_comment_body.entity_id AND comment.status=0;

DELETE FROM comment WHERE status=0;

https://www.drupal.org/forum/general/general-discussion/2010-05-16/mass-delete-spam-comments#comment-10964889

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.