Previous Thread
Next Thread
Print Thread
Hop To
Joined: Jun 2006
Posts: 215
enthusiast
enthusiast
Joined: Jun 2006
Posts: 215
Hi
I wonder how to 'safely' delete an user in SQL command ?

My site have thousands of inactive users , that is , no post , no PMs ... I want to delete these users , but not by web GUI (it is too inefficient)

I want to find user with ubbt_USER_PROFILE.USER_TOTAL_POSTS = 0 and ubbt_USER_PROFILE.USER_TOTAL_PM = 0 ,

Can I safely delete him just in ubbt_USERS ?

Code
select USER_ID , 
       USER_LOGIN_NAME , 
       USER_DISPLAY_NAME ,
       USER_REGISTRATION_EMAIL ,
       count(USER_REGISTRATION_EMAIL) as c
       from ubbt_USERS 
       join ubbt_USER_PROFILE using (USER_ID)
       where ubbt_USER_PROFILE.USER_TOTAL_POSTS = 0
       and   ubbt_USER_PROFILE.USER_TOTAL_PM = 0       
       group by USER_REGISTRATION_EMAIL
       having ( count(USER_REGISTRATION_EMAIL)  > 1)
       order by USER_ID;

UBBT 7.5.2


English is not my native language. I try my best to express my thought precisely. I hope you understand what I mean. If any misunderstanding results from culture gaps, I apologize first.
Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
If it were me I would use the advance search in the control panel for 0 posts and delete them from there.
At least if a problem pops up it would be a bug related problem. Not one from trying a work around.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
you'd need to clear out USER_PROFILE too (parallel as implied by your join)

also, you prolly want to remove them from the USER_GROUPS table, the ONLINE table, USER_DATA, RATINGS, DISPLAY_NAMES, WATCH_LISTS, PROFILE_COMMENTS, CALENDAR_EVENTS

soooo, i'd just really recommend you search for all users with posts 0 in the Admin cPanel and delete em all from there.. it does a chunk of 20 or so at a time, so even if you have a timeout, it can be restarted..

your call wink

Joined: Jun 2006
Posts: 215
enthusiast
enthusiast
Joined: Jun 2006
Posts: 215
Originally Posted by Ruben
If it were me I would use the advance search in the control panel for 0 posts and delete them from there.
At least if a problem pops up it would be a bug related problem. Not one from trying a work around.
Thanks , but users with 0 posts may have PMs...


English is not my native language. I try my best to express my thought precisely. I hope you understand what I mean. If any misunderstanding results from culture gaps, I apologize first.
Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
then i'd just change the query in admin cpanel to also add an option for 0 PMers too wink

membersearch.php checks for 0 posters

PHP Code
$extra .= " and t2.USER_TOTAL_POSTS = '0' "; 

becomes

PHP Code
$extra .= " and t2.USER_TOTAL_POSTS = '0' and t2.USER_TOTAL_PMS = '0' "; 

you can verify returned results as being valid against your query above wink

Note: also, a lot of your 0 posters may prolly have at LEAST one PM.. namely the welcome message, so i personally would just say 'screw it' and remove all 0 posters who haven't visited the board within {insert time frame here} and *poof* gonzo wink

Joined: Dec 2003
Posts: 6,560
Likes: 78
Joined: Dec 2003
Posts: 6,560
Likes: 78
Do you really care?
If you also use the advanced search for say two years since registered.
If a user has never posted and they have a couple of Private messages, Then... Poof gone.

They can always register again if they just use private topics.

You could also add a announcement that you are pruning members that never post before doing so.


Blue Man Group
There is no such thing as stupid questions. Just stupid answers
Joined: Mar 2007
Posts: 522
Addict
Addict
Joined: Mar 2007
Posts: 522
I go by zero posts and last login date. If the "zero posters" haven't logged in in over four years (or whatever), I couldn't care less if they have private threads in the system. I haven't had a complaint yet. I usually delete a thousand or more at a time, and it doesn't take that long.


Steve

