Merging Users

Lefteris_D · December 08, 2004, 06:28:35 PM

One of the forums I help run has a few users that have several accounts active at the same time. Recently they have asked me if I could merge some of their accounts together and I told them that I would try.

I tried to do it with an Invision forum in the past but the result was that my script messed up the user accounts. Thank god I had a database backup.

I have no wish to do that with an SMF installation since I'm not used to the database structure yet so I'd love if some of you can help with the SQL queries.

Right now I'm thinking of a simple script with a form where you would enter the destination user ID and the ID that will be merged to it(or multiple ID's?).

The script would then perform each stage of the merging individually(posts, PM, Threads etc) much like the SMF converters in the download page.

I hope you can help.

[Unknown] · December 09, 2004, 01:40:47 AM

Well, I don't currently have the time to write a script, but... it would really only involve the tables that have ID_MEMBER on them - which is a lot, but it could be worse.

These are, from memory (may not be complete)... log_polls, log_topics, log_mark_read, log_notify, log_boards, instant_messages, topics, messages, im_recipients, banned, attachments, moderators, calendar, collapsed_categories, log_actions, log_banned, log_errors, polls, and themes.

Now, I know that's a lot (most of the tables, in fact!) It's as simple as replacing one value with another, like so:

UPDATE table_name
SET ID_MEMBER = new
WHERE ID_MEMBER = old;
(or ID_MEMBER_UPDATED, etc.)

And of course delete them from the members table... obviously, a backup is a very good idea.

-[Unknown]

Lefteris_D · December 09, 2004, 03:16:38 AM

Last night I installed a backup of the forum on my localhost so that means I get to have 1500 user accounts and 20000+ posts to use for testing

I started my script by including SSI.php and ManageMembers.php

So far executing

Code Select

UPDATE `smf_instant_messages` SET ID_MEMBER_FROM=$destinationid WHERE ID_MEMBER_FROM=$sourceid
UPDATE `smf_topics` SET ID_MEMBER_STARTED=$destinationid WHERE ID_MEMBER_STARTED=$sourceid

from phpMyAdmin has been a success. All the posts made by the user ID's that I selected were gived to the account I specified. The display name in the posts changed on it's own, no query needed.

Today I plan to test the instant messages and the attachments.

From what I saw using the forum delete member option or calling deleteMembers($users) since ManageMembers.php is included in the script should eliminate any traces of the old account like callendar entries.

Lefteris_D · December 09, 2004, 06:54:07 AM

Ok... I have something. I know that the code is a mess but i'll tweak it and everyone is free to help.

What it does is assign topics, posts and attachments to the specified user. Then it calls the forum's deleteMembers() function and wipes out the remaining old member data.

Currently supports:

User topics
User posts
User callendar events
User Attachments
Private Messages

The code is provided without support. Make a database backup!

Code Select

<?php

require(dirname(__FILE__) . '/SSI.php');
require_once($sourcedir . '/ManageMembers.php');

// Check if user is Administrator
if (!$context['allow_admin'])
{
echo 'A valid administrator forum session is required to access this page.';
exit;
}

?>

<html>
<home>
<title>SMF User Merging Script</title>
</home>
<body>
<br />
<table border="1" width="800" align="center" bgcolor="lightgray">
<tr>
<td align="center"><b>SMF User Merging Script</b></td>
</tr>
<?php
if (isset($_POST['step'])) {
//Continue
} else {
?>
<tr>
<td>
This script will allow a forum administrator to merge two users into one. The script currently supports:
<ul>
<li>User topics</li>
<li>User posts</li>
<li>User callendar events</li>
<li>User Attachments</li>
<li>Private Messages</li>
</ul>
Everything else shall be removed once the "source user" has been deleted. To perform the operation you need the source user and destination user ID's.
<br />
</br />
<span style="color: darkred;"><b>Please Note:</b> This script comes with no support and it might damage your forum. A database backup before continuing is suggested. Also make sure that both user ID's are valid.</span>
<br />
<br />
<div align="center"><FORM action="smf-merge-users.php" method="post"><INPUT type="hidden" name="step" value="dataform"><INPUT type="submit" value="Begin"></FORM></div>
</td>
</tr>
<?php
exit;
}

