Merge two SMF user accounts and effect on Joomla

[phil_roy]

Hi all,

I appreciate that this is probably a SMF issue as opposed to one that is related to the bridge, but given the fact that Joomla is added into the mix, I thought I had better post here.

Here's the situation....a user has been posting away and then one day (for whatever reason) can't login/forgot password etc and decides not to contact admin. Instead, they create a new account under and slight variant of their name and a different email address and start posting anew. It's nothing sinister or anything, they just did this rather than contact me.

Except, today, they had trouble again...and after a long chat, the user was using the "old" user name with the "new" account password.

It's going to make things so much easier to merge these two accounts. I've done some reading but only managed to find this thread that has a few hints but not all details....
http://www.simplemachines.org/community/index.php?topic=13738.0;all

What concerns me, is that even if someone posts how to merge, the Joomla part of things means that there are also two Joomla accounts that need to merged also. So I wonder if there is a better approach? I was thinking (if someone could advise me)....

* How to assign the posts of the "old" user account to the "new" account
* How to assign the 'join' and 'first logged in' dates of the "old" user account to the "new" account

This would then see their old posts and their important old information assigned to the new user account (is there any other important data I've left out)...and then simply, I could delete their old account completely (as the newer account has more active details), which the bridge would then also remove in Joomla.

Is that the best approach?

Cheers, Phil

[Kindred]

you can set a user's posts to a different user in phpmyadmin

UPDATE smf_messages SET `posterName`='newusername' WHERE `posterName`='oldusername';

UPDATE smf_messages SET `ID_MEMBER`='newuserID' WHERE `posterName`='newusername';

the other stuff can be modified directly by editting the specific record for the newusername in the smf_members table

editted---

[Kindred]

oops... I corrected the second bit of code...

[phil_roy]

Hmm, not getting notifications emails from this site for some reason?? Anyway, thanks for the correction. You've still got "poster_name" and it should be "posterName" I think??

I do note once I've done that, that the old user still has a post count against their name and the new user post count remains as it was. I tried doing forum maintenance to see if asking it to check stats would work, but it had no impact. If I go and ask to see the posts, I see all. So, old user had 49 (and it says that), new user has 16 (and says that) but if I go to see posts of new user I get 65.

I'm assuming I should just delete the old user at this stage and then as admin of SMF, I see I can go into a user's account and alter the number of posts value? Is this the best way?

Cheers,

Phil

PS...one other SQL command I think I should run after having a look....

UPDATE smf_messages SET `posterEmail`='activeemailaddress' WHERE `posterName`='newusername';

[Kindred]

yes, you are correct about the code... and you can either set the user's post count to the correct value in the admin section or  directly in the smf_members table

[phil_roy]

OK, thanks very much for your help Kindred 

Phil

[Kindred]

I actually have a user who keeps settinng up a new account and pretending to be someone new (for various, spurious reasons) and, being the all-seeing admin, I run those scripts to re-associate all of her old stuff with whatever the new account is (and then delete the old accounts)...

mostly because it amuses me...

[Eurosoft]

#### Merge Two Users ####
UPDATE smf_attachments SET ID_MEMBER = 'newmemberid' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_calendar SET ID_MEMBER = 'newmemberid' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_personal_messages SET ID_MEMBER_FROM = 'newmemberid' WHERE ID_MEMBER_FROM = 'oldmemberid';
UPDATE smf_pm_recipients SET ID_MEMBER = 'newmemberid' WEHRE ID_MEMBER = 'oldmemberid';
UPDATE smf_messages SET ID_MEMBER = 'newmemberid', posterName = 'newusername', posterEmail = 'newemailaddress' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_messages SET modifiedName = 'newusername' WHERE modifiedName = 'oldusername';
UPDATE smf_log_polls SET ID_MEMBER = 'newmemberid' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_polls SET ID_MEMBER = 'newmemberid' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_polls SET posterName = 'newusername' WHERE posterName = 'oldusername';
UPDATE smf_topics SET ID_MEMBER_STARTED = 'newmemberid' WHERE ID_MEMBER_STARTED = 'oldmemberid';
UPDATE smf_topics SET ID_MEMBER_UPDATED = 'newmemberid' WHERE ID_MEMBER_UPDATED = 'oldmemberid';
#### Merge Two Users ####

[en_shua]

#### Merge Two Users ####
UPDATE smf_attachments SET ID_MEMBER = 'newmemberid' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_calendar SET ID_MEMBER = 'newmemberid' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_personal_messages SET ID_MEMBER_FROM = 'newmemberid' WHERE ID_MEMBER_FROM = 'oldmemberid';
UPDATE smf_pm_recipients SET ID_MEMBER = 'newmemberid' WEHRE ID_MEMBER = 'oldmemberid';
UPDATE smf_messages SET ID_MEMBER = 'newmemberid', posterName = 'newusername', posterEmail = 'newemailaddress' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_messages SET modifiedName = 'newusername' WHERE modifiedName = 'oldusername';
UPDATE smf_log_polls SET ID_MEMBER = 'newmemberid' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_polls SET ID_MEMBER = 'newmemberid' WHERE ID_MEMBER = 'oldmemberid';
UPDATE smf_polls SET posterName = 'newusername' WHERE posterName = 'oldusername';
UPDATE smf_topics SET ID_MEMBER_STARTED = 'newmemberid' WHERE ID_MEMBER_STARTED = 'oldmemberid';
UPDATE smf_topics SET ID_MEMBER_UPDATED = 'newmemberid' WHERE ID_MEMBER_UPDATED = 'oldmemberid';
#### Merge Two Users ####

Sorry to ask what probably is a silly question, but does this update the post count as well?

[Kindred]

no. If you look at the script, you'll notice that it does not touch the members table at all.

[en_shua]

So, would I also have to include what you posted at the top, as well as something that would add the two post counts together?

[Kindred]

eurosoft's code does include the code that I used...   I just never bothered with anything beyond the posts (I don't let users add events or polls)

and I always just updated the post count manually.  This is really just a one-time combine, in most cases.

[en_shua]

Good point.  Since I have almost a hundred users I need to merge, I guess I was looking for a script I could run to make it easier.