How can I delete all users which have never commented / have posted spam comments?

2 comments

1. Hi @Nick Loman:

   There is not one command, but several. Be care though to back up your database before running this because there’s a tiny chance something in your database differs from mine, especially after a .1 upgrade beyond 3.0 and thus it may not work perfectly and you’ll have to restore.

   DELETE FROM wp_usermeta WHERE user_id IN (
     SELECT user_id FROM wp_comments WHERE user_id<>0 AND comment_approved='spam' 
   ) AND user_id NOT IN (
     SELECT DISTINCT user_id FROM wp_comments
   );
   DELETE FROM wp_users WHERE ID IN (
     SELECT user_id FROM wp_comments WHERE user_id<>0 AND comment_approved='spam' 
   ) AND ID NOT IN (
     SELECT DISTINCT user_id FROM wp_comments
   );
   DELETE FROM wp_commentmeta WHERE comment_id IN (
     SELECT comment_ID FROM wp_comments WHERE comment_approved='spam' 
   );
   DELETE FROM wp_comments WHERE comment_approved='spam'; 
   

   Log in to Reply

2. You’ll want to use the wp_delete_user() function:
   http://codex.wordpress.org/Function_Reference/wp_delete_user

   However, first you need all the user ids to delete. I’d suggest getting them by doing something like the following (note: this code is not tested):

   global $wpdb;
   $delete_ids = $wpdb->get_col(
       "SELECT 
           users.ID 
       FROM 
           {$wpdb->users} users 
       LEFT JOIN 
           {$wpdb->prefix}comments comments ON comments.user_id = users.ID
       WHERE 1
           AND (
               comments.comment_ID IS NULL 
               OR 
               comments.comment_approved = 'spam'
           )"
   );
   
   foreach ($delete_ids as $id) {
       wp_delete_user($id);
   }
   

   I would suggest running the query manually and spot checking the results before doing any deletions. And, of course, back up your database before making any changes.

   Log in to Reply