Merging two forums

This is an archive of the phpBB 2.0.x support forum. Support for phpBB2 has now ended.
Forum rules
Following phpBB2's EoL, this forum is now archived for reference purposes only.
Please see the following announcement for more information: viewtopic.php?f=14&t=1385785
Locked
User avatar
flashweb
Registered User
Posts: 309
Joined: Sun Apr 20, 2003 4:15 pm
Location: India
Contact:

Merging two forums

Post by flashweb » Wed Jun 16, 2004 9:26 pm

Hi,

I have two phpBB forums on two different web sites.

I want to merge two phpBB's into one.

I mainly need the threads of one forum moved to another forum, no need to move the users as users can sign up again.

Can any one tell me which MySQL tables i need to backup from forum1 and restored to forum2 ?

Regards,

Yujin Boby

User avatar
phpbb2_fan
Registered User
Posts: 730
Joined: Wed Apr 30, 2003 6:21 pm
Location: Ethelbert, Manitoba, Canada
Contact:

Post by phpbb2_fan » Wed Jun 16, 2004 10:10 pm

Hello there, Yujin Boby

You need to backup phpbb_posts_text, phpbb_posts, phpbb_forums and phpbb_categories

Christian :wink:
I'm Back!

User avatar
flashweb
Registered User
Posts: 309
Joined: Sun Apr 20, 2003 4:15 pm
Location: India
Contact:

phpBB merging

Post by flashweb » Fri Jun 18, 2004 8:49 pm

Got it done with modified version of the script available at

http://www.typo.nl/misc/merge_phpbb.phps

The script, i found some errors, following is the modified phpBB merge tool.

Code: Select all

<?php

$dbms = 'mysql';
$phpbb_root_path = '../forums/';

$dbhost = 'localhost';

$dbuser = '';
$dbpasswd = '';

// Name and prefix for the database that should keep the original IDs

$dbname1 = '';
$table_prefix1 = '';

// Name and prefix for the database that is going to be added
// to DB1.
$dbname2 = '';
$table_prefix2 = '';


# NO NEED TO MODIFY CODE BELOW
# MODIFY ONLY IF U KNOW WHAT U R DOING


define('IN_PHPBB', true);
// Use DB1 for the initial config table etc.
$dbname = $dbname1;
$table_prefix = $table_prefix1;
include($phpbb_root_path . 'extension.inc');
include($phpbb_root_path . 'includes/constants.'.$phpEx);
include($phpbb_root_path . 'includes/db.'.$phpEx);


// These tables can be dropped from DB2 (not used in this script)
$drop_tables = array(
	"config",
	"banlist", // This one could be converted
	"disallow",
	"search_results",
	"search_wordlist",
	"search_wordmatch",
	"sessions",
	"smilies",
	"themes",
	"themes_name",
	"words"
	);
	
// All tables in DB2 that should shift ID.
//  - table that needs shifting (categories)
//  - id in table (cat_id)
//    nested array:
//    - table that depends on id (forums)
//    - id that corresponds to id in original table (cat_id)
$shift_tables = array(
	"categories" => array(
		"cat_id", 
		array(
			array("forums", "cat_id")
		)
	),
	"forums" => array(
		"forum_id",
		array(
			array("posts", "forum_id"),
			array("topics", "forum_id"),
			array("forum_prune", "forum_id"),
			array("auth_access", "forum_id")
		)
	),
	"forum_prune" => array(
		"prune_id",
		array()
	),
	"groups" => array(
		"group_id",
		array(
			array("user_group", "group_id"),
			array("auth_access", "group_id")
		)
	),
	"posts" => array(
		"post_id",
		array(
			array("posts_text", "post_id"),
			array("topics", "topic_first_post_id"),
			array("topics", "topic_last_post_id")
		)
	),
	"privmsgs" => array(
		"privmsgs_id",
		array(
			array("privmsgs_text", "privmsgs_text_id"),
			array("users", "user_last_privmsg")
		)
	),
	"topics" => array(
		"topic_id",
		array(
			array("posts", "topic_id"),
			array("topics_watch", "topic_id"),
			array("vote_desc", "topic_id")
		)
	),
	"users" => array(
		"user_id",
		array(
			array("user_group", "user_id"),
			array("groups", "group_moderator"),
			array("posts", "poster_id"),
			array("topics", "topic_poster"),
			array("privmsgs", "privmsgs_to_userid"),
			array("privmsgs", "privmsgs_from_userid"),
			array("topics_watch", "user_id"),
			array("vote_voters", "vote_user_id")
		)
	),
	"ranks" => array(
		"rank_id",
		array(
			array("users", "user_rank")
		)
	),
	"vote_desc" => array(
		"vote_id",
		array(
			array("vote_voters", "vote_id"),
			array("vote_results", "vote_id")
		)
	)
);

