Deleting Self Pingbacks
So, I’m talking to [Emmy] the other night, and she says that she doesn’t like so many of those “automated” comments on my posts, because they make it harder to find real comments (from other humans).
I looked into the matter and found that I had 1060 of these pingbacks, strewn throughout my collection of posts for this, the Tom’s Diary blog. Upon examination, I discovered that all of those were pingbacks from this blog, to this blog; thus the term: self pingback.
In short, a pingback is a special comment attached to a particular post, which is generated by WordPress whenever another blog (or another post in thisblog) links to the subject post. Upon reviewing the WordPress documentation and goggling around for “independent” explanations of pingbacks, I determined that these self pingbacks probably aren’t that useful; especially since most of them referenced my daily revisions posts, which give little more information than the names of revised documents that day. Therefore, I figure that I don’t need these, because when I write a post, I always add links to other relevant posts to it, and so, need not rely on any self pingbacks in the comments beneath to provide those links.
Subsequently, I deleted all self pingbacks from this blog. This involved a couple steps; the first of which was to run an SQL script on the wp_comments table in the blog’s database. That script was as follows:
DELETE FROM `wp_comments`WHERE `comment_author` LIKE "%Diary%"
Note that you’d have to modify this statement for your particular blog’s name. Since this blog is called Tom’s Diary and since I knew that nobody left any non self pingbacks with the string “Diary” in the author’s name, I surmised that this would be safe. It deleted 1060 comment records.
Well, immediately after this, I thought I was done, but soon realized that the comment counts displayed beneath each post on the blog had become incorrect. The self pingbacks had been deleted alright. But the count of comments in the affected posts had not been decremented to reflect this. I had assumed that the WordPress database would have triggers to automatically update the counts. But It appears not to.
So I wrote a non destructive SQL script, to get a count of the post records whose comment count field did not match the actual number of comments associated with that post in the wp_comments table.
SELECT count( * ) FROM `wp_posts`
WHERE `comment_count` <> (
SELECT COUNT( * )
FROM `wp_comments`
WHERE `comment_post_ID` = `wp_posts`.`ID` )
I verified that I got what appeared to be a reasonable count of disagreeing records.
Next, I rewrote the above SQL that, instead of just displaying a a count, actually update the counts in the offending records as follows;
UPDATE `wp_posts` SET `comment_count` =
(SELECT COUNT( * )
FROM `wp_comments`
WHERE `comment_post_ID` = `wp_posts`.`ID` )
What this does is that, for each post, it counts the number of comments that list that post in their comment_post_ID field, and assigns that value to the comment_count field.
This seems to have done the trick, as the ten or fifteen posts I examined that had comments noe display the correct number below, and there appear to be no missing comments either.
Thanks for the tip, [Emmy].
I’ll need to run the above SQL against the databases for my other blogs as well, and will note in the comments below when I finish that.
Of course, there’s still the problem of preventing the self pingbacks from being written to the database in the first place. I read of some plugins for WordPress that may solve this problem. So I’ll look into them at some point and install them.
Take care.