Clear users in WordPress

How to get rid of spammer robots that register on numerous blogs to get a back link?

To do this, the script that needs to be posted on the network can perform an operation: erase all spam into a block, that is, fictitious registrations in order to post the URL of your site on the blog.

The script takes a list of users in the database and erases anyone who has never added comments.
To do this, you need to study Wordpress tables using a relational database to find relationships between different tables and delete the user, remove references to this user in all tables.

Wordpress Table and Users

User table:

SAME user_login ... user_nicename    
Unique number User's nickname   Display name    

Other tables search for references to user ID or login, username, or other data.

Message table:

  post_author        
  User ID        

The user ID will appear in the ticket table if he created it (on the shared site).

Comment table:

  comment_author ...     user_id
  Name specified during comment       Registered User ID

To find out the user, you need to view the comment table. It contains several information about the user taken when the comment was displayed and his ID. Only the last field interests us, users whose ID is present in this table should not be deleted.

usermeta table:

  user_id        
  User ID        

Contains the parameters of the user and, therefore, its identifier.

Links table:

  link_owner        
  User ID        

This is a list of blogrolls for each user.

SQL Query

Condition:

- indicates a list of users.
- Compares it with the comment table.
"Compares it to a ticket table.
- Reduce the list of users by excluding those who wrote a post or comment.
- Excludes administrator ID 1.

Action:

For selected users.

- Erases the user table entry.
- erases the entries in the usermeta table.
- erases the entries in the reference table .

Take a list of users

SELECT AN IDENTIFIER FROM THE wp_users

And eliminate number 1

SELECT ID FROM wp_users WHERE user_id> 1

Full inquiries

Preliminary test

To preview the list of erased accounts, use the following command:

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

Example with wp _ suffix

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

Clarification

Three matching tables use DELETE instead of SELECT *.

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

For the usermeta table, the ID is also replaced by the user_id .

DELETE FROM wp_usermeta WHERE user_id> 1 AND user_id NOT IN (РАЗЛИЧНЫЙ SELECT post_author FROM wp_posts) AND user_id NOT IN (РАЗЛИЧНЫЙ SELECT user_id FROM wp_comments)

For the links table, the ID is replaced with the link_owner

DELETE FROM wp_links WHERE link_owner> 1 AND link_owner NOT IN (РАЗЛИЧНЫЙ SELECT post_author FROM wp_posts) AND link_owner NOT IN (РАЗЛИЧНЫЙ SELECT user_id FROM wp_comments)

Restrictions

Plug-ins

Frequently used plugins do not create tables or affect users.
But it is possible that plugins specializing in user management or statistics will create a new table that is linked to the user table. This can be checked using 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.

Use script

Instead of doing three SQL commands every time you want to clean the database, you will create a script to perform the operation, which you will post to the network .

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...

This script is not tested with each new version of Wordpress. It may not work with the new version.

  1. Download the archive and extract it to the root directory of the Wordpress site.
  2. Change the file name.
  3. Check the script on the local version of the site. To do this, see how to install Wordpress locally to get this local version.
  4. Back up the database using phpMyAdmin or an equivalent tool. The export function is insufficient.
    5. In
  5. In the browser URL string, enter the script URL.