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
pdkv2
Registered User
Posts: 8
Joined: Wed Feb 07, 2007 10:31 am

Re: Merging two forums

Post by pdkv2 »

In the billow given script for merging forums just remove the table names which you don't want to merge
the array name of the tables is "$shift_tables"

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";

?>
Cheers
Sharad
fidforum.vze.com
Registered User
Posts: 185
Joined: Sun Nov 12, 2006 11:39 am

Re: Merging two forums

Post by fidforum.vze.com »

Im sorry....but im a n00b....i dont know php that very well...

can u plz remove the the things and i only want to merge the users table thats all. nothing else.
My Site . < Front Page Under Development!
User avatar
houser
Registered User
Posts: 97
Joined: Sun Sep 24, 2006 9:41 am

Re: Merging two forums

Post by houser »

Hi pdkv2,

I am trying to do the opposite, add tables...for Smartor's Photo albúm.
Could you tell me where to add those in the script?
Just one place or more than one place?

TIA and regards
Janne A.
pdkv2 wrote:In the billow given script for merging forums just remove the table names which you don't want to merge
the array name of the tables is "$shift_tables"

Sharad
User avatar
pdkv2
Registered User
Posts: 8
Joined: Wed Feb 07, 2007 10:31 am

Re: Merging two forums

Post by pdkv2 »

The script meagres all the tables listed in the "$shift_tables" array so you need to add the tables only in this array

Regards,
Sharad
fidforum.vze.com
Registered User
Posts: 185
Joined: Sun Nov 12, 2006 11:39 am

Re: Merging two forums

Post by fidforum.vze.com »

ok..now...hows the script run?

directly run from the phpbb root folder? where should i upload the database of the forum which i want to merge?
My Site . < Front Page Under Development!
User avatar
pdkv2
Registered User
Posts: 8
Joined: Wed Feb 07, 2007 10:31 am

Re: Merging two forums

Post by pdkv2 »

Hi,

Run the script from phpbb root folder the script will use two separate databases "$dbname1" and "$dbname2"
The script will merge the "$dbname2" to "$dbname1".

Regards
Sharad.
trv
Registered User
Posts: 64
Joined: Sun Nov 07, 2004 4:39 pm

Re: Merging two forums

Post by trv »

now that we have phpbb3, how about merging two phpbb3 forums? Is this possible by modifying the script?
User avatar
pdkv2
Registered User
Posts: 8
Joined: Wed Feb 07, 2007 10:31 am

Re: Merging two forums

Post by pdkv2 »

Yes
It is possible to merge the phpbb3 forums

Rgds
Sharad
User avatar
houser
Registered User
Posts: 97
Joined: Sun Sep 24, 2006 9:41 am

Re: Merging two forums

Post by houser »

Dear all,

So we have tried to add the tables for Smartor's Photo album. Phew...this is hard...
We used the last script with attachment mod in this thread...and added Smartor's tables...
Hopefully others will need that too?

I am afraid we still can't get beyond the first frame in the browser when running the script
"Shifting IDs in table categories" or just a white page....
is all we get (same as before editing the script)

Please find attached script here, if anyone has a chance to have a look at the code and see what's wrong?
TIa and best
Janne A.

(Edited to include suggestion in following post)

Code: Select all

<?php

set_time_limit (0);

$dbms = 'mysql4';
$phpbb_root_path = '../forum/m/';

$dbhost = 'localhost';

$dbuser = 'afff_se';
$dbpasswd = 'secret';

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

$dbname1 = 'afff_se';
$table_prefix1 = 'phpbbm1_';

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