$bla_tables = array(
	"auth_access",
	"user_group",
	"posts_text",
	"privmsgs_text",
	"topics_watch",
	"vote_results",
	"vote_voters"
	);


// Traverse the shift_tables array

foreach($shift_tables as $key => $value)
{
	$table = $key;
	$merge_tables[$table] = 0; // keep an array with all tables that need merging
	$column = $value[0]; // Column with ID that needs to be shifted
	$ref = $value[1]; // Tables that are using the mentioned ID.
	print "Shifting IDs in table $table<br />\n";
	$max = shift_ids($table, $column);
	flush();
	// Do the dependent tables
	foreach($ref as $key => $value)
	{
		$d_table = $value[0];
		$merge_tables[$d_table] = 0;
		$d_column = $value[1];
		print "&nbsp; Altering dependent table: $d_table : $d_column (offset = $max)<br />\n";
		flush();
		shift_ids($d_table, $d_column, $max);
	}
	
	print "<br />\n";
	flush();
}

foreach($merge_tables as $table => $value)
{
	print "Merging $table table: ";
	if(merge_tables($table))
	{
		print " OK<br />\n";
	}
	else
	{
		print " FAILED!<br />\n";
	}
}

print "Merging users (username and either password or email are the same).<br />";
$sql = "
	SELECT
		u1.user_id as id1, 
		u2.user_id as id2, 
		u1.username
	FROM
		" . USERS_TABLE . " u1,
		" . USERS_TABLE . " u2
	WHERE
		u1.username = u2.username
		&& (u1.user_password = u2.user_password
			|| u1.user_email = u2.user_email)
		&& u1.user_id != u2.user_id
		&& u1.user_id < u2.user_id";
if(!$result = $db->sql_query($sql))
{
	 message_die(GENERAL_ERROR, 'Could not query for double user records.', '', __LINE__, __FILE__, $sql);
}

print "<table cellpadding='0' cellspacing='0'>";
while($row = $db->sql_fetchrow($result))
{
	print "<tr><td> ".$row['id1']."&nbsp;</td><td> ".$row['id2']."&nbsp;</td><td> ".$row['username']."&nbsp;</td><td> ";
	merge_users($row['id1'], $row['id2']);
	print " </td></tr>\n";
}
print "</table>";


function merge_users($user_id1, $user_id2)
{
	global $db;
	global $shift_tables;
	global $table_prefix1;
	
	$user_deps = $shift_tables['users'][1];

	// The users table should be skipped and the user_posts column should be updated.
	
	foreach($user_deps as $key => $value)
	{
		$d_table = $value[0];
		$d_column = $value[1];
		$sql = "UPDATE $table_prefix1$d_table SET $d_column = $user_id1 WHERE $d_column = $user_id2";
		if(!$result = $db->sql_query($sql))
		{
			 message_die(GENERAL_ERROR, 'Could not update user_id.', '', __LINE__, __FILE__, $sql);
		}
	}
	
	$sql = "DELETE FROM " . $table_prefix1 . "users WHERE user_id = $user_id2";
	if(!$result = $db->sql_query($sql))
	{
		 message_die(GENERAL_ERROR, 'Could not delete user2.', '', __LINE__, __FILE__, $sql);
	}

	print "OK";
	return;
}

function double_users()
{
	global $db;
	global $table_prefix1;

	$users_table = $table_prefix1 . "users";

	$sql = "SELECT user_id, ";
}

