Code to remove authors with no posts connected to them

Question

I am looking for SQL code that I can run from phpmyadmin to remove all registered users on the blog that do not have any post associated with them.

Answer 1

<?php

/**
 * Plugin Name: Delete Non Authors
 */

function delete_non_authors()
{
    global $wpdb;
    $non_authors = $wpdb->get_col(
        "SELECT DISTINCT $wpdb->users.ID FROM $wpdb->users
        LEFT JOIN $wpdb->posts ON $wpdb->users.ID = $wpdb->posts.post_author
        WHERE $wpdb->posts.ID IS NULL"
    );

    foreach ($non_authors as $user_ID)
        wp_delete_user($user_ID);
}
register_activation_hook(__FILE__, 'delete_non_authors');

?>

Drop this in a file, name it something like delete-non-authors.php, upload it to your plugins folder, activate, and you're done! (You can then de-activate).

Answer 2

The idea is we will get all authors who have some post, and delete user who not in that list! If you're going to use SQL only, run this command on PHPMYADMIN:

DELETE FROM wp_users WHERE ID NOT IN (SELECT DISTINCT post_author FROM wp_posts)

If you're going to do in code use this:

global $wpdb;
$wpdb->query("DELETE FROM {$wpdb->users} WHERE ID NOT IN (SELECT DISTINCT post_author FROM {$wpdb->posts})
")

Answer 3

I don't have ready access to the code, so I don't want to get too specific and quote the wrong functions, but it would probably be a better idea to do it via PHP and MySQL rather than just deleting the records from the wp_users table. If you do it via PHP and use WordPress's built in functions, it will do it cleanly and remove the users without leaving orphaned data in the wp_usermeta table, etc.