if ($_POST['step'] == 'dataform')
{
// ok
}
else
{
// Catch 0 bug
if ($_POST['destinationid'] == '0')
{
echo '<tr><td>';
echo '<div align="center"><b>Warning:</b> Destination User ID cannot be 0</div>';
echo '</td></tr>';
echo '</table></body></html>';
exit;
}
else
{
// do nothing
}
if ($_POST['sourceid'] == '0')
{
echo '<tr><td>';
echo '<div align="center"><b>Warning:</b> Source User ID cannot be 0</div>';
echo '</td></tr>';
echo '</table></body></html>';
exit;
}
else
{
// do nothing
}

// $_POST['destinationid'] and $_POST['sourceid'] must not be the same
if ($_POST['destinationid'] == $_POST['sourceid'])
{
echo '<tr><td>';
echo '<div align="center"><b>Warning:</b> Destination User and Source User cannot be the same</div>';
echo '</td></tr>';
echo '</table></body></html>';
exit;
}
else
{
// do nothing
}

if ($_POST['sourceid'] == '0')
{
echo '<tr><td>';
echo '<div align="center"><b>Warning:</b> Source User ID cannot be 0</div>';
echo '</td></tr>';
echo '</table></body></html>';
exit;
}
else
{
// do nothing
}

// Make sure $_POST['destinationid'] is not null
if ($_POST['destinationid'] == NULL)
{
echo '<tr><td>';
echo '<div align="center"><b>Warning:</b> Destination User ID cannot be empty</div>';
echo '</td></tr>';
echo '</table></body></html>';
exit;
}
else
{
// do nothing
}

// Make sure $_POST['sourceid'] is not null
if ($_POST['sourceid'] == NULL)
{
echo '<tr><td>';
echo '<div align="center"><b>Warning:</b> Source User ID cannot be empty</div>';
echo '</td></tr>';
echo '</table></body></html>';
exit;
}
else
{
// do nothing
}
}
// Let's begin with the steps!

// Data Form (Step 0)
if ($_POST['step'] == 'dataform')
{
echo '<tr><td align="center">Please insert the required information.<br />';
echo '<FORM action="smf-merge-users.php" method="post">Source User(will be deleted): <INPUT type="text" name="sourceid" SIZE="5" MAXLENGTH="5"><br />Destination User(will remain intact)<INPUT type="text" name="destinationid" SIZE="5" MAXLENGTH="5"><br /><INPUT type="hidden" name="step" value="mergethreads"><BR><INPUT type="submit" value="Click to proceed to merge topics"></FORM>';
echo '</td></tr>';
}
else
{
// do nothing
}



// Topic Merging Operation (Step 1)
if ($_POST['step'] == 'mergethreads')
{
$dstid = $_POST['destinationid'];
$srcid = $_POST['sourceid'];

echo '<tr><td><div align="center">Step 1: Μerging Topics<br />';
// Add the SQL code here
db_query("UPDATE {$db_prefix}topics SET ID_MEMBER_STARTED=$dstid WHERE ID_MEMBER_STARTED=$srcid", __FILE__, __LINE__);
db_query("UPDATE {$db_prefix}topics SET ID_MEMBER_UPDATED=$dstid WHERE ID_MEMBER_UPDATED=$srcid", __FILE__, __LINE__);
?>
<FORM action="smf-merge-users.php" method="post"><INPUT type="hidden" name="sourceid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['sourceid']; ?>"><INPUT type="hidden" name="destinationid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['destinationid']; ?>"><br /><INPUT type="hidden" name="step" value="mergeposts"><INPUT type="submit" value="Click to proceed to merge posts"></FORM>
<br />
<?php
echo '</div></td></tr>';
}
else
{
// do nothing
}


// Post Merging Operation (Step 2)
if ($_POST['step'] == 'mergeposts')
{
$dstid = $_POST['destinationid'];
$srcid = $_POST['sourceid'];

echo '<tr><td><div align="center">Step 2: Merging posts<br />';
// Add the SQL code here
db_query("UPDATE {$db_prefix}messages SET ID_MEMBER='$dstid' WHERE ID_MEMBER='$srcid'", __FILE__, __LINE__);
?>
<FORM action="smf-merge-users.php" method="post"><INPUT type="hidden" name="sourceid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['sourceid']; ?>"><INPUT type="hidden" name="destinationid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['destinationid']; ?>"><br /><INPUT type="hidden" name="step" value="mergeattachments"><INPUT type="submit" value="Click to proceed to merge attachments"></FORM>
<br />
<?php
echo '</div></td></tr>';
}
else
{
// do nothing
}


// Merge Attachments (Step 3)
if ($_POST['step'] == 'mergeattachments')
{
$dstid = $_POST['destinationid'];
$srcid = $_POST['sourceid'];
echo '<tr><td><div align="center">Step 3: Merging attachments<br />';
// Add the SQL code here
db_query("UPDATE {$db_prefix}attachments SET ID_MEMBER='$dstid' WHERE ID_MEMBER='$srcid'", __FILE__, __LINE__);
?>
<FORM action="smf-merge-users.php" method="post"><INPUT type="hidden" name="sourceid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['sourceid']; ?>"><INPUT type="hidden" name="destinationid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['destinationid']; ?>"><br /><INPUT type="hidden" name="step" value="mergepm"><INPUT type="submit" value="Merge Private Messages"></FORM>
<br />
<?php
echo '</div></td></tr>';
}
else
{
// do nothing
}

