cleaning up a lot of comment spam in drupal

Sadly, sometimes there is a bunch of comment spam on a drupal site. It's easy enough to set up an approval queue to make the site look bad, but that still leaves one with having to clean up the unapproved comments.

There are a lot of suggestions out there that suggest simply doing a SQL delete:

    DELETE from COMMENT where STATUS='0';

That will indeed get rid of the comments, but the nodes that have the comment attached will still show incorrect stats on the number of comments.

After doing a lot of searching, I cobbled my solution together and run it via drush scr

First, use the following snippet and put it in a file on the server. I suggest clean-comments.php

    $query = db_select('comment', 'c')
      ->condition('status', 0, '=');
    $result = $query->execute();
    while ( $record = $result->fetchAssoc() ) {
      print $record['cid'] . " -> " . $record['nid'] . "\n";

now you can run that script with

    drush scr clean-comments.php

You probably should not put this into the html directory but outside of it. That means you'll have to either add the path to the script name or pass -r <path to drupal install> to drush.

I think the same code can be added in a special page with full PHP enabled, but you probably don't want to leave that unprotected.

I hope this helps someone.