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:
|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:
|ID of the user|
The identifier of the user appears in the table of posts if he has created one (on a collaborative website).
|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.
|ID of the user|
It contains the options for a user and hence his ID.
|ID of the user|
This is the list of blogrolls for each user.
The SQL query
- 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.
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:
And to exclude the number 1
The full queries
To first see the list of accounts to be deleted, use this command:
Example with the suffix wp_
SELECT * is replaced by DELETE in the three tables involved.
For the table usermeta we also replaces ID by user_id.
For the table links, ID is replaced by link_owner.
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.
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.
- Download and extract the archive in the root directory of the Wordpress site.
- If you want, change the file name.
- Test the script on a local installation of you site. See installing Wordpress locally to see how to make that.
- Make a backup of the database using phpMyAdmin or similar tool. The export function is not enough.
- In the URL bar of a browser, type the URL of the script.