How do I delete all comments from a specific old blog post?

The question:

I’m cleaning up an old, controversial blog entry and I need to remove all the comments from the post.

Surprisingly, I can’t find a way to do this within the existing WordPress (3.0.4) UI.

I can certainly go through and click “trash” on all 200+ comments, but that seems.. excessive. Is there another way to do this that I am missing?

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

Alternative for people reading this with a fear for SQL…………… (or finding this via Google after Januari 2011):

Wait for this action until 3.1 comes out, then go to a post, check all comments and bulk “move to trash” 🙂 (it should come out any day now) (http://wordpress.org/about/roadmap/)

(or download 3.1 RC3 from http://wordpress.org/download/release-archive/)

Example:

http://edward.de.leau.net/files/bulkremove.png

Method 2

Hi @Jeff Atwood:

I’m assuming you have MySQL query access. This will give you all comments for your blog post whose URL slug is 'your-blog-post' (the slug is the last segment in your post’s URL if you are using pretty permalinks, i.e. for http://example.com/2011/01/foo-bar-baz/ your slug would be 'foo-bar-baz'):

SELECT * from wp_comments WHERE comment_post_ID IN (
  SELECT ID FROM wp_posts WHERE post_name='your-post-slug'
)

And this will give you all the comment metadata:

SELECT * from wp_commentmeta WHERE comment_id IN (
  SELECT comment_ID from wp_comments WHERE comment_post_ID IN (
    SELECT ID FROM wp_posts WHERE post_name='your-post-slug'
  )
) 

So… run these two commands (but be sure to replace the post_name value to be equal to yours):

DELETE from wp_commentmeta WHERE comment_id IN (
  SELECT comment_ID from wp_comments WHERE comment_post_ID IN (
    SELECT ID FROM wp_posts WHERE post_name='your-post-slug'
  )
);

DELETE from wp_comments WHERE comment_post_ID IN (
  SELECT ID FROM wp_posts WHERE post_name='your-post-slug'
);

P.S. This will of course fully delete them but unless you do want to keep them in the trash this is easier than moving them to the trash.

Method 3

WordPress doesn’t support bulk management of comments on a post by post basis. Although it does have a comment management section directly at the post’s edit page. As you said, you would have to click on “Trash” on every single comment:

alt text

Alternately you could take a back up of the wp_comments and wp_commentmeta table and run the following queries:

  1. Find post ID. Lets say the relevant id is X.

    SELECT ID from wp_posts WHERE post_type='post' AND post_title='Hello world!' INTO @x; 
    
  2. Add relevant comment metadata just to preserve integrity:

    INSERT INTO wp_commentmeta (comment_id, meta_key, meta_value) SELECT comment_ID, "wp_trash_meta_time", UNIX_TIMESTAMP() FROM wp_comments WHERE [email protected];
    
    INSERT INTO wp_commentmeta (comment_id, meta_key, meta_value) SELECT comment_ID, "wp_trash_meta_status",comment_approved FROM wp_comments WHERE [email protected];
    
  3. Trash all comments:

    UPDATE wp_comments SET comment_approved='trash' WHERE [email protected];
    

Method 4

I do not advise removing directly from the database as data may have dependencies one might end up with orphan records.

With the post ID (add it in ADDPOSTID), save the script bellow and add the PHP file in the root of your WordPress website, login as Admin and call the file directly on your browser:

<?php

require('./wp-load.php');

if ( current_user_can( 'manage_options' ) ) {
    if ( $comments = get_comments( array( 'post_id' => ADDPOSTID ) ) ) {
        foreach ( $comments as $comment ) {
            echo '<p>Deleting comment '.$comment->comment_ID.'</p>';
            wp_delete_comment( $comment->comment_ID, true );
        }
    } else {
        echo '<p>No comments found</p>';
    }
} else {
    echo '<p>Not allowed</p>';
}

Method 5

Nowadays, the best solution if you can install WP-CLI on the server would be using it this way:

$ wp comment delete $(wp comment list --post_id={ID})

Method 6

This is not enough, you must also update the column count, otherwise it will not be displayed correctly.
Here is a sample code for a multisite install.

try 
{   
    $rowsUpdated = $wpdb->query(
    "
    UPDATE " . $wpdb->prefix . "COMMENTS SET COMMENT_APPROVED='trash' 
    WHERE comment_post_id = " . $post_id
    );
    if ( $rowsUpdated != 0 ) {
        $wpdb->query( 
            "
            UPDATE " . $wpdb->prefix . "POSTS SET COMMENT_COUNT = COMMENT_COUNT - " .  $rowsUpdated . "
            WHERE ID = " . $post_id
        );
    }
}       
catch (Exception $e) 
{ 
    echo( 'Unexpected error while trying to connect to WP database: ' . $e->getMessage() );
}


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Comment