function merge_tables($table)
{

echo "<H1>Merging table : $table </H1>";

	global $db;
	global $dbname1, $table_prefix1, $dbname2, $table_prefix2;

	
	$sql = "SHOW FIELDS FROM $table_prefix1$table";
	if(!$result = $db->sql_query($sql))
	{
		 message_die(GENERAL_ERROR, 'Could not get field info from $table.', '', __LINE__, __FILE__, $sql);
	}
	
	$fields = array();
	while($row = $db->sql_fetchrow($result))
	{
		$fields[] = $row['Field'];
	}
	$fieldlist = implode($fields, ', ');
	

	if($table == 'users')
	{
		$where = " WHERE $dbname2.$table_prefix2" . $table . ".user_id > 0";
	}
	else
	{
		$where = '';
	}

	echo "$table";
	
	$sql = "INSERT INTO $dbname1.$table_prefix1".$table." ($fieldlist) SELECT $fieldlist from $dbname2.$table_prefix2" . $table . $where;
	if(!$db->sql_query($sql))
	{
		 message_die(GENERAL_ERROR, 'Could not merge $table.', '', __LINE__, __FILE__, $sql);
	}

	return TRUE;
}


// Shift all ID's in column $id in table $table in 
// database 2 by MAX($id) or (if not 0) by $offset
function shift_ids($table, $id, $offset = 0)
{
	global $db;
	global $dbname1, $table_prefix1;
	global $dbname2, $table_prefix2;

	// Offset hasn't been given, we're going to figure it out ourselfs
	if($offset == 0)
	{
		if(!$offset = getmax($dbname1, $table_prefix1.$table, $id))
		{
			// Empty table, no need to shift IDs
			print "Empty table? Skipping...<br />\n";
			return;
		}
	}

	// What's the max_id in the current table?
	$max2 = getmax($dbname2, $table_prefix2.$table, $id);
	$max2 = intval($max2); // Make sure that max2 contains a number, make it 0 if this table is empty.

// First we add the offset + the max of the current table
	// Treat values of 0 and lower as special values.
	$sql = "UPDATE $dbname2." . $table_prefix2 . $table . " SET $id = $id + $max2 + $offset WHERE $id > 0";
	print "$sql<br />\n";
	if(!$result = $db->sql_query($sql) )
	{
		 message_die(GENERAL_ERROR, 'Could not fetch max(id).', '', __LINE__, __FILE__, $sql);
	}
	
	// Then we subtract the max of the current table again.
	// We do this to prevent problems with key constrains from happening
	// i.e. if we do id=id+20 on key 1 when key 21 already exists we would get an error
	$sql = "UPDATE $dbname2." . $table_prefix2 . $table . " SET $id = $id - $max2 WHERE $id > 0";
	print "$sql<br />\n";
	if(!$result = $db->sql_query($sql) )
	{
		 message_die(GENERAL_ERROR, 'Could not fetch max(id).', '', __LINE__, __FILE__, $sql);
	}

	return $offset;
}

function getmax($dbname, $table, $id)
{
	global $db;
		$sql = "SELECT MAX($id) as max_id FROM $dbname." . $table;
		if(!$result = $db->sql_query($sql) )
		{
			 message_die(GENERAL_ERROR, 'Could not fetch max(id).', '', __LINE__, __FILE__, $sql);
		}
		if($max = $db->sql_fetchrow($result))
		{
			return($max['max_id']);
		}
		else
		{
			// Probably no rows where returned.. Empty table.
			return FALSE;
		}
}


function message_die($msg_code, $msg_text = '', $msg_title = '', $err_line = '', $err_file = '', $sql = '')
{
	global $db, $template, $board_config, $theme, $lang, $phpEx, $phpbb_root_path, $nav_links, $gen_simple_header;
	global $userdata, $user_ip, $session_length;
	global $starttime;

	$sql_store = $sql;
	
	//
	// Get SQL error if we are debugging. Do this as soon as possible to prevent 
	// subsequent queries from overwriting the status of sql_error()
	//
	$sql_error = $db->sql_error();

	$debug_text = '';

	if ( $sql_error['message'] != '' )
	{
		$debug_text .= '<br /><br />SQL Error : ' . $sql_error['code'] . ' ' . $sql_error['message'];
	}

	if ( $sql_store != '' )
	{
		$debug_text .= "<br /><br />$sql_store";
	}

	if ( $err_line != '' && $err_file != '' )
	{
		$debug_text .= '</br /><br />Line : ' . $err_line . '<br />File : ' . $err_file;
	}

	print $debug_text;

	exit;
}

echo "Done";

?>

User avatar
Siberian Fox
Registered User
Posts: 19
Joined: Mon Apr 14, 2003 7:53 pm
Location: Europe

Post by Siberian Fox » Sun Jun 20, 2004 5:41 pm

flashweb, thank you so much!

