phpBB2Refugees.com Logo
Not affiliated with or endorsed by the phpBB Group

Register •  Login 

Continue the legacy...

Welcome to all phpBB2 Refugees!Wave Smilie

This site is intended to continue support for the legacy 2.x line of the phpBB2 bulletin board package. If you are a fan of phpBB2, please, by all means register, post, and help us out by offering your suggestions. We are primarily a community and support network. Our secondary goal is to provide a phpBB2 MOD Author and Styles area.

How to merge two forums?


 
Search this topic... | Search General Support... | Search Box
Register or Login to Post    Index » General Support  Previous TopicPrint TopicNext Topic
Author Message
Ultimos Homo Statans
Board Member



Joined: 10 Feb 2012

Posts: 3



PostPosted: Sun Feb 12, 2012 3:08 am 
Post subject: How to merge two forums?

Hi!

I need to merge two 2.0.23 databases.
I've found this. But when I try to run the script, I get the following error:
Code:
SQL Error : 1054 Unknown column 'forum_parent' in 'field list'

INSERT INTO friends2011.phpbb_forums (forum_id, cat_id, forum_name, forum_desc, forum_status, forum_order, forum_posts, forum_topics, forum_last_post_id, prune_next, prune_enable, auth_view, auth_read, auth_post, auth_reply, auth_edit, auth_delete, auth_sticky, auth_announce, auth_vote, auth_pollcreate, auth_attachments, forum_parent, minimum_posts, minimum_posts_enabled, forum_desc_long) SELECT forum_id, cat_id, forum_name, forum_desc, forum_status, forum_order, forum_posts, forum_topics, forum_last_post_id, prune_next, prune_enable, auth_view, auth_read, auth_post, auth_reply, auth_edit, auth_delete, auth_sticky, auth_announce, auth_vote, auth_pollcreate, auth_attachments, forum_parent, minimum_posts, minimum_posts_enabled, forum_desc_long from otv.phpbb_forums

Line : 292
File : D:\xampplite\htdocs\friends2011\phpBB2_merge.php

I think this is from the MOD Simple Subforums. But I run Clean Tables MOD on both forums so the databases should have only the original tables.
Back to top
Dog Cow
Board Member



Joined: 18 Nov 2008

Posts: 378


flag
PostPosted: Tue Feb 14, 2012 12:40 am 
Post subject: Re: How to merge two forums?

Ultimos Homo Statans wrote:
Hi!

I need to merge two 2.0.23 databases.
I've found this. But when I try to run the script, I get the following error:

I think this is from the MOD Simple Subforums. But I run Clean Tables MOD on both forums so the databases should have only the original tables.

Yes, but that merge program is still issuing a query with forum_parent. Modify the program.

_________________
Moof! Email me: dog_cow@macgui.com
Inside Allerton Park Lincoln's Tomb, Oak Ridge Cemetery, Springfield IL Mac 512K Blog
Back to top
Ultimos Homo Statans
Board Member



Joined: 10 Feb 2012

Posts: 3



PostPosted: Tue Feb 14, 2012 1:11 am 
Post subject: Re: How to merge two forums?

I don't understand what you're saying. icon_rolleyes.gif

How to modify the script so that I can merge forums hosted on different hosts?

Code:
<?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_prefix1, $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_prefix,$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";

?>
Back to top
Display posts from previous:   
Register or Login to Post    Index » General Support  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT
 
Jump to:  

Index • About • FAQ • Rules • Privacy • Search •  Register •  Login 
Not affiliated with or endorsed by the phpBB Group
Powered by phpBB2 © phpBB Group
Generated in 0.0247 seconds using 15 queries. (SQL 0.0144 Parse 0.0002 Other 0.0100)
phpBB Customizations by the phpBBDoctor.com
Template Design by DeLFlo and MomentsOfLight.com Moments of Light Logo