Page 1 of 1
Forum

Welcome to the Tweaking4All community forums!
When participating, please keep the Forum Rules in mind!

Topics for particular software or systems: Start your topic link with the name of the application or system.
For example “MacOS X – Your question“, or “MS Word – Your Tip or Trick“.

Please note that switching to another language when reading a post will not bring you to the same post, in Dutch, as there is no translation for that post!




Wordpress - SQL to ...
 
Share:
Notifications
Clear all

Wordpress - SQL to remove inactive users

1 Posts
1 Users
0 Likes
949 Views
 Hans
(@hans)
Famed Member Admin
Joined: 10 years ago
Posts: 2457
Topic starter  

Be careful with this - as you may be tossing the wrong users ... so only do this if you understand what you're doing.

Here 3 queries to help you find and remove inactive users on a multisite Wordpress setup, where I have defined "inactive" as 

- Registered more than 2 years ago
- Never posted a comment
- Never posted a Post
- Never posted in the (WPForo) forum(s)

For this we will dump the affected user ID's in a temporary table, which we will call "remove_these".
Before doing this, we just want to make sure it is gone, in case we did this before:

DROP TABLE IF EXISTS remove_these;

 

Next we will create the temporary table based on data we pull from a select statement, because ... well, we're lazy haha.

The select statement looks something like this, where you may have to adjust the table names, these are the table I have used in this example query, where I assume your WordPress tables start with "wp_"

wp_users (users)
wp_comments (comments)
wp_posts (posts)
wp_wpforo_posts (forum posts - if your site does not use WPForo, then just remove the relevant lines)

SELECT 
  wp_users.ID,
  `display_name`,
  `user_email`,
  IFNULL( `user_registered`, 0),
  IFNULL( `CommentCount`, 0), 
  IFNULL( `PostCount`, 0),
  IFNULL( `WPForoCount`, 0)
FROM wp_users  
  LEFT OUTER JOIN (SELECT wp_comments.user_id, COUNT(wp_comments.comment_ID) as CommentCount FROM wp_comments GROUP BY wp_comments.user_id) as CommentCountTable
     ON CommentCountTable.user_id = wp_users.ID  
  LEFT OUTER JOIN (SELECT wp_posts.post_author,COUNT(wp_posts.ID) as PostCount FROM wp_posts GROUP BY wp_posts.post_author) as PostCountTable
     ON PostCountTable.post_author = wp_users.ID 
  LEFT OUTER JOIN (SELECT wp_wpforo_posts.userid,COUNT(wp_wpforo_posts.postid) as WPForoCount FROM wp_wpforo_posts GROUP BY wp_wpforo_posts.userid) as WPForo1CountTable
     ON WPForoCountTable.userid = wp_users.ID
WHERE 
  ( (IFNULL( `CommentCount`, 0) + IFNULL( `PostCount`, 0) + IFNULL( `WPForoCount`, 0) )=0 ) AND
  ( user_registered<DATE_SUB(NOW(), INTERVAL 2 YEAR) )  
ORDER BY `wp_users`.`user_email` ASC;

 

Run this select query to verify the results.
Next we run this combined query to create and populate our temporary table. In the WHERE statement, you will see that I used "INTERVAL 2 YEARS" (reference), which you can change to a time frame you prefer. For example "INTERVAL 90 DAYS".

CREATE TABLE remove_these
  SELECT 
    wp_users.ID,
    `display_name`,
    `user_email`,
    IFNULL( `user_registered`, 0),
    IFNULL( `CommentCount`, 0), 
    IFNULL( `PostCount`, 0),
    IFNULL( `WPForoCount`, 0)
  FROM wp_users  
    LEFT OUTER JOIN (SELECT wp_comments.user_id, COUNT(wp_comments.comment_ID) as CommentCount FROM wp_comments GROUP BY wp_comments.user_id) as CommentCountTable
       ON CommentCountTable.user_id = wp_users.ID  
    LEFT OUTER JOIN (SELECT wp_posts.post_author,COUNT(wp_posts.ID) as PostCount FROM wp_posts GROUP BY wp_posts.post_author) as PostCountTable
       ON PostCountTable.post_author = wp_users.ID 
    LEFT OUTER JOIN (SELECT wp_wpforo_posts.userid,COUNT(wp_wpforo_posts.postid) as WPForoCount FROM wp_wpforo_posts GROUP BY wp_wpforo_posts.userid) as WPForo1CountTable
       ON WPForoCountTable.userid = wp_users.ID
  WHERE 
    ( (IFNULL( `CommentCount`, 0) + IFNULL( `PostCount`, 0) + IFNULL( `WPForoCount`, 0) )=0 ) AND
    ( user_registered<DATE_SUB(NOW(), INTERVAL 2 YEAR) )  
  ORDER BY `wp_users`.`user_email` ASC;

 

Again: scroll through that remove_these table and make sure the data looks valid.

Finally we remove the users from the wp_users table.
Caution this is PERMANENT.

DELETE FROM wp_users WHERE wp_users.ID in (SELECT remove_these.ID FROM remove_these);

 

When done, you can optionally keep the temporary table for future reference or toss it like so:

DROP TABLE IF EXISTS remove_these;

   
ReplyQuote

Like what you see and you'd like to help out? 

The best way to help is of course by assisting others with their questions here in the forum, but you can also help us out in other ways:

- Do your shopping at Amazon, it will not cost you anything extra but may generate a small commission for us,
- send a cup of coffee through PayPal ($5, $10, $20, or custom amount),
- become a Patreon,
- donate BitCoin (BTC), or BitCoinCash (BCH).

Share: