How to mass delete users on Wordpress

How to get rid of spambots registering on lot of blogs only to get backlink?

To do this, a script to upload, can perform the operation: erase block all spam, ie the fictitious entries in order to put the URL of its website on the blog.

The script takes the list of users in the database, and deletes all those who have never added any comments.
To do this, we must study the Wordpress tables, using a relational database, to detect links between the various tables and deleting a user, delete the references to the users in all tables.

Wordpress tables and users

The table users:

ID user_login ... user_nicename    
Unique number Name of the user   Displayed name    

We look at other tables to references to the ID or login name for the user, or other data.

The table posts:

  post_author        
  ID of the user        

The identifier of the user appears in the table of posts if he has created one (on a collaborative website).

Table comments:

  comment_author ...     user_id
  Name given with the comment       ID of the user registered

The table of comments should be consulted to determine if the user is present. It contains more information about the user taken when he commented, and his ID. Only the last field is useful here, users whose ID is included in this table should not be deleted.

Table usermeta:

  user_id        
  ID of the user        

It contains the options for a user and hence his ID.

Table links:

  link_owner        
  ID of the user        

This is the list of blogrolls for each user.

The SQL query

The condition:

- From the list of users.
- The table compares with the comments.
- The table compares the posts.
- Reduces the list of users by eliminating those who have produced a post or comment.
- Eliminates ID 1, it is that of the administrator.

The action:

For users whose IDs is selected by the condition.

- It deletes the entry in the users table.
- Clears the entries in the usermeta table.
- Clears the entries in the links table.

Getting the list of users:

SELECT ID FROM wp_users

And to exclude the number 1

SELECT ID FROM wp_users WHERE user_id > 1

The full queries

Pretest

To first see the list of accounts to be deleted, use this command:

SELECT * FROM $usertable WHERE ID > 1 AND ID NOT IN (SELECT DISTINCT post_author FROM $postable) AND ID NOT IN (SELECT DISTINCT user_id FROM $commentable)

Example with the suffix wp_

SELECT * FROM wp_users WHERE ID > 1 AND ID NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND ID NOT IN (SELECT DISTINCT user_id FROM wp_comments)

Deletion

SELECT * is replaced by DELETE in the three tables involved.

DELETE FROM wp_users WHERE ID > 1 AND ID NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND ID NOT IN (SELECT DISTINCT user_id FROM wp_comments)

For the table usermeta we also replaces ID by user_id.

DELETE FROM wp_usermeta WHERE user_id > 1 AND user_id NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND user_id NOT IN (SELECT DISTINCT user_id FROM wp_comments)

For the table links, ID is replaced by link_owner.

DELETE FROM wp_links WHERE link_owner > 1 AND link_owner NOT IN (SELECT DISTINCT post_author FROM wp_posts) AND link_owner NOT IN (SELECT DISTINCT user_id FROM wp_comments)

Limitations

Plugins

Plugins commonly used do not create tables and have no impact on users.
But it is possible that plugins specializing in user management and statistics create a new table that is linked to the user table. This can be checked with phpMyAdmin.

Multiple Accounts

If you create multiple accounts to access the site, and they are not used to create a post or comment, they will be deleted.

Making a script

Rather than type three SQL commands every time you want to clean the database, we will create a script to perform the operation, we will put online.

Source code :

<?php
include("wp-config.php");

$usertable=$table_prefix."users";
$commentable=$table_prefix."comments";
$usermeta=$table_prefix."usermeta";
$postable=$table_prefix."posts";
$linktable=$table_prefix."links";

$db_handler=mysql_connect(DB_HOST, DB_USER, DB_PASSWORD)
or die("MySQL database '".DB_NAME."' not accessible.");

mysql_select_db(DB_NAME, $db_handler)
or die("Enable to select ".DB_NAME." database
\n");

$query1="DELETE FROM $usertable 
         WHERE ID > 1 
         AND ID NOT IN (SELECT DISTINCT post_author FROM $postable) 
         AND ID NOT IN (SELECT DISTINCT user_id FROM $commentable)";

$query2="DELETE FROM $usermeta 
         WHERE user_id > 1 
         AND user_id NOT IN (SELECT DISTINCT post_author FROM $postable) 
         AND user_id NOT IN (SELECT DISTINCT user_id FROM $commentable)";

$query3="DELETE FROM $linktable 
         WHERE link_owner > 1 
         AND link_owner NOT IN (SELECT DISTINCT post_author FROM $postable) 
         AND link_owner NOT IN (SELECT DISTINCT user_id FROM $commentable)";

mysql_query($query1,$db_handler);
mysql_query($query2,$db_handler);
mysql_query($query3,$db_handler);

echo "Done!";
?>

To use it...

The script is not tested with each new version of Wordpress. It could to not work with a revent version.

  1. Download and extract the archive in the root directory of the Wordpress site.
  2. If you want, change the file name.
  3. Test the script on a local installation of you site. See installing Wordpress locally to see how to make that.
  4. Make a backup of the database using phpMyAdmin or similar tool. The export function is not enough.
  5. In the URL bar of a browser, type the URL of the script.