|
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 ?
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
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
|
|
|
|
Joined: Jun 2006
Posts: 215
enthusiast
|
enthusiast
Joined: Jun 2006
Posts: 215 |
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
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 membersearch.php checks for 0 posters $extra .= " and t2.USER_TOTAL_POSTS = '0' ";
becomes $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 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
|
|
|
|
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.registerUsersIt means one site can first bulk feed all of its account's hashed emails and user_id to facebook hashed email is :
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
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
|
|
|
|
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
Veteran
|
Veteran
Joined: Aug 2006
Posts: 1,358 |
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.
|
|
|
|
Joined: Jan 2010
Posts: 25
newbie
|
newbie
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 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
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" 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.
|
|
|
0 members (),
744
guests, and
147
robots. |
Key:
Admin,
Global Mod,
Mod
|
|
|
|