UBB.classic from 2000-2003
UBB.threads from 2003-present!
Joined: Jun 2006
Posts: 215
enthusiast
enthusiast
Joined: Jun 2006
Posts: 215
Yes , I do care ...

Well , in fact , I need to clean up duplicated accounts with same USER_REGISTRATION_EMAIL .

I use UBBT since WWWThreads.perl , it's about more than 10 years.
I didn't forbid users to use one email to register multiple accounts.

It was fine , though there are a lot of dummy accounts there.
But recently I looked into Facebook Connect , it [optionally] demands the connecting site with "one email with one account" policy
see here : http://wiki.developers.facebook.com/index.php/Connect.registerUsers

It means one site can first bulk feed all of its account's hashed emails and user_id to facebook
hashed email is :
Code
CONCAT(CRC32(LOWER(TRIM(ubbt_USERS.USER_REGISTRATION_EMAIL))) ,'_' ,MD5(LOWER(TRIM(ubbt_USERS.USER_REGISTRATION_EMAIL)))) as emailHash

Because my site didn't follow unique email policy until last year , I have to "clean up" a lot of unused accounts and merge users with duplicated USER_REGISTRATION_EMAIL .

Rick said he will be doing some stuff with Facebook , but didn't see the schedule yet . I cannot wait the not-knowing-when-will-arrive UBBT8 , I've to connect to FB by myself , that's why I have to do clean these accounts...


English is not my native language. I try my best to express my thought precisely. I hope you understand what I mean. If any misunderstanding results from culture gaps, I apologize first.
Joined: Apr 2007
Posts: 3,940
Likes: 1
SD Offline
Former Developer
Former Developer
Joined: Apr 2007
Posts: 3,940
Likes: 1
if you care, you can look @ all the tables i mentioned and delete according to matching USER_ID OR modify membersearch.php

good luck smile

Joined: Jun 2006
Posts: 215
enthusiast
enthusiast
Joined: Jun 2006
Posts: 215
I found USER_TOTAL_PM is wrong...
An user with Private Messages , his USER_TOTAL_PM is still 0 , why ?


English is not my native language. I try my best to express my thought precisely. I hope you understand what I mean. If any misunderstanding results from culture gaps, I apologize first.
Joined: Jun 2006
Posts: 215
enthusiast
enthusiast
Joined: Jun 2006
Posts: 215
I am confused with the three tables : ubbt_PRIVATE_MESSAGE_POSTS , ubbt_PRIVATE_MESSAGE_TOPICS , ubbt_PRIVATE_MESSAGE_USERS

How do I know if a user doesn't have any incoming PM and outgoing PM (except Admin's welcome message , Admin's ID = 2) ?
how to write such SQL message ?

BTW , the PMs in ubbt_USER_PROFILE.USER_TOTAL_PM is not synchronized with the real value , should it be considered as a bug ?


English is not my native language. I try my best to express my thought precisely. I hope you understand what I mean. If any misunderstanding results from culture gaps, I apologize first.
Joined: Aug 2006
Posts: 1,358
Y
Veteran
Veteran
Y Offline
Joined: Aug 2006
Posts: 1,358
Originally Posted by smallufo
BTW , the PMs in ubbt_USER_PROFILE.USER_TOTAL_PM is not synchronized with the real value , should it be considered as a bug ?

USER_TOTAL_PM holds the number of unread PM's.

Anyway, if you would require such cleanup procedures, I would opt not to just collect a bunch of queries, but to write some code for it.

You could break up a complex problem in very easy little steps.


[Linked Image from siemons.org]
Joined: Jan 2010
Posts: 25
J
newbie
newbie
J Offline
Joined: Jan 2010
Posts: 25
That's a bit tough to do with a single SQL query. If they have no Private Messages in their inbox, then they will have no entries for their userid in the ubbt_PRIVATE_MESSAGE_USERS table.

If they have sent something and it's sitting in someone elses inbox then there will be an entry in the ubbt_PRIVATE_MESSAGE_TOPICS for their user id in the USER_ID field.