I was having a few problems with the original script. With this script I have been able to restore about 25,000 posts that were lost when some of the forums on my board were deleted. I didn't want to restore the entire board because the last backup was about three weeks old, but with this I was able to restore the backup to an empty board and then merege the missing forums with the main board.

Cheers! :)
Siberian Fox

Mysterious Benefactor
Registered User
Posts: 13
Joined: Mon Jun 21, 2004 4:30 pm
Location: Kentucky
Contact:

Post by Mysterious Benefactor » Mon Jun 21, 2004 4:35 pm

Hi,

Hate to be a bother, but I'm having trouble running this script (and its predecessor) on my local machine. The server here is running PHP, MySQL, and forums fine, but the script can't get any farther than giving me a "Shifting IDs in table categories" and then just stopping (when running through a web browser). I'd be more than willing to troubleshoot the thing, if I could get it started. :lol:

User avatar
flashweb
Registered User
Posts: 309
Joined: Sun Apr 20, 2003 4:15 pm
Location: India
Contact:

Post by flashweb » Mon Jun 21, 2004 7:24 pm

I tested the script on my pc 4 times with backup database of two forums before trying on server. It worked well. But it take too much processer power... My AMD 2200 with 256 RAM won't respond for some time while running the script. After running the script on the server, server become very slow and i have to reboot the server.

Mysterious Benefactor
Registered User
Posts: 13
Joined: Mon Jun 21, 2004 4:30 pm
Location: Kentucky
Contact:

Post by Mysterious Benefactor » Mon Jun 21, 2004 9:27 pm

Thank you, flashweb. But, if I may be a pretentious, newbie jerk, may I ask how you ran the script (i.e. on a web browser)? For example, how did you enter the database names into the script? Were they backups downloaded from phpMyAdmin, backups from the phpBB interface, or restored databases in a directory (I've been trying all three :( )?Did you receive the same message as I, and it just took some time for the results to be posted on screen? Thank you for your help very much!

User avatar
flashweb
Registered User
Posts: 309
Joined: Sun Apr 20, 2003 4:15 pm
Location: India
Contact:

Post by flashweb » Mon Jun 21, 2004 9:48 pm

You need to edit the script, enter data base name, login details etc...

I backup one db (1MB) using phpmyadmin. Other was near 20 MB, i used mysqldump to take backup, you can use anything as both are same.

I used browser to execute the script. It take some time, system get very slow. Some times, it will not even respond. I think it take around 5 mins to get the merging done.

Mysterious Benefactor
Registered User
Posts: 13
Joined: Mon Jun 21, 2004 4:30 pm
Location: Kentucky
Contact:

Post by Mysterious Benefactor » Mon Jun 21, 2004 10:43 pm

Hi again,

Thanks again, flashweb, but... my apologies for my ignorance, but I still can't get thing thing to work. If you could take a look at my code work...

Code: Select all

<?

$dbms = 'mysql';
$phpbb_root_path = '../forum/';

$dbhost = 'localhost';
$dbuser = 'MB';
$dbpasswd = 'password';


// Name and prefix for the database that should keep the original IDs
$dbname1 = 'forum';
$table_prefix1 = 'phpbb_';

// Name and prefix for the database that is going to be added
// to DB1.
$dbname2 = 'forum2';
$table_prefix2 = 'phpbb_';
I've adjusted the root path accordingly, as well as the logon details for MySQL (password witheld of course ;) ). 'forum' and 'forum2' exist as two *.sql databsases exported from phpMyAdmin lying in the same directory as the script (which is also the root web directory for the server). Each is around 2MB.

When I connect through my web browser to my own computer through the web server and attempt to run the script, I receive that aforementioned message. My firewall tells me that the script connects in for around 10s, and then the browser disconnects. This happens whether I use the *.sql files or expanded databases in direcories (also in the root web directory with the script), or whether I use 'localhost' or insert my IP address.

What am I doing wrong? I hate to have you guys piece-feed me the info, but this isn't so much for me as it is for a community. I'd really like to get it working. Thanks again, all. :)

