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 everyone)
  • closed (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!