How to remove SPAM users from WordPress site
In today’s post we’ll show you how we deal with spammers on our blog. Since we gave you the Ask and Answer (http://www.itcuties.com/answers) function on our site, there are a lot of spammers that are registering to itcuties.com.
Those users don’t contribute to our site – they don’t write any questions, answers or comments. We have decided to remove those account since they are worthless. Here is how we do it.
# Create temporary table CREATE TABLE tmp_contributors (user_id bigint(20), username VARCHAR(100)); # Insert all the users that write comments INSERT INTO tmp_contributors (user_id, username) SELECT ID, user_login FROM wp_users WHERE user_login IN (SELECT DISTINCT comment_author FROM wp_comments); # Insert all the users that write posts INSERT INTO tmp_contributors (user_id, username) SELECT ID, user_login FROM wp_users WHERE ID IN (SELECT DISTINCT post_author FROM wp_posts); # Delete all the users that does nothing DELETE FROM wp_users WHERE ID NOT IN (SELECT DISTINCT user_id FROM tmp_contributors); # Delete user meta DELETE FROM wp_usermeta WHERE user_id NOT IN (SELECT DISTINCT user_id FROM tmp_contributors);
What we do in this SQL script is we create temporary table
tmp_contributors where we store user id’s na usernames of the accounts that contribute to our site. Next we get accounts that have at least one comment (
wp_comments table) (comments are answers to the questions as well – this is our implementation) on our site and we store them in the temporary table. The same is with accounts that write questions, which are a special type of posts (
wp_posts table) in our implementations. We get data for accounts that have at least one post published on our site and store it in the temporary table. At the end of our script we delete data from
wp_users table based on the ID’s – all the accounts that have ID that is not stored in the temporary tmp_contributors table are deleted. The same thing is done with account metadata, all the rows with user_id that is not stored in the temporary table are deleted.
The last step of our “deal with the spammers” procedure is to delete the temporary table.
# Delete temporary table DROP TABLE tmp_contributors;