DiggingIntoWordPress

by Chris Coyier & Jeff Starr

Easy Comment Management via SQL

Posted by on

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.

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 including 3.0!

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

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!

9 Responses

  1. Some great SQL snippets. Thanks

  2. What’s the difference between a trackback and a pingback? Is there a way to disable one and enable another?

    • There are many ways to explain it, but essentially pingbacks require an actual link from one article to another. Conversely, you can set a trackback to another post without linking to it.

      The easiest way to disable trackbacks only is to use a plugin.

      • I’m still confused. If someone links to a blog on my site, is that a pingback or a trackback? If I link in one blog post to another blog post of mine on my site, is that a pingback or a trackback?

        • If they link to your blog, it’s a pingback. If you look in the Write/Edit Post screen, just beneath the Excerpt meta box, you will see a box for entering trackbacks. No linking is necessary for trackbacks, which are typically associated with spam.

  3. Newbie Wordpress August 9, 2010

    wow, i will try this later

  4. Wow..Nice piece of code,Jeff
    Thank

  5. Quick question Jeff,
    What changed in the ATOM\RPC protocalls from 2.9.2 and 3.0.1? I had a drip feeder working fine in 2.9.2 but when I upgraded to 3.0.1 it no longer works.

  6. Wow this is great i really needed this

Comments are closed. Contact us with any critical information. Thank you!

Code is poetry