By ahoeben on

One of my sites recently got a heavy dose of comment spam from an anonymous user who found his way around spam.module. Since deleting comments one by one was way too much hassle, I ended up deleting the comments through a simple MySQL call, directly to the database, screwing up the comment counts etc in the node_comment_statistics table.

I had a look around drupal.org but could not find info on manually rebuilding the node_comment_statistics table, untill I found some neat MySQL calls in the wp2drupal script on ninjafish.org.

Here's a working excerpt I used, and I hope someday it saves someone's node_comment_statistics. To run this script, I created a new page with a user with PHP input permissions. I did not actually post this page to the site, but used 'preview' to execute the PHP statements.

Be sure to back up your database before executing the PHP below!

db_query("DELETE FROM node_comment_statistics");

$nodes = db_query("SELECT DISTINCT nid,max(cid) FROM comments GROUP BY nid");
while ($m = db_fetch_object($nodes)) {
  $nid = $m->nid;
  $cid = $m->{"max(cid)"};

  $lastcomments = db_query("SELECT uid,name,timestamp FROM comments WHERE cid=$cid");
  $n = db_fetch_object($lastcomments);
  $uid = $n->uid;
  $name = $n->name;
  $timestamp = $n->timestamp;
  
  $counts = db_query("SELECT count(*) FROM comments WHERE status=0 AND nid=$nid");
  $n = db_fetch_object($counts);
  $count = $n->{"count(*)"};

  db_query("INSERT INTO node_comment_statistics (nid,last_comment_timestamp,last_comment_name,last_comment_uid,comment_count) VALUES ('$nid','$timestamp','$name','$uid','$count')");
}
⋅ Categories: Drupal 4.6.x, Drupal 4.7.x

Comments

Marc Bijl’s picture

Marc Bijl commented

Hi,

This post is exactly what I need, thanks!

However, I do not fully understand all the things that are related to comments.

I'm building a new site right now. Therefore, I do a lot of testing - with comments as well. Before the site will be launched, I want to delete all comments, so I can start with a "clean database". What I thought to do was: empty comment table and reset comment id in sequence table.

Now I see there's a node_comment_statistics table as well.

Can anyone explain what is the best way to clear anything that is related to comments?

Thanks!
Marc

___________________
discover new oceans
lose sight of the shore

kus’s picture

kus commented

you saved my node_comment_statistics. thanks.

martas’s picture

martas commented

Thanks!!
Here is the same code with a few improvements.
(Just added {} around table names - useful if using table prefix and some SQL "function(arg) as result".)

db_query("DELETE FROM {node_comment_statistics}");

$nodes = db_query("SELECT DISTINCT nid,max(cid) as max FROM {comments} GROUP BY nid");
while ($m = db_fetch_object($nodes)) {
  $nid = $m->nid;
  $cid = $m->max;

  $lastcomments = db_query("SELECT uid,name,timestamp FROM {comments} WHERE cid=$cid");
  $n = db_fetch_object($lastcomments);
  $uid = $n->uid;
  $name = $n->name;
  $timestamp = $n->timestamp;
 
  $counts = db_query("SELECT count(*) as count FROM {comments} WHERE status=0 AND nid=$nid");
  $n = db_fetch_object($counts);
  $count = $n->count;

  db_query("INSERT INTO {node_comment_statistics} (nid,last_comment_timestamp,last_comment_name,last_comment_uid,comment_count) VALUES ('$nid','$timestamp','$name','$uid','$count')");
}
ahoeben’s picture

ahoeben commented

Here's an updated snippet that includes nodes with 0 comments. Nodes without comments would not show up on the tracker at all after the fix.

db_query("DELETE FROM {node_comment_statistics}");

$nodes = db_query("SELECT nid,uid,changed FROM {node} WHERE status=1");
while ($m = db_fetch_object($nodes)) {
  $nid = $m->nid;
  
  $comments=db_query("SELECT nid,max(cid) FROM {comments} WHERE status=0 AND nid=$nid GROUP by nid");
  if($l = db_fetch_object($comments)) {
    $cid = $l->{"max(cid)"};

    $lastcomments = db_query("SELECT uid,name,timestamp FROM {comments} WHERE cid=$cid");
    $n = db_fetch_object($lastcomments);
    $uid = $n->uid;
    $name = $n->name;
    $timestamp = $n->timestamp;
  
    $counts = db_query("SELECT count(*) FROM {comments} WHERE status=0 AND nid=$nid");
    $n = db_fetch_object($counts);
    $count = $n->{"count(*)"};
  } else {
    $timestamp = $m->changed;
    $name = NULL;
    $uid = $m->uid;
    $count = 0;
  }
  db_query("INSERT INTO {node_comment_statistics} (nid,last_comment_timestamp,last_comment_name,last_comment_uid,comment_count) VALUES ('$nid','$timestamp','$name','$uid','$count')");
}
NiCoS-1’s picture

NiCoS-1 commented

Great - it works well !

Thanks to both of you !! :-)

joshk’s picture

joshk commented

This is great stuff. I'm going to get it into the handbook.

------
Personal: Outlandish Josh
Professional: Chapter Three

------
Personal: Outlandish Josh
Professional: Pantheon

epiphanymarketing’s picture

epiphanymarketing commented

This helped me fix a botched import of forum topics where the node_comment_statistics table had been ignored...

Anonymous’s picture

Anonymous (not verified) commented

Thank you very much for this -- I have no idea why my comment count got screwed up, but this fixed it. Great stuff.

There is a slight bug: $name should be escaped, because it might contain a ' character (or something else problematic). I did have some commenters whose names contained a ' character.

There's a potential SQL injection vulnerability, but more likely it just won't work... as in my case.

marcomatic’s picture

marcomatic commented

Thank you for creating this. After a recent migration where we manually moved tables from d5 to d6 this saved us.

sphism’s picture

sphism commented

Hello, i'm having an issue with the flag module, certain node types show up in the block and other node types don't - even though they are flagged in the database.

I think i've narrowed it down to there being an INNER JOIN to the node_comment_statistics table within the View query.

The nodes that work seem to have entries in the node_comment_statistics table, the ones that don't work don't.

So rebuilding that table seems to be what i need to do, any idea how to edit that code for drupal 7?

i'll post back if i get it to work.

thanks,

matt

sphism’s picture

sphism commented

Ok so that was the problem, and i managed to fix it using Devel module.

Enable Devel module
Add the Development block somewhere so you can see it (eg in the footer)
Click 'Execute PHP code'
enter this into the php code box:

devel_rebuild_node_comment_statistics();

Run the code, and all node comment stats are fixed.

Matt

geraldito’s picture

geraldito commented

Thanks, that worked out for Drupal 7.