Removing users with an SQL statement

This forum is now closed as part of retiring phpBB2.
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

This forum is now closed due to phpBB2.0 being retired.
Post Reply
User avatar
Nux
Registered User
Posts: 280
Joined: Tue May 18, 2004 3:14 pm
Location: 3cities, Poland
Contact:

Removing users with an SQL statement

Post by Nux »

Hi.

I have made myself this script... And I just wonder if anyone could tell me if this is correct:

Code: Select all

DELETE forum_groups, forum_user_group, forum_users
 FROM forum_groups g, forum_user_group ug, forum_users u
 WHERE u.user_id IN (#user_ids_list#) AND u.user_active=0 AND u.user_id>10 AND ug.user_id=u.user_id AND g.group_id=ug.group_id AND g.group_description="Personal User" AND u.user_posts+u.user_new_privmsg+u.user_unread_privmsg+u.user_last_privmsg = 0;
I mean I want to delete all table rows connected with selected users and check if they were activated or have any post or PMs (just to be sure). I already checked that it works (or seems too), I just wanted to ask if anyone see anything that I missed out?

BTW: If anyone want that script working on Greasemonkey just let me know.
My page: DOM, Javascript, SQL - tips&scripts (mostly in Polish, sorry ;))
Afkamm
Registered User
Posts: 178
Joined: Fri Sep 06, 2002 6:57 pm
Location: Britain
Contact:

Re: Removing users with an SQL statement

Post by Afkamm »

Take a look at the file admin/admin_users.php. You'll see the code that's involved when a user is deleted as a lot of tables are affected.

3 Select queries, 4 Update queries and 10 Delete queries.

There are several MODs available that allow you to mass delete users the proper way. They'll display the user's reg date, last visit date, last post date etc. Very handy. :)
Marc :)
User avatar
drathbun
Former Team Member
Posts: 12204
Joined: Thu Jun 06, 2002 3:51 pm
Location: TOPICS_TABLE
Contact:

Re: Removing users with an SQL statement

Post by drathbun »

Multi-table deletes can be difficult to debug and may not be 100% portable. The admin panel deletes from a single table at a time. On databases that support it a transaction is started and closed in order to maintain database consistency, which is probably better than trying to run a mutil-table delete query.

That's just my opinion.
I blog about phpBB: phpBBDoctor blog
Still using phpbb2? So am I! Click below for details
Image
User avatar
Nux
Registered User
Posts: 280
Joined: Tue May 18, 2004 3:14 pm
Location: 3cities, Poland
Contact:

Re: Removing users with an SQL statement

Post by Nux »

Afkamm wrote: Take a look at the file admin/admin_users.php. You'll see the code that's involved when a user is deleted as a lot of tables are affected.

3 Select queries, 4 Update queries and 10 Delete queries.

There are several MODs available that allow you to mass delete users the proper way. They'll display the user's reg date, last visit date, last post date etc. Very handy. :)


I've only checked the registration process, thanks for the suggestion. As for queries... Well they are mostly for updating stuff where user posted some messages or things like that. The users I remove are not yet activated, so that is not my case.

I know there are MODs for that I just have problems with my FTP... Well that's kinda complicated by I do this through ACP.
My page: DOM, Javascript, SQL - tips&scripts (mostly in Polish, sorry ;))
Afkamm
Registered User
Posts: 178
Joined: Fri Sep 06, 2002 6:57 pm
Location: Britain
Contact:

Re: Removing users with an SQL statement

Post by Afkamm »

I recently installed a "mass user delete" mod and changed it to suit my own needs. Displays the username, email, website, join date, last visit date, last post date, posts total and whether their account is active or not. The list can be ordered using any of the columns. Quite handy. :)
Marc :)
Post Reply

Return to “[2.0.x] MOD Writers Discussion”