11

how can I delete all comments at once? I have seen how to do it with nodes and bulkdelete module, Delete all nodes of a given content type but am looking at a similar solution for comments.

I am looking for a solution that does not leave any artifacts in the database. Is Views Bulk Operations the best solution?

Thanks

Edit This worked for me:

<?php
$results = db_select('comment', 'c')->fields('c', array('cid'))
                                    ->execute()->fetchAll();
$i = 0;
$max = 1000; 
foreach ($results as $result) {
    $cids[] = $result->cid;
    ++$i;
    if($i  > $max){ break; }
}
var_dump(count($cids));
comment_delete_multiple($cids);
?>
7

AFAIK, VBO should be able to do the job, but I haven't tried it myself to delete comments.

Another way is to run some code (in a module or in Devel module's 'execute php' block) that gets a list of all queries from the db, builds an array of the comment IDs and passes that array to the comment_delete_multiple function. Note that this can take a while to complete (depending on server performance and the number of comments), so you may have work your way around that, for instance by using set_time_limit (http://php.net/manual/en/function.set-time-limit.php) or Drupal's batch API.

[update: see Chris Cohen's answer for example code based on this approach.]

7

This is not an alternative answer, more an elaboration on marcvangend's, but I couldn't comment on his answer and leave example code. So the manual approach, using devel's execute code block, would look a bit like:


$cids = db_select('comment', 'c')
  ->fields('c', array('cid'))
  ->execute()
  ->fetchCol();

comment_delete_multiple($cids);

As marcvangend points out, this is going to be limited to the execution time on your server, so you will need to temporarily raise it if you have a lot of comments. Thanks to manarth and instanceofjamie for the dbtng assistance.

  • 1
    Thanks for the direction, but your syntax is off. The table is comment, you need an alias and the raw object results can not be fed directly into comment_delte_multiple – brian_d Mar 15 '11 at 18:06
  • @brian_d how would you process the raw object results ready for comment_delte_multiple()? – jackocnr Dec 7 '12 at 17:24
2

Personally, I'd go for the Views Bulk Operations module.

This module augments Views by allowing bulk operations to be executed on the displayed rows. It does so by showing a checkbox in front of each node, and adding a select box containing operations that can be applied. Drupal Core or Rules actions can be used.

0

Honestly when I needed to do this I just did it in the database. You delete the comments, fix the comment statistics and poof, all the comments are gone. I'd avoid doing this if you have modules interacting with the comments in some odd fashion, otherwise its what I'd suggest.

TRUNCATE TABLE comments

UPDATE node_comment_statistics SET comment_count = 0

  • I think the advantage of using the drupal API call comment_delete_multiple is that it can call additional drupal hooks for you, leaving your DB potentially cleaner. – brian_d Mar 15 '11 at 18:36
  • Thats why I said I'd suggest avoiding doing so if you have modules interacting with the comments. Otherwise if you have stock comments, I've never noted any issues with it. Its certainly a lot easier to do if you have a site that has gotten spammed with thousands of comments. – G.Martin Mar 15 '11 at 22:55
  • You need to truncate the table 'field_data_comment_body' as well, since that's where the comments content is stored. – Creynders Jan 24 '13 at 8:53
0

Some UI Tools You may install the modules below

Views - drupal.org/project/views

Every Drupal site needs, I believe... It generates SQL at the backend and displays results with configurable settings, filters, sorting, paging...etc

VBO - http://drupal.org/project/views_bulk_operations To allow bulk operations (i.e. delete comments for this thread)

Administration Views - http://drupal.org/project/admin_views Take advantages of Views and VBO, replace original contnet, comment, user admin pages by menu_alter...

  1. After enabling above modules, go back to comment admin page
  2. Tick select all (be careful, ensure you would like to delete all comments...)
  3. Select "Delete" and "Submit" (be careful, no more confirm button later...)
0

Achieved deleting about 45,000 comments by using the Backup and Migrate module. In the advanced set up in 'Exclude the data from the following tables' - first press Ctrl key then select comment in the list - (be careful because here the unnecessary cache tables are already selected) - backup - restore from this backed up file. Hurrah!

0

This answer is similar to an already listed answer but I modified it to prevent an 'out of memory error' for about 27,000 comments. This will take a while to execute depending on the number of comments. Simply truncating the comments table may not be a good idea; it's best to let Drupal handle the deleting of content.

I created a PHP script:

$cids = db_select('comment', 'c')
  ->fields('c', array('cid'))
  ->execute()
  ->fetchCol();

foreach($cids as $cid)
{
  comment_delete($cid);
}

...then ran the script with Drush

drush @my_alias php-script my_script.php
0

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
-2

Enable the PHP filter, and create a basic page with the following code:

<?php
  db_query("TRUNCATE TABLE {comment}");
  db_query("UPDATE {node_comment_statistics} SET comment_count = 0");
?>
  • 1
    never use PHP filter for things like that (even for any other thing, PHP filter is generally one of the worst ideas in Drupal) – Alejandro Moreno Jan 22 '14 at 14:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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