And yes, from talking with Rick there have been a few problems with the numbers getting out of sync, which are supposed to be recalculated when that user logs in. We'll be able to fix the source of the problem in v8.

Joined: Jun 2006
Posts: 215
enthusiast
enthusiast
Joined: Jun 2006
Posts: 215
I am not skilled in SQL , and I've spent a lot of time writing non-executable SQL cry
I almost give up...

Can anybody write some SQL code to grab the old/inactive users that fulfill aLL of these criterias :

  • ubbt_USER_PROFILE.USER_TOTAL_POSTS = 0
  • ubbt_USERS.USER_REGISTERED_ON prior to 2007/1/1
  • ubbt_USER_DATA.USER_LAST_VISIT_TIME prior to 2007/1/1
  • User never wrote a message : USER_ID should not in ubbt_PRIVATE_MESSAGE_TOPICS.USER_ID . This line is very slow , I don't know how to speed it up.
  • User with zero or only one PM (from Admin's welcome message , admin USER_ID = 2 ) I cannot write this one , either crazy


Code
select USER_ID , 
 USER_LOGIN_NAME , 
 USER_DISPLAY_NAME ,
 USER_REGISTRATION_EMAIL ,
 FROM_UNIXTIME(USER_REGISTERED_ON) as regTime ,
 FROM_UNIXTIME(ubbt_USER_DATA.USER_LAST_VISIT_TIME) as lastTime

 from ubbt_USERS 
 join ubbt_USER_PROFILE using (USER_ID)
 join ubbt_USER_DATA using (USER_ID)
 where 
 USER_REGISTERED_ON < UNIX_TIMESTAMP('2007-01-01 00:00:00')
 and ubbt_USER_DATA.USER_LAST_VISIT_TIME < UNIX_TIMESTAMP('2007-01-01 00:00:00')
 and ubbt_USER_PROFILE.USER_TOTAL_POSTS = 0
 and USER_ID not in (select distinct USER_ID from ubbt_PRIVATE_MESSAGE_TOPICS)
 group by USER_ID
 order by USER_ID;
This is what I could achieve , it doesn't find out "users with zero or only one PM from Admin" frown

It would be better if it can be accomplished in one SELECT command , so that I can insert the code to membersearch.php .

Thanks a lot !!


English is not my native language. I try my best to express my thought precisely. I hope you understand what I mean. If any misunderstanding results from culture gaps, I apologize first.
Joined: Jan 2010
Posts: 34
newbie
newbie
Joined: Jan 2010
Posts: 34
I'm not too sure if you can get that into a single query. I'll test a bit this evening and see if I can come up with something, but with the tables you're needing to look through, just not sure it's possible.

Joined: Jun 2006
Posts: 215
enthusiast
enthusiast
Joined: Jun 2006
Posts: 215
Well , it's not important anymore...

I've solved this problem , by creating another temp table , inserting all USER_ID that has PM in their box...
And then modify the query in membersearch.php , looking up USER_ID not in that temp table ...
It works...


English is not my native language. I try my best to express my thought precisely. I hope you understand what I mean. If any misunderstanding results from culture gaps, I apologize first.

Link Copied to Clipboard
ShoutChat
Comment Guidelines: Do post respectful and insightful comments. Don't flame, hate, spam.
Recent Topics
spam issues
by ECNet - 03/19/2024 11:45 PM
Looking for a forum
by azr - 03/15/2024 11:26 PM
Editing Links in Post
by Outdoorking - 03/15/2024 9:31 AM
Question on barkrowler and the like
by Mors - 02/29/2024 6:51 PM
Member Permissions Help
by domspeak - 02/27/2024 6:31 PM
Who's Online Now
0 members (), 744 guests, and 147 robots.
Key: Admin, Global Mod, Mod
Random Gallery Image
Latest Gallery Images
Los Angeles
Los Angeles
by isaac, August 6
3D Creations
3D Creations
by JAISP, December 30
Artistic structures
Artistic structures
by isaac, August 29
Stones
Stones
by isaac, August 19
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20230217)