Don't you hate it when someone re-registers with a different username, etc., when they already have an account and lots of posts, etc.?? []http://www.the-highway.com/Smileys/hairout.gif[/] I find this normally occurs when someone hasn't logged in for months and has forgotten their username, password and changed their e-mail address or some other reason. Gee, I never thought clicking on the "Contact Us" link and asking for help was that hard a thing to do, eh?

So, what I'm wanting to know is how to combine the two accounts without the person losing their "Title" and "# of posts", using either the old or new username. The easy scenario is when there are no new messages posted with the new account. Then you can simply delete that account and do a username change for the old account. But the situation I'm having to deal with mostly, is where the person re-registers and posts messages using that new account. Is there a way to merge these two accounts?

Jeff

Bump since I need to do the same thing...

OK - so we have (example) user#20 and user#30. We want to ditch user#30 and combine everything to user#20. (Untested - make backups )

YOu'd want to change anything in the AddressBook over
UPDATE w3t_AddressBook
SET Add_Owner = 20
WHERE Add_Owner = 30


UPDATE w3t_AddressBook
SET Add_Member = 20
WHERE Add_Member = 30


(This might make some duplicate Addressbook entries but they can just remove duplicates from MyHome).

Update the Boards table, in case #30 was the last poster in any forum
UPDATE w3t_Boards
SET Bo_Posterid = 20
WHERE Bo_Posterid = 30

If they own any calendar events
UPDATE w3t_Calendar
SET C_Owner = 20
WHERE C_Owner = 30

Update any favorites:
UPDATE w3t_Favorites
SET F_Owner = 20
WHERE F_Owner = 30


The Last Table could be tricky - we definately don't want any duplicate entries here. We're best to just ditch those entries for user#30. Worst that happens is their new post counts are wacked until they visit again. They can always "mark all read" and start fresh.

DELETE FROM w3t_Last
WHERE L_Uid = 30


UPdate any private messages
UPDATE w3t_Messages
SET M_Uid = 20
WHERE M_Uid = 30


UPDATE w3t_Messages
SET M_Sender = 20
WHERE M_Sender = 30


If they are moderators anywhere
UPDATE w3t_Moderators
SET Mod_Uid = 20
WHERE Mod_Uid = 30


Dont' worry about the online table, that's temporary data and it'll be removed after X amount of minutes anyway.

If they are a moderator (in 6.5) we also need to get rid of permissions for the user we are deleting.

DELETE FROM w3t_Permissions
WHERE P_Uid = 30



Update any polls
UPDATE w3t_PollVotes
SET P_Voter = 20
WHERE P_Voter = 30


Now we update any posts
UPDATE w3t_Posts
SET B_PosterId = 20
WHERE B_PosterId = 30


Merge any ratings for the bogus user
UPDATE w3t_Ratings
SET R_Rater = 20
WHERE R_Rater = 30

UPDATE w3t_Ratings
SET R_What = 20
WHERE R_What = 30 AND R_Type = u


Remove any subscriptions for user#30
DELETE FROM w3t_Subscribe
WHERE S_Uid = 30

Update any user notes (6.5)
UPDATE w3t_UserNotes
SET N_Uid = 20
WHERE N_Uid = 30



Now let's fine out how many posts they have (combined)
SELECT COUNT(*)
FROM w3t_Posts
WHERE B_PosterId = 20


Now we need to update the post count in user#20 to reflect our results from above

UPDATE w3t_Users
SET U_Totalposts = xxxxx
WHERE U_Number = 20



Hope I didn't miss anything. Untested, backup everything twice before monkeying in your database to this degree.

Too much of a hassle for a single user who can't keep track of his accounts. Thanks Josh, but I'm gonna pass on this one...

actually you can make this into a dandy mod...

Easier to dump the member..lol

easier not to have any members at all and just do mods for the enjoyment

I find it easier if I never even upcompress the ubbthreads zip file after I download it from infopop.

[]JoshPet said:
I find it easier if I never even upcompress the ubbthreads zip file after I download it from infopop. [/]
I usually code using only my sense of smell.

I usually code with my eyes closed, my ears covered, my mouth closed and a clothes pin over my nose. I tend to pass out after doing this for too long however, but I find that I create some very nice code during those stages where I'm deprived of oxygen for long periods of time.

LOL