MyBB Community Forums

Anyone know the propper SQL command to merge all user accounts with one?

Some thing like; You've to run all these in phpmyadmin.

UPDATE `mybb_users` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_adminlog` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_announcements` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_events` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_threadsubscriptions` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_forumsubscriptions` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_joinrequests` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_moderatorlog` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_pollvotes` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_posts` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_privatemessages` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_threadratings` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_threads` SET uid = 'X' WHERE uid IN(3,4,5);
UPDATE `mybb_sessions` SET uid = 'X' WHERE uid IN(3,4,5);

Change X to the UserID you want to merge into, and Change 3,4,5 to the User IDs yu wish to Merge to X. You may add hundreds of UserIDs just you need to separate each with ,

Thanks Yaldraram.

No problem =)

Create a new file in your MyBB root folder and paste in this:

<?php

define('IN_MYBB', true);
define('NO_ONLINE', true);
require_once "./global.php";

$x = 1;

$query = $db->query("SHOW TABLES");
while($table = $db->fetch_array($query))
{
	$table = current($table);
	
	$q = $db->query("SHOW COLUMNS FROM {$table} LIKE 'uid'");
	if($db->num_rows($q) > 0)
	{
		$key = $db->fetch_field($q, 'Key');
		if($key == 'PRI')
		{
			// if it's a primary id, remove the rest
			print "DELETE FROM `{$table}` WHERE `uid`<>{$x};<br/>";
		}
		else
		{
			// otherwise update the uid
			print "UPDATE `{$table}` SET `uid`={$x};"."<br/>";
		}
	}

}

Now change the value of $x to the ID you want to keep.
Go to www.yourdomain.com/file.php (where you replace file.php with the filename you chose).
You will see the SQL you need to run.

Afterwards, run all the Recount and Rebuild tools.

What is this; <> in the following statement ?

print "DELETE FROM `{$table}` WHERE `uid`<>{$x};<br/>";

It should be like this;

print "DELETE FROM `{$table}` WHERE `uid`={$x};<br/>";

(2011-06-10, 06:09 PM)Aries-Belgium Wrote: [ -> ]// if it's a primary id, remove the rest
print "DELETE FROM {$table} WHERE uid<>{$x};<br/>";

(2011-06-10, 06:16 PM)Yaldaram Wrote: [ -> ]What is this; <> in the following statement ?

print "DELETE FROM `{$table}` WHERE `uid`<>{$x};<br/>";

It should be like this;

print "DELETE FROM `{$table}` WHERE `uid`={$x};<br/>";

(2011-06-10, 06:09 PM)Aries-Belgium Wrote: [ -> ]// if it's a primary id, remove the rest
print "DELETE FROM {$table} WHERE uid<>{$x};<br/>";

<> in MySQL is not equal. That's basic SQL knowledge.
$x is the UID you want to keep so you don't want to delete it from the table.