How to Delete Spam WordPress Users in Bulk Using SQL Statements

So you log into your WordPress site and see 50,000 new subscribers. You are teaming with excitement, until you realize it’s ALL spam. Boo, no one likes spammers. So now what?

 

Option 1: Bulk Delete from Admin Panel

Well, if you have a few extra users, you can always delete them from the admin panel. To show a larger number of users click Screen Options, then change the number of items per page. This way you can show 100 users at a time and delete them using the bulk actions.

 

 

Although WordPress allows you to apply a large number of items per page like 50,000, it usually presents an error when loading or when performing a bulk action.

“Request-URI Too Long. The requested URL’s length exceeds the capacity limit for this server.”

If you have more than let’s say 100 users, it may be tedious and time consuming to delete the spammers from the admin panel. So, if you feel comfortable, this can be accomplished in seconds by running sql statements against the database in phpMyAdmin.

A few warnings:

• Before you run anything, be sure to backup your database.
• This example only excludes the admin from deletion, you can add more conditions if necessary.

 

Option 2: Run SQL Statements against the database

WordPress user data in stored in two tables, wp_users and wp_usersmeta. Some tables may have some variation of this, such as wp_bhtf11_users. When you login, a list of tables should appear. First find the wp_users table and click Browse.  From this screen you will see all of the registered users on your site. Now click SQL from top tabs. From here you can run a sql statement.

First, let’s make sure our statement is correct. Here’s the basic setup to display all entries.

SELECT * FROM 'table_name' WHERE 1

Be sure that the table name is correct. Now we want to include some conditions. For this we will add a column name and an equation of sorts. On this screen, you can move your cursor after the “WHERE” then select and insert the column name into the statement then add “<>”, which means does not equal. I prefer to use the user ID, because the admin is usually 1. This makes for easier commands and less conflict. Now your statement should look something like this:

SELECT * FROM `wp_users` WHERE `ID` <> 1

 

 

So your statement now says show all entries from the wp_users tables whose ID is not 1. Now run your statement. If you have no errors and the admin is NOT displayed, then you’re ready to delete.

Click the SQL tab once more. This time enter the following line:

DELETE FROM `wp_users` WHERE `ID` <> 1

Once you get your confirmation message, the task is complete. Now you can do this same process for the wp_usersmeta table. The final statement should look like:

DELETE FROM `wp_usersmeta` WHERE `ID` <> 1

To confirm that all of the unwanted users are gone, login to your site’s admin panel. You should only see admin. If you only see admin listed under all users, but 50,000 users total, then your usermeta sql statement was not run or run incorrectly.

 

 

That’s it! In a matter of minutes you have completed what may have taken hours. Enjoy.