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

[Solved] Wordpress - SQL to remove inactive users

1 Posts
1 Users
0 Reactions
2,271 Views
 Hans
(@hans)
Famed Member Admin
Joined: 11 years ago
Posts: 2805
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
Share: