TIP: Mass Delete inactive users with no posts

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
User avatar
mamba
Registered User
Posts: 603
Joined: Thu Jan 16, 2003 7:59 pm
Location: Australia

TIP: Mass Delete inactive users with no posts

Post by mamba »

This command, issued in phpmyadmin, will get rid of the rubbish on your users file: the people who never completed their registrations (inactive) and have never made any posts (as opposed to those who are inactive now but were once active and do have posts). The code also protects against the deletion of the Anonymous User. I have also built in a limit so that you can avoid deleting people who recently partially registered and may still become active.

Code: Select all

DELETE FROM `phpbb_users` WHERE (`user_posts`=0 AND `user_active`=0 AND `user_id`!=-1 AND `user_id` < [number])
Take a backup first!

Note: change [number] to some thing like 8768 (no square brackets) based on your table's most recent user_id values, so as not to delete recent partial registrations.

This code means you do not have to install an irritating PRUNE USER MOD.
User avatar
noth
Registered User
Posts: 2528
Joined: Fri Jan 07, 2005 7:10 pm
Location: North Surrey
Contact:

Post by noth »

that is massive mamba

I used

Code: Select all

DELETE FROM `nphpbb_users` WHERE `user_posts`=0
to rip EIGHTY EIGHT PAGES of Russian 'members'

SUPERB!!!! :mrgreen:
popmonkey
Registered User
Posts: 2
Joined: Wed Apr 13, 2005 3:29 pm

Post by popmonkey »

here's another approach based on mamba's idea:

Code: Select all

delete from phpbb_users where user_posts=0 and user_active=0 and user_id!=-1 and from_unixtime(user_regdate) < now() - interval '1' day;
gets rid of anyone who has started registration over a day ago and still hasn't activated. any real user is likely to have registered as soon as possible. less guessing games with user_ids, you can cut & paste directly or even run as a cronjob (as i do).

adjust the interval if you feel it's too short (or too long, in my case i use interval '6' hours, i expect anyone registering in my forum to complete their registration within a few minutes and if it fails well, they can always try again in 6 hours...)
PanTzeR
Registered User
Posts: 32
Joined: Mon Sep 05, 2005 9:42 am
Contact:

Post by PanTzeR »

Thanks guys, I will try this and wondering why it is not included in the same manner as the "forum clean up" feature.
espicom
Registered User
Posts: 17905
Joined: Wed Dec 22, 2004 1:14 am
Location: Woodstock, IL

Post by espicom »

Deleting users this way leaves stuff behind in the forum... namely the single-user user groups created for each user.

It's much safer to use the Starfoxtj Tool Kit to fix a lot of Admin-related problems, which has the ability to sort users by last access, number of posts, etc., and do mass deletes SAFELY, including cleaning up after itself.
Jeff
Fixing 1016/1030/1034 Errors | (obsolete link) | MySQL 4.1/5.x Client Error | phpBBv2 Logo in ACP
Support requests via PM are ignored!
"To be fully alive is to feel that everything is possible." - Eric Hoffer
NoDeity
Registered User
Posts: 150
Joined: Thu Jul 17, 2003 9:43 am
Name: Brad Reddekopp

Post by NoDeity »

espicom wrote: Deleting users this way leaves stuff behind in the forum... namely the single-user user groups created for each user.

It's much safer to use the Starfoxtj Tool Kit to fix a lot of Admin-related problems, which has the ability to sort users by last access, number of posts, etc., and do mass deletes SAFELY, including cleaning up after itself.


That link gives me a "Unable to connect".
User avatar
-jm-
Former Team Member
Posts: 2025
Joined: Fri Jul 16, 2004 10:56 am
Location: Inside the mind of the machine
Contact:

Post by -jm- »

NoDeity wrote:
espicom wrote:Deleting users this way leaves stuff behind in the forum... namely the single-user user groups created for each user.

It's much safer to use the Starfoxtj Tool Kit to fix a lot of Admin-related problems, which has the ability to sort users by last access, number of posts, etc., and do mass deletes SAFELY, including cleaning up after itself.


That link gives me a "Unable to connect".

there are server troubles over there ... feel free to use this mirror
-jm- (a.k.a. juanm) - *NO* private support
Hacked?
With so many beautiful colors in the world it’s a shame to make everything black and white - Dennis R. Little
my links: tips&stuff :: stuff only
User avatar
karlsemple
Former Team Member
Posts: 39802
Joined: Mon Nov 01, 2004 8:54 am
Location: Hereford, UK
Contact:

Post by karlsemple »

I am sorry using phpmyadmin to delete users from the phpbb_users table is wrong as it does not remove everything relating to the member being deleted. Unless you know what you are doing please do not post such advice and certainly do not use it :) If you want to mass delete members please use a mod which does just that, but does it properly removing everything. Seens as there was not a support request here and the original posts were bad advice i am closing this one



Locked!
Image
Locked

Return to “2.0.x Support Forum”