Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 4 down vote accepted
<?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).

share|improve this answer
    
awesome thanks! –  OperaManiac Dec 15 '10 at 4:18
    
Did not work out... WordPress gave this error: "The plugin generated 460 characters of unexpected output during activation. If you notice “headers already sent” messages, problems with syndication feeds or other issues, try deactivating or removing this plugin." and this: notepad.cc/share/iQxQkrmeJk –  OperaManiac Jan 27 '11 at 13:51
    
You need to wrap the code in <?php ... ?> tags - I've updated my answer to reflect. Make sure that when you copy and paste, there are no line breaks or spaces before the opening tag, or after the closing tag. –  TheDeadMedic Feb 2 '11 at 12:40
add comment

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})
")
share|improve this answer
    
awesome thanks! –  OperaManiac Dec 15 '10 at 4:19
    
ok. phpmyadmin did show that a lot of rows were deleted. but wordpress backend still shows authors with zero posts. :D –  OperaManiac Jan 27 '11 at 13:55
    
What about deleting associated usermeta data? –  user4122 Mar 21 '11 at 23:59
add comment

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.

share|improve this answer
    
A PHP file that I can upload on the server and run would be just fine! :) –  OperaManiac Dec 13 '10 at 19:30
add comment

protected by Community Jun 3 '11 at 15:36

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.