Someone asked for a way to delete 15000 users from WordPress based on their role.
I have this snippet I’ve used before, not sure how well it will perform for 15000, but I’m sure it can be tuned to do that 🙂
<?php
define( 'WP_USE_THEMES', false );
require_once( './wp-load.php' );
require_once( ABSPATH.'wp-admin/includes/user.php' );
$role = 'subscriber'; // The role to kill.
$reassign = 1; // The user that all posts will fall back to, other wise they will be deleted.
$this_role = sprintf( 's:%d:"%s";', strlen( $role ), $role );
$results = $wpdb->get_col( $wpdb->prepare( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = '{$wpdb->prefix}capabilities' AND meta_value LIKE %s", '%' . $wpdb->esc_like( $this_role ) . '%' ) );
if ( $results ) {
foreach ( $results as $user_id ) {
wp_delete_user( $user_id, $reassign );
}
}
(Edited to skip the wpdb->users table from the mix)
(Updated: 2017-01-06, works for WordPress 4.x, added a version which generates a PHP file with each of the delete commands)
Here is a version where you can pipe the output into a separate PHP file, review and then run.
<?php
define( 'WP_USE_THEMES', false );
require_once( './wp-load.php' );
$role = 'subscriber'; // The role to kill.
$reassign = 1; // The user that all posts will fall back to, other wise they will be deleted.
$this_role = sprintf( 's:%d:"%s";', strlen( $role ), $role );
$results = $wpdb->get_col( $wpdb->prepare( "SELECT user_id FROM $wpdb->usermeta WHERE meta_key = '{$wpdb->prefix}capabilities' AND meta_value LIKE %s", '%' . $wpdb->esc_like( $this_role ) . '%' ) );
if ( $results ) {
echo <<<HTML
<?php
define( 'WP_USE_THEMES', false );
require_once( './wp-load.php' );
require_once( ABSPATH.'wp-admin/includes/user.php' );
HTML;
foreach ( $results as $user_id ) {
echo "wp_delete_user( $user_id, $reassign );\n";
}
}
When you run it, e.g. like this:
php this-snippet.php > output.php
The output should generate something like
<?php define( 'WP_USE_THEMES', false ); require_once( './wp-load.php' ); require_once( ABSPATH.'wp-admin/includes/user.php' ); wp_delete_user( 2, 1 ); wp_delete_user( 3, 1 ); ... wp_delete_user( 15000, 1 );
Which you can then review before you decide to delete those 15000 users.
egill.blog 
Thinking about it I might just skip the select from wpdb->users and simply use the id from usermeta.
LikeLike
The short answer is yes – everything is possible. You can modify the code to delete users that have comments marked as spam (if I understand you correctly). I would modify the code as well to take into account the number of successful comments vs. comments marked as spam (You probably don’t want to delete a user with > 3 accepted comments and 1 marked as spam).
LikeLike
This is an awesome bit of code – I wonder if this could be used to mass delete the 17,000 users I have in my multisite database that are marked as spam? And would it also delete the associated blogs (I think now labeled as “sites”?) – that are marked as spam – would be nice to clear out some space in my multi-site database this way.
Would also be nice to have a bit of code to cull the ip addys used to sign up these spam accounts and their associated email addys to look for other ways or banning or auto-deleting future issues with them – but heck if this bit of code could simply delete and free up the space from all the users and sites marked as spam, that would be cool.
LikeLike
thanks for the reply egill – it’s not the users that have comments marked as spam – it’s the thousands of users who signed up for user accounts. I have one site that is running wordpressMu – will I guess it’s “wordpress multi-site enabled” now – and I have another that is setup as multi-site with buddypress installed. On both of these sites there has been thousands of “new user” sign up – many, but not all of them chose to sign up for a “new blog” as well as the user account.
I go through on a regular basis and mass “mark as spam” all users who appear to be fake – but that has left my database full of users who are marked as spam, and full of blogs, maybe they are called “sites” now in the backend – and so I have thousands of those marked as spam.
I do not see any reason to keep all this info clogging up the database – it would be awesome if wordpress could check the users marked as spam and add thier associated sign up IPs into a ban list so they could not sign up for more spam user accounts and create new blogs/sites – but that’s another issue I guess.
In the meantime, if you took your bit of code and made into a multi-site plugin that would slowly comb through the thousands of users that had signed up and slowly delete them along with their associated blog (or sites they may be called now) – I am betting that there are a lot of multi-site / wordpressMU users out there that would benefit from clearing out so much unused data from their databases.
LikeLike
Marking users as spam isn’t something I’ve done before. How do you mark them?
LikeLike
I can think of a couple of scenarios for user deletion, users that are older than X without comments and/or without blogs, users with more than X comment marked as spam, deletion based on a regexp (username, name or email), while whitelisting users with roles (editor, author, administrator, contributor). I guess I could wrap this up into a plugin. I’ll look into it at least.
LikeLike