👻 Halloween Treat: Our biggest-ever 25% OFF memberships — use SPOOKYSOCIAL before Nov 1! 🎃

BuddyDev

Search

[Resolved] Delete all blog comments by specific user

  • Participant
    Level: Enlightened
    Posts: 35
    Michael on June 15, 2024 at 3:54 pm #52721

    Hi guys, hope you can advise.

    I am using a SQL query to delete all wordpress blog comments by a specific user, userA, identified by email address.

    
"DELETE FROM wp_comments
 WHERE comment_author_email = 'name@email.com'"

    It does indeed delete those comments by userA, but the system still counts them as existing when it displays how many comments there are for an individual post.

    For example, a post has two comments, one each by userA and userB, I then SQL-delete all by userA, but the page incorrectly still displays “Two thoughts on post xxx” and it correctly shows only the comment by userB.

    Any ideas?

    Thanks!

  • Keymaster
    (BuddyDev Team)
    Posts: 25189
    Brajesh Singh on June 17, 2024 at 1:41 am #52729

    Hi Michael,
    The comment count is stored in the posts table. Your code deletes from comments table but does not modify the post table.

    I will suggest using some DB cleaning tool to bulk delete comments. You may want to try Bulk Delete comments plugin instead.

    Or you can go for recounting all posts comments after deletion.

    Regards
    Brajesh

  • Participant
    Level: Enlightened
    Posts: 35
    Michael on June 18, 2024 at 3:09 pm #52736

    Thanks Brajesh,

    – I looked at the Bulk Delete plugin but the functionality to delete comments per specific user id is only available in the premium version, and I will probably only use it only once.

    – re. Recounting all the post comments, I’m unable to write the correct code myself, but I found the following on Stack overflow :

    
$entries = $wpdb->get_results("SELECT * FROM wp_posts WHERE post_type IN ('post', 'page')");

foreach($entries as $entry)
{
    $post_id = $entry->ID;
    $comment_count = $entries = $wpdb->get_results("SELECT * FROM wp_posts WHERE post_type IN ('post', 'page')");
    $wpdb->query("UPDATE wp_posts SET comment_count = '$comment_count' WHERE ID = '$post_id'");
}

    – I tested in test environment, seems to work fine (albeit with a small number of posts)

    Question – my live site has over 1000 posts, would the code be safe to use?

    Thanks!

  • Participant
    Level: Enlightened
    Posts: 35
    Michael on June 18, 2024 at 10:02 pm #52737

    Sorry! Sorry! Sorry!

    The code snippet above was pasted wrong, here’s the correct version:

    
$entries = $wpdb->get_results("SELECT * FROM wp_posts WHERE post_type IN ('post', 'page')");

foreach($entries as $entry)
{
    $post_id = $entry->ID;
    $comment_count = $wpdb->get_var("SELECT COUNT(*) AS comment_cnt FROM wp_comments WHERE comment_post_ID = '$post_id' AND comment_approved = '1'");
    $wpdb->query("UPDATE wp_posts SET comment_count = '$comment_count' WHERE ID = '$post_id'");
}
  • Keymaster
    Level: Yogi
    (BuddyDev Team)
    Posts: 3115
    Ravi on June 21, 2024 at 6:58 am #52747

    Hello Michael,

    Try the following way:

    

$comments = get_comments( 'author_email=' . $user_email );

foreach ( $comments as $comment ) {
	wp_delete_comment( $comment->comment_ID, true );
}

    PS: it may have some performance issues if comments are in large quantity.

    Regards
    Ravi

  • Participant
    Level: Enlightened
    Posts: 35
    Michael on June 21, 2024 at 4:26 pm #52749

    Thankyou so much Ravi, that works great on my test environment. Deletes the comments and correctly updates the total comments count per post too. Perfect.

    Re. possible performance issues for large numbers of comments.

    – the particular user has approx. 2000 comments spread over 4 years.
    – would it be safe to do them all at once?
    – or maybe using ‘date_query’ with the get_comments() function, I could delete them in smaller batches.

    What would you recommend?

    Thanks!

  • Participant
    Level: Enlightened
    Posts: 35
    Michael on June 26, 2024 at 9:19 am #52769

    Hi Ravi,

    Hope you can kindly answer my last question and then this can be noted as “resolved”.

    Thanks!

  • Keymaster
    Level: Yogi
    (BuddyDev Team)
    Posts: 3115
    Ravi on June 27, 2024 at 10:14 am #52773

    Hello Michael,

    Thank you for the acknowledgement. With this number of comments, you can use it with no performance issues.

    Regards
    Ravi

  • Participant
    Level: Enlightened
    Posts: 35
    Michael on June 27, 2024 at 11:00 am #52774

    Thanks Ravi!

The topic ‘ [Resolved] Delete all blog comments by specific user’ is closed to new replies.

This topic is: resolved