# 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"),
         array("album", "pic_user_id"),
         array("album_comment", "comment_user_id"),
         array("album_comment", "comment_edit_user_id"),
         array("album_rate", "rate_user_id")
      )
   ),
   "album" => array(
      "pic_id",
      array(
         array("album_comment", "comment_pic_id"),
         array("album_rate", "rate_pic_id")
      )
   ),
   "album_cat" => array(
      "cat_id",
      array(
         array("album", "pic_cat_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";

?>
Last edited by houser on Tue Jul 17, 2007 10:18 am, edited 2 times in total.
User avatar
pdkv2
Registered User
Posts: 8
Joined: Wed Feb 07, 2007 10:31 am

Re: Merging two forums

Post by pdkv2 »

Hello,

Add the following line in the beginning of your script

Code: Select all

set_time_limit (0);
this will set the the script execution time (PHP variable )to unlimited

Regards
Sharad
User avatar
houser
Registered User
Posts: 97
Joined: Sun Sep 24, 2006 9:41 am

Re: Merging two forums

Post by houser »

Is there an "official" end-all ppbb2 version of this script, with the attachment MOD?
I think I did my Smartor's add changes to the wrong version of the script.... :oops:

Can someone please point to the latest greatest, attachment MOD version, and we will try to add
Smartor's Album to it again....

Or better still, some codeace that can take the time to merge and update the correct codesnippets for a
RC1 release of this script?
I am sure it would be usefuil to the community..

thanx regardless!
Janne A.
User avatar
houser
Registered User
Posts: 97
Joined: Sun Sep 24, 2006 9:41 am

Re: Merging two forums

Post by houser »

Hi all,

I took help from a coding friend who has modified the script as seen below
It is the latest version of Imladris attachment script from this thread, and it that also has Smartor's Album added to it.
Still can't get beyond the first white screen though...I must be missing something basic.... :cry:
I am now running the script from path http://www.mysite/forum/
The forum I am merging too is at http://www.mysite.com/forum/m/

Grateful for any feedback...
best
Janne A.

Code: Select all

<?php

// modifications by Imladris and others - for merging forums with Attachment Mod and also Smartor's Album Mod only!
// from: http://www.phpbb.com/phpBB/viewtopic.php?p=1426672#1426672

echo "starting phpbb w/attachment/smartor mod merge<br>";
flush();

$dbms = 'mysql4';
$phpbb_root_path = '../forum/m/';

$dbhost = 'localhost';

$dbuser = 'afff_se';
$dbpasswd = 'secret';

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

$dbname1 = 'afff_se';
$table_prefix1 = 'phpbbm1_';


// Name and prefix for the database that is going to be added
// to DB1.

$dbname2 = 'afff_se';
$table_prefix2 = '2006phpbb_';

# 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"),
        array("attach_quota", "group_id")
     )
  ),
  "posts" => array(
     "post_id",
     array(
        array("posts_text", "post_id"),
        array("topics", "topic_first_post_id"),
        array("topics", "topic_last_post_id"),
   array("forums", "forum_last_post_id"),
        array("attachments", "post_id")
     )
  ),
  "privmsgs" => array(
     "privmsgs_id",
     array(
        array("privmsgs_text", "privmsgs_text_id"),
        array("users", "user_last_privmsg"),
        array("attachments", "privmsgs_id")
     )
  ),
  "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"),
        array("attachments", "user_id_1"),
        array("attach_quota", "user_id")
        array("album", "pic_user_id"),
        array("album_comment", "comment_pic_id"),
        array("album_rate", "rate_pic_id")
      )
   ),
   "album_cat" => array(
      "cat_id",
      array(
         array("album", "pic_cat_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")
     )
  ),
  "attachments" => array(
        "attach_id",
     array(
         array("attachments_desc", "attach_id")
     )
   )
);

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


// 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";

//attach_quota, attachments, attachments_config, attachments_desc, extension_groups, extensions, forbidden_extensions, quota_limits 
?>
User avatar
houser
Registered User
Posts: 97
Joined: Sun Sep 24, 2006 9:41 am

Re: Merging two forums

Post by houser »

Pretty please??
Anything?
I think I have a problem with something basic, such as the rootpath relative to the installation
but I am seriously stuck....having thrown everything and the kitchensink at it....
would it not be great with Smartor's and all?
TIa and regards
Janne A.
User avatar
Lumpy Burgertushie
Registered User
Posts: 67788
Joined: Mon May 02, 2005 3:11 am
Contact:

Re: Merging two forums

Post by Lumpy Burgertushie »

I am not clear on what you are trying to do with smartor's photo album.

it is not a stand alone script, it must be run from phpbb as that is what it is designed to work with.

therefore, there should be no need to do any kind of merging with it and phpbb tables.

robert
I'm baaaaaccckkkk. still doing work on donation basis. PM your needs.

Premium phpBB 3.3 Styles by PlanetStyles.net

If nobody is in the forest, does a tree really fall?
User avatar
houser
Registered User
Posts: 97
Joined: Sun Sep 24, 2006 9:41 am

Re: Merging two forums

Post by houser »

Hmm

I have just added the tables in Smarto's album in order to merge the albums in the forums I
am merging. Is that clear?
Thanx
Janne A.
Locked

Return to “2.0.x Support Forum”