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

6 comments

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

   Log in to Reply

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.

   Log in to Reply

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:

   

   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 comment_post_ID=@x;
      
      INSERT INTO wp_commentmeta (comment_id, meta_key, meta_value) SELECT comment_ID, "wp_trash_meta_status",comment_approved FROM wp_comments WHERE comment_post_ID=@x;
      

   3. Trash all comments:

      UPDATE wp_comments SET comment_approved='trash' WHERE comment_post_ID=@x;
      

   Log in to Reply

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

   Log in to Reply

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})
   

   Log in to Reply

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() );
   }
   

   Log in to Reply