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*


fields('c')
->condition('status', 0, '=');

$result = $query->execute();
while ( $record = $result->fetchAssoc() ) {
print $record['cid'] . " -> " . $record['nid'] . "\\n";
comment_delete($record['cid']);
_comment_update_node_statistics($record['nid']);
}
?>

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 thepath to the script name or pass `-r ` 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.

\\@matthias

Leave a Reply

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