Executing a script through phpmyadmin

[bubbles4u35]

I am new to mysql and php scripts.  I am running MySQL client version: 5.0.51a and phpMyAdmin - 2.11.9.1.

 

I am using this script to combine 2 dbs (well actually 2 prefixes in the same db but they act like 2 dbs).  It was given to me by a friend to try after 4 days of trying on my own to combine them. 

 

<?

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

$dbhost = 'localhost';
$dbuser = 'user';
$dbpasswd = 'secret';


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

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

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 "  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']." </td><td> ".$row['id2']." </td><td> ".$row['username']." </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)
{
    global $db;
    global $dbname1, $table_prefix1, $dbname2, $table_prefix2;
    
    $sql = "SHOW FIELDS FROM $table_prefix$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 = '';
    }
    
    $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;
}

?>

 

I have filled in the db name, prefixes, user and pw.  That's as far as I can get.  I don't know where to upload the script to and how to execute it through phpmyadmin.  Any help would be greatly appreciated.

 

CT

[revraz]

That won't execute via PHPMYADMIN, just run the script via Webserver with PHP.

[bubbles4u35]

OK I uploaded it to the php folder on the server.  Now what?    Sorry I know I must sound dumb! 

[revraz]

Navigate to the file via your browser.

[bubbles4u35]

I am getting a 404 error.  I cannot get to it through my browser only through the CP

[revraz]

Not sure what you mean.

 

You need to put the .php file in the correct folder that it belongs in.

 

Since this is a PHPBB script, read their instructions.

[bubbles4u35]

The only instructions I was given was to change the info at the top (database name, prefixes , user and pw) save as NAME.php then upload and execute. 

 

If it can't be executed through phpmyadmin the only php folder I have has these subfolders.

 

.channels

.registry

data

docs

ext

tests

 

and then my script file.  I have a checklist folder with a lot of .php but I wouldn't think it would go in there.

 

 

[revraz]

You should really be asking this on PHPBB's forums, but the basic fix is...

 

when you downloaded the .zip file, they had folders recursed.  When you unzip them all say onto your HD, you should put them in a folder named something like PHPBB (if it didn't have a top level folder name in the .zip).  You modify that file, then upload the entire folder to your webhost, so now you have

 

http://mydomain.com/PHPBB

 

navigate to that path, with the filename after (config.php or setup.php?) and it should run.

[bubbles4u35]

phpbb boards would not help because this is an existing database on a existing forum.  It is not a phpbb problem but a db problem with combining 2 prefixes that exist in one db to 1 prefix.  That is why I posted under mysql help.  The script that I was given was to try and accomplish this without having to pay someone to do this for me because I need it done by tonight and our site is a non profit and is supported by donations.  We took our site down to upgrade and add scripts to enhance our services.  We are scheduled to bring the site back up on Thursday and I still have work to do on the db's after I get this one combined. 

 

At this point if you could recommend where I could get someone to do this that is not too expensive I would appreciate any suggestions.   

 

Thank You for your time.

[bubbles4u35]

Ok the script did not work.  So can anyone tell me how I can combine 2 prefixes into one on the db? Right now I have

 

phpbb_

recovery_

 

I need to integrate the recovery ones into the phpbb without losing the structure or data.