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?
@scarlet ,
You can delete inactive users in Dashboard > Forums > Members admin page, just filter users by "inactive", select all and delete.
Hi, thank you for your info but this feature is not work.
I see only 4 users in the inactive members section.
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.
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?
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.
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:
- wp_users
- wp_usermeta
- wp_wpforo_profiles
@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?
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/
@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?
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
@chris thanks - one more question - what does online_time having a value of 0 mean?