September 24, 2006

So I’ve been doing some work on the long-neglected Fontleech and today found myself removing over .25 million spam comments. That sounds herculean, but it was actually pretty easy.

The WordPress frontend pulls the comment count for each post from the ‘comment_count’ field in the wp_posts table. obviously that doesn’t get updated when you manually delete spam directly from the database, so here’s a simple ruby script that will update that for you (just define your db variables):

#!/usr/bin/ruby
require 'mysql'
 
db = Mysql.real_connect(host, user, pass, dbname)
ids = db.query("SELECT `ID` FROM `wp_posts` WHERE 1")
 
ids.each_hash do |post|
  id = post['ID']
  num = 0
  comments = db.query("SELECT COUNT(1) FROM `wp_comments` WHERE `comment_post_ID`='#{id}' AND `comment_approved`='1';")
  comments.each {|x| num = x[0]}
  up = db.query("UPDATE `wp_posts` SET `comment_count`='#{num}' WHERE `ID`='#{id}';")
end

1 Response

  1. 1 Sergio

    Hey Joey, I have this problem too but I dont idea how to make this working.

    Would you mind telling me how I execute the script?

Leave a Reply