Notifications
Clear all

wpForo 1.x.x [Solved] Delete inactive users

13 Posts
5 Users
1 Reactions
3,355 Views
Posts: 6
Topic starter
(@scarlet)
Active Member
Joined: 3 years ago

Hi,

I want to delete members who have never posted and logged into the forum for the past 1 year. Can we do this over the database?

12 Replies
Posts: 992
Moderator
(@martin)
Support Team
Joined: 8 years ago

@scarlet ,

You can delete inactive users in Dashboard > Forums > Members admin page, just filter users by "inactive", select all and delete.

Posts: 6
Topic starter
(@scarlet)
Active Member
Joined: 3 years ago

Hi, thank you for your info but this feature is not work.

I see only 4 users in the inactive members section.

1 Reply
Robert
Admin
(@robert)
Joined: 9 years ago

Support Team
Posts: 10590

It says your users were registered before installing wpForo. wpForo detects the users who don't activate their account after the registration. In this case, there is no way to find and remove the inactive users.

Posts: 6
Topic starter
(@scarlet)
Active Member
Joined: 3 years ago

I know and I asked sql query for this request. 

For example, can we list and delete users who have no posts in the database and according to the last login date?

1 Reply
Robert
Admin
(@robert)
Joined: 9 years ago

Support Team
Posts: 10590

That's very hard because we have to JOIN three tables:
wp_users
wp_usermeta
wp_wpforo_profile

The information about the number of forum posts is located in the wp_wpforo_profile table, but we need to connect all three tables by User ID and delete all together. There is no way to clean up tables one by one.

Robert
Posts: 10590
Admin
(@robert)
Support Team
Joined: 9 years ago

Please try the following SQL in your Hosting Service phpMyAdmin database manager. Make sure the red marked table prefixes are correct and change the date range you want:

DELETE u, um, p FROM `wp_users` u
   INNER JOIN `wp_wpforo_profiles` p ON p.`userid`   = u.`ID`
       LEFT JOIN `wp_usermeta` um       ON um.`user_id` = u.`ID`
WHERE p.`posts` = 0
AND u.`ID` NOT IN( SELECT DISTINCT `post_author` FROM `wp_posts` )
AND p.`last_login` BETWEEN '2021-03-01' AND '2021-05-01'

 

In any case, don't forget to backup the following tables:

  1. wp_users
  2. wp_usermeta
  3. wp_wpforo_profiles
5 Replies
(@highisland)
Joined: 5 years ago

Eminent Member
Posts: 23

@robert I don't see the last_login column on the wp_wpforo_profiles table now for wpForo 2.0.  I guess it's been removed.  How can I do the same?  Use the online_time column = 0?

Chris
(@chris)
Joined: 3 years ago

Famed Member
Posts: 3627

@highisland,

Try this one

DELETE u, um, p FROM `wp_users` u
   INNER JOIN `wp_wpforo_profiles` p ON p.`userid`   = u.`ID`
       LEFT JOIN `wp_usermeta` um       ON um.`user_id` = u.`ID`
WHERE p.`posts` = 0
AND u.`ID` NOT IN( SELECT DISTINCT `post_author` FROM `wp_posts` )
AND p.`online_time` BETWEEN Unix Timestamp AND Unix Timestamp

 

Open the below Link, its Date & Time to Unix Timestamp converter, Enter the Date & Time and convert to Unix Timestamp.

https://www.unixtimestamp.com/

(@highisland)
Joined: 5 years ago

Eminent Member
Posts: 23

@chris I'm mostly wanting to clear out bot accounts that never logged in - would this be where posts = 0, comments = 0, and online_time = 0?

Chris
(@chris)
Joined: 3 years ago

Famed Member
Posts: 3627

@highisland,

Run this SQL:

DELETE u, um, p FROM `wp_users` u
   INNER JOIN `wp_wpforo_profiles` p ON p.`userid`   = u.`ID`
       LEFT JOIN `wp_usermeta`    um ON um.`user_id` = u.`ID`
WHERE p.`posts` = 0
AND u.`ID` NOT IN( SELECT DISTINCT `post_author` FROM `wp_posts` )
AND p.`online_time` IS NULL
(@highisland)
Joined: 5 years ago

Eminent Member
Posts: 23

@chris thanks - one more question - what does online_time having a value of 0 mean?

Page 1 / 2