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

Question

My user database is full up with robots. Is there a useful SQL command to delete all the users that either a) have posted a comment marked as spam by Akismet or b) never posted a comment ?

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

Answer 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.