// Merge Private Messages (Step 4)
if ($_POST['step'] == 'mergepm')
{
$dstid = $_POST['destinationid'];
$srcid = $_POST['sourceid'];
echo '<tr><td><div align="center">Step 4: Merging Private Messages<br />';
// Add the SQL code here
db_query("UPDATE {$db_prefix}instant_messages SET ID_MEMBER_FROM='$dstid' WHERE ID_MEMBER_FROM='$srcid'", __FILE__, __LINE__);
db_query("UPDATE {$db_prefix}im_recipients SET ID_MEMBER='$dstid' WHERE ID_MEMBER='$srcid'", __FILE__, __LINE__);
?>
<FORM action="smf-merge-users.php" method="post"><INPUT type="hidden" name="sourceid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['sourceid']; ?>"><INPUT type="hidden" name="destinationid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['destinationid']; ?>"><br /><INPUT type="hidden" name="step" value="mergecallevents"><INPUT type="submit" value="Click to merge callendar events"></FORM>
<br />
<?php
echo '</div></td></tr>';
}
else
{
// do nothing
}

// Merge Callendar Events (Step 5)
if ($_POST['step'] == 'mergecallevents')
{
$dstid = $_POST['destinationid'];
$srcid = $_POST['sourceid'];
echo '<tr><td><div align="center">Step 3: Merging callendar events<br />';
// Add the SQL code here
db_query("UPDATE {$db_prefix}calendar SET ID_MEMBER='$dstid' WHERE ID_MEMBER='$srcid'", __FILE__, __LINE__);
?>
<FORM action="smf-merge-users.php" method="post"><INPUT type="hidden" name="sourceid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['sourceid']; ?>"><INPUT type="hidden" name="destinationid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['destinationid']; ?>"><br /><INPUT type="hidden" name="step" value="deluser"><INPUT type="submit" value="Click to delete user"></FORM>
<br />
<?php
echo '</div></td></tr>';
}
else
{
// do nothing
}

// Delete user and remaining data (Step 6)
if ($_POST['step'] == 'deluser')
{
echo '<tr><td><div align="center">Step 6: Deleting User and remaining data<br />';
$users = $_POST['sourceid'];
// Kill the source user
deleteMembers($users)
?>
<FORM action="smf-merge-users.php" method="post">
<INPUT type="hidden" name="sourceid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['sourceid']; ?>">
<INPUT type="hidden" name="destinationid" SIZE="5" MAXLENGTH="5" value="<?php echo $_POST['destinationid']; ?>">
<br />
<INPUT type="hidden" name="step" value="showfinal"><INPUT type="submit" value="Continue"></FORM>
</div><br /></td></tr>
<?php
}
else
{
// do nothing
}

// Show Final Message (Step 7)
if ($_POST['step'] == 'showfinal')
{
echo '<tr><td><br /><div align="center">The source user has been deleted!<br />Please check your forum to ensure that the user merging was a success.<br /><br /><a href="smf-merge-users.php" target="_self">Back To Start</a><br /><br /></div></td></tr>';
}
else
{
// do nothing
}
?>
</table>
</body>
</html>

I look forward for your comments.

Edit: Added some code to prevent uning a user id 0 that will mess the forum and a null value. Also make sure the user ID's you plan to use are correct.

Edit: Sorry, just made the PHP color-coded (using full ?php not just ?) -[Unknown]

Edit: Code updated! Now uses SMF query system & supports more data.

NoRad · December 21, 2004, 10:19:06 AM

What exactly does this do? I'd like to merge two forums, or at least just take 1 database of users and import it to the bigger of the two sites. I have some categories that are unique per forum that I'd like to bring over in whole, and others that are the same that I'd like to merge. Any ideas?

Lefteris_D · December 22, 2004, 04:29:30 AM

QuoteWhat exactly does this do? I'd like to merge two forums, or at least just take 1 database of users and import it to the bigger of the two sites. I have some categories that are unique per forum that I'd like to bring over in whole, and others that are the same that I'd like to merge. Any ideas?

What you need is some kind of importer that will handle that, [Unknown] might know something about it.

All this script does is merge one user account with another.

NoRad · December 22, 2004, 11:49:12 AM

Ohhh ok! I needed this feature too at one point or another. Thanks

en_shua · April 03, 2008, 10:11:59 AM

Is this script still in working order?

en_shua · September 30, 2008, 12:23:08 PM

Well, I tried to modify the code and correct the bad tables, etc, however I was presented with "Hacking Attempt" by SMF at the Merging Posts section.

News:

Merging Users