Easy Comment Management via SQL Queries

Here are some sweet SQL code snippets for easy comment management. Sometimes it’s easier to modify comment status and delete unwanted comments on a sitewide basis. Using a program like phpMyAdmin makes it so easy to do stuff like remove spam, close/open comments on old posts, enable/disable pingbacks for specific time periods, and so on. Just remember to backup your database before running any queries (just to be on the safe side).

Remove all spam comments from the database

This SQL query removes all comments marked as “spam” from the database:

DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

Works great in all versions of WordPress!

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:

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 match whatever date you want to target.

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2008-01-01' AND post_status = 'publish';

I run this query 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:

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';

That last query is perfect for manual control over the closing of both comments and xbacks for posts from a specific time period. Sure there are plugins for this, but there are also plugins that enable direct SQL commands, making it convenient for granular control over discussion intervals.

Here are some more awesome SQL techniques!