joey nelson blog title

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