Edit: I'm sorry, I just realized I've been working with the older merging script. However, I do so because I the newer one will not work at all; all I get is a gimish of PHP code output. :( Perhaps I'm saving it improperly (i.e. I copy the code, paste to Notepad, and save as a .phps file, which is set to be read through the PHP interpreter on my server. :) )?

BlueRook
Registered User
Posts: 2892
Joined: Wed Mar 10, 2004 2:38 am

Post by BlueRook » Tue Jun 22, 2004 12:34 am

Mysterious Benefactor wrote: Perhaps I'm saving it improperly (i.e. I copy the code, paste to Notepad, and save as a .phps file, which is set to be read through the PHP interpreter on my server. :) )?


You might double check that Notepad isn't appending a .txt at the end of the file name (e.g. filename.php.txt). If it is then you will need to remove the .txt

User avatar
CTCNetwork
Former Team Member
Posts: 15424
Joined: Fri Dec 19, 2003 3:50 am
Location: In that Volvo behind you!
Contact:

Post by CTCNetwork » Tue Jun 22, 2004 12:36 am

Hi,

Even better, use EditPlus2.

Des. . . :wink:
Density:- Not just a measurement~Its a whole way of Life.! ! !
| Welcome! | RTFM!!! | Search! It's Easy! | Problem? | Spam? | Advice! |

Mysterious Benefactor
Registered User
Posts: 13
Joined: Mon Jun 21, 2004 4:30 pm
Location: Kentucky
Contact:

Post by Mysterious Benefactor » Tue Jun 22, 2004 5:24 am

Thanks guys, but I've been pretty careful about checking the extensions. When something like that does happen, I just rename it in File Manager. (Yes, File Manager. :lol: ) So, I don't think that's it... ;) :)

I'll continue experimenting, but any further input would be appreciated.

Mysterious Benefactor
Registered User
Posts: 13
Joined: Mon Jun 21, 2004 4:30 pm
Location: Kentucky
Contact:

Post by Mysterious Benefactor » Wed Jun 23, 2004 3:37 am

If I could add a bit of a rub into the situation...

I've found running the script from a host's server (which uses Apache and PHP 4.3.4) gets the script running. I use Aprelium's Abyss sever and PHP 4.3.7. As I said, I've configured it and have gotten the forums to be functional on my home server. Could it be my server (i.e. should I start learning Apache :shock: )?

User avatar
flashweb
Registered User
Posts: 309
Joined: Sun Apr 20, 2003 4:15 pm
Location: India
Contact:

Post by flashweb » Wed Jun 23, 2004 6:49 am

Nice to know you have got it working on loacl pc.

You can now restore this DB on a new data base on the server and change the config.php file in the phpbb folder.

In cpanel box, when i tryed to add same user to two databases, it won't work. Think only one user can be assigned to a data base at a time.

Since one of my tables have no prefix, i restored that tables to same database and run the script to get it done on the server.

Mysterious Benefactor
Registered User
Posts: 13
Joined: Mon Jun 21, 2004 4:30 pm
Location: Kentucky
Contact:

Post by Mysterious Benefactor » Fri Jun 25, 2004 1:46 am

Well, I didn't really have it working on a local PC at that point. :oops:

But, I've installed Apache and configured everything around that, and now, the script runs! But...

Now I've run across more trouble. :( When I try running the script, I get this:

Code: Select all

Hacking attemptHacking attemptShifting IDs in table categories

Fatal error: Call to a member function on a non-object in c:\program files\apache\apache\htdocs\merge.php on line 349
This is with an adjusted header:

Code: Select all

<?php 

$dbms = 'mysql'; 
$phpbb_root_path = 'http://localhost/forum/'; 

$dbhost = 'localhost'; 

$dbuser = 'MB'; 
$dbpasswd = 'password'; 

// Name and prefix for the database that should keep the original IDs 

$dbname1 = 'forum'; 
$table_prefix1 = 'phpbb_';

// Name and prefix for the database that is going to be added 
// to DB1. 
$dbname2 = 'forum2'; 
$table_prefix2 = 'phpbb_'; 


# NO NEED TO MODIFY CODE BELOW 
# MODIFY ONLY IF U KNOW WHAT U R DOING
I'm not quite sure what tomake of the "Hacking Attempt" output; seeing as it isn't in this script I could only guess MySQL doesn't like the script querying. I'm using Win98, Apache 1.3 (2.0 locks up my machine :( ), and newest MySQL and PHP releases. I'm going to coninue digging, but any offered help would be gladly appreciated. :)

Edit: Also, if I use the old script, then I receive the same error but on line 334. :(

Locked

Return to “2.0.x Support Forum”

Who is online

Users browsing this forum: No registered users and 30 guests