webadmin's picture

Mass Change Prefix Tables MySQL with single Script PHP

Coding

When we worked on maintenance mode in Database MySQL on Drupal sites, sometime we need to change Our Prefix Tables at new database server, this is a simple trick if you don't want to change one by one table name :)

This is simple script PHP to use it.

<?php
/* script code by Praetorian Alexandria. */
/* global9.net | Belida.com (c) 2011 */

$db_server = 'localhost'; // if this script in your DB server on localhost
$db_username = 'dbusername'; // DB username
$db_password = 'pwdusername'; // PASSWORD DB username
$db_name = 'databasename'; // Name of your Database

$pattern = "oldexample_"; // Old Prefix Name Table
$new_pattern = "newexample_"; // New Prefix Name Table (e.g. "newexample_")

// login to MySQL server
$link = mysql_connect( $db_server, $db_username, $db_password);

if (!$link)
{
die('Could not connect: ' . mysql_error());
}

// list all tables in the database containing the search pattern
$sql = "SHOW TABLES FROM `" . $db_name . "`";
$sql .= " LIKE '%" . $pattern . "%'";

$result = mysql_query ( $sql, $link );
if (!$result)
{
die("Invalid query: " . mysql_error( $link ));
}

$renamed = 0;
$failed = 0;

while ( $row = mysql_fetch_array ($result) )
{
// rename every table by replacing the search pattern
// with a new pattern
$table_name = $row[0];
$new_table_name = str_replace ( $pattern, $new_pattern, $table_name);

$sql = "RENAME TABLE `" . $db_name . "`.`" . $table_name . "`";
$sql .= " TO `" . $db_name . "`.`" . $new_table_name . "`";

$result_rename = mysql_query ( $sql, $link );
if ($result_rename)
{
echo "Table `" . $table_name . "` renamed to :`";
echo $new_table_name . "`.\n";
$renamed++;
}
else
{
// notify when the renaming failed and show reason why
echo "Renaming of table `" . $table_name . "` has failed: ";
echo mysql_error( $link ) . "\n";
$failed++;
}
}

echo $renamed . " tables were renamed, " . $failed . " failed.\n";

// close connection to MySQL server
mysql_close( $link );

?>