WordPress Discussion Management: Enable or Disable Comments and Pingbacks via SQL
Continuing my quest to stop comment spam without using plugins, I have decided to disable comments on “old” posts. In my experience, over 90% of comment, trackback and pingback spam occurs on posts that have been online for over a month or so, just long enough to be indexed by the search engines and picked up by spammers. Especially for older posts that have managed to acquire a little page rank, the frequency of spam attempts is far greater than it is for fresher content. Throw dofollow comment status into the mix, and say “hello” to a hellish number of spam attempts on established pages.
Thus, my evolving anti-spam strategy now includes discussion management, which involves periodic closing of feedback on older posts. In this article, we will examine currently available methods of managing comments, and then proceed with a versatile toolbox of SQL queries for complete discussion management.
Currently available methods for discussion management
- Site-wide discussion moderation via the WordPress Admin
- WordPress includes an easy way to disable or enable discussion throughout your entire site. Simply login to your WP Admin, go to “
Options > Discussion”, and toggle the option to “Allow people to post comments on the article”. This setting applies only to future posts and may be overridden on a per-post basis via the “Discussion” options in the “Edit Posts” screen. This method is great for permanently or temporarily disabling future discussion on a sitewide basis. - Post-level discussion moderation via the WordPress Admin
- Regardless of which sitewide discussion option you choose, WordPress makes it possible to override the setting on a per-post basis. Simply open the “
Discussion” options in the “Edit Posts” screen, and toggle the comment, pingback and trackback options for that particular post. These settings take precedence over sitewide settings and may be changed at any time. This method is great for disabling or enabling comments and pingbacks on existing content, however would prove far too tedious to implement on any significant number of posts. Nonetheless, post-level discussion management provides authoritative, granular control for existing material. - Discussion moderation via plugin or other script
- Using one of the popular comment-disabling (404 link removed) or other incredibly useful discussion management plugins available, it is possible to disable comments, pingbacks and trackbacks on posts published after some specified time period. As such plugins or scripts modify the WordPress database, implemented comment settings override the sitewide discussion option described previously. Likewise, upon execution, these plugins will erase any specific post-level discussion settings that may have been established. Although individual post settings may be restored on an individual basis, they may be erased with the next automatic execution of the plugin. Further, plugins generally act sitewide on existing posts and may or may not include granular control over comments, pingbacks and trackbacks. Moreover, execution of these plugins is generally irreversible — once the discussion options have been closed, there is no included method of opening them again.
- Direct discussion moderation via the WordPress database
- Ultimately, all of the above methods act upon the WordPress database. Using a little SQL magic, it is possible to manipulate the MySQL database directly, thereby assuming complete control over your discussion management strategy. Interacting directly with the database is fast and flexible, eliminating the need for yet another resource-gobbling plugin to do the job. With a single SQL query, we can disable or enable comments, pingbacks or trackbacks for non-registered users or all users, and for all posts published before a specific date or for all posts. For those of us comfortable working directly with MySQL (via phpMyAdmin or some other method), continue reading for a comprehensive collection of simple, one-step SQL queries perfect for direct discussion management.
Disabling and enabling comments
In the WordPress database, the “wp_posts” table includes a column called “comment_status”, which may contain one of the following values for each row (i.e., post):
open(comments open to everyone)closed(comments closed to everyone)registered_only(comments open for registered/logged-in users)
Given this information, we may execute the following SQL queries (via phpMyAdmin or any other method of querying the database) to manipulate our discussion-management settings for comments (note: remember to backup your database):
Globally enable comments for all users
UPDATE wp_posts SET comment_status = 'open';Globally disable comments for all users
UPDATE wp_posts SET comment_status = 'closed';Globally enable comments for registered users only
UPDATE wp_posts SET comment_status = 'registered_only';Globally enable/disable comments before a certain date
For this query, specify the comment_status as either open, closed, or registered_only. Also, specify the date by editing the 2008-01-01 to suit your needs.
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2008-01-01' AND post_status = 'publish';This last query is one that I will be using a few times each year (or as often as I can remember it) to disable comments on old posts. Ultimately, I will combine this query with a similar one (provided below) for pingbacks and trackbacks to manage discussion options with a single step.
Disabling and enabling Trackbacks & Pingbacks
Similar as before, the “wp_posts” table also includes a column called “ping_status”, which applies to both pingbacks and trackbacks, and may contain one of the following values for each row (i.e., post):
open— pingbacks/trackbacks open to everyoneclosed— pingbacks/trackbacks closed to everyone
Given this information, we may execute the following SQL queries (via phpMyAdmin or any other method of querying the database) to manipulate our discussion-management settings for pingbacks and trackbacks (note: remember to backup your database):
Globally enable pingbacks/trackbacks for all users
UPDATE wp_posts SET ping_status = 'open';Globally disable pingbacks/trackbacks for all users
UPDATE wp_posts SET ping_status = 'closed';Globally enable/disable pingbacks/trackbacks before a certain date
For this query, specify the ping_status as either open or closed. Also, specify the date by editing the 2008-01-01 to suit your needs.
UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2008-01-01' AND post_status = 'publish';As before, this last query is one that I will be using a few times each year (or as often as I can remember it) to disable comments on old posts. Ultimately, I will combine this query with the comments query to produce the one-step discussion-management query provided below.
Complete, one-step discussion management
Given the queries described above, we may fashion the following “one-step” SQL queries, perfect for complete, plugin-free discussion management:
Globally enable/disable all discussion: comments, pingbacks and trackbacks
For this query, specify the comment_status as either open, closed, or registered_only. Also, specify the ping_status as either open or closed.
UPDATE wp_posts SET comment_status = 'open', ping_status = 'open' WHERE comment_status = 'closed' AND post_status = 'publish';Globally enable/disable comments, pingbacks and trackbacks before a certain date
For this query, specify the comment_status as either open, closed, or registered_only. Also, specify the ping_status as either open or closed. Finally, specify the date by editing the 2008-01-01 to suit your needs.
UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed' WHERE post_date < '2008-01-01' AND post_status = 'publish';This last query is the money shot. I will be using it periodically to manage sitewide discussion options here at Perishable Press.
More Information
While these SQL queries are rather basic, they remain quite effective at manipulating discussion-management options in the WordPress database. Remember to backup your database before trying any of these queries, just in case you find yourself experiencing some unexpected behavior. If you do happen to receive errors upon executing one of these queries, open your site’s config.php file and double-check the default database table prefix, which is generally “wp_”. If the prefix is something else, simply edit the desired query to match. And finally, as always, I love to hear your ideas, thoughts, and criticisms. New queries welcome!
8 responses to “WordPress Discussion Management: Enable or Disable Comments and Pingbacks via SQL”
Great post… the sample queries at the end were very helpful. Thanks!
I’ve been experiencing some slow queries on one of my blogs, specifically the “simple flood protection” query in wp-includes/comment.php, line 192. I disabled comments on all my old posts, and I’m hoping that will help some until I can figure out how to rewrite that query. Wasn’t planning on learning SQL just yet, but I guess I’ll have to. :)
Very interesting about the
wp-includes/comment.php“simple flood protection”.. this is the first opportunity I have had to look into it. I am no expert in SQL, but seeing code like this sets my mind reeling with ideas for tweaking, experimentation, and plugins. In any case, I am wondering how you managed to pinpoint that particular function as the culprit for the “slow queries” on your blog..I’m using MediaTemple for shared webhosting. They have a slow query report you can run in certain circumstances (like when they’ve decided that your site is causing problems), and that report lists problem queries. I searched for some of the text from the query on Google, and found a page that said the query was related to comment verification. So I looked through a few of the comment-related pages in my wordpress install until I found it. There was one other query listed in the report, but I haven’t been able to figure out where it lives yet.
My current thinking is that if I can get it to only search throught the last 10 comments or so, or maybe just that day’s comments, rather than all the comments in the table, that might fix it. Whether that’s possible, I don’t know. If you have any ideas, I’d love to hear them.
Very interesting indeed. I certainly wish my web host would provide such a tool. I know there are plugins to diagnose other plugins, but testing the WordPress core would be incredibly useful.
In any case, I tried exploring a few options for limiting the scope of the dupe-content query. Specifically, I tried your idea of limiting the query to same-day comments by including several variations of this into line #186:
WHERE comment_date = CURDATE()But I couldn’t get this to work as the formatting of
CURDATEis different than that ofcomment_date. Or something. As mentioned, I am no expert with SQL! I also tried implementing this parameter:WHERE $commentdata['comment_date'] = current_time('mysql');But again, no dice. I am sure there is a way to improve the function, but I suppose further investigation is required.
Hello, thank you for the interesting article. For various reasons I have a wordpress installation where I had to modify the administration pages.
Now I need to change the global time after that comments from an article are automatically closed. I imagine it’s the wp_options table, but I don’t know which variable.
Do you happen to know? Thank you in advance.
Hi Giulio, I am not sure about WordPress 2.5+, but I don’t think that automatic comment closing is a native feature of WordPress (although it should be). There are, however, several plugins available to do the job. Personally, I prefer the manual method of closing old comments due to the continuous nature of ongoing comment conversations — I would hate to terminate a thread that continues to advance the topic!
Thanks a lot, I was looking for a way to disable comments on all existing posts to then manually enable it for a select few … this helped me :)
My pleasure, mores — glad to be of service :)