Print this page
Saturday, 07 June 2014

How to rename prefix all tables name of mysql database

Written by
(1 Vote)

How to rename prefix all tables of the database, compatible with php 5.x and php 7.x

FOR PHP 5.x

 <?php

$db_server = "localhost";
$db_name = "vietpublic";
$db_username = "root";
$db_password = "vietpublic";

$old_prefix = "old_";
$new_prefix = "new_";

$session_id = mysql_connect( $db_server, $db_username, $db_password);
if (!$session_id) { die('Connection Failed: ' . mysql_error());}

$query = "SHOW TABLES FROM `" . $db_name . "`" . " LIKE '%" . $old_prefix . "%'";
$result = mysql_query ( $query, $session_id );
if (!$result) {die("Query Failed: " . mysql_error( $session_id ));}

while ( $row = mysql_fetch_array ($result) ) {
$old_table_name = $row[0];
$new_table_name = str_replace ($old_prefix, $new_prefix, $old_table_name);
$query = "RENAME TABLE `".$db_name."`.`".$old_table_name."`"." TO `".$db_name."`.`".$new_table_name."`";
if (mysql_query ( $query, $session_id )) {
echo "$old_table_name was renamed TO $new_table_name <br />";
} else {
echo "Error: $old_table_name was not renamed" . mysql_error( $session_id ) . "<br />";
}
}
mysql_close( $session_id );
?>

 

FOR PHP 7.x

 <?php
$db_server =     "localhost";
$db_name =         "vietpublic";
$db_username =     "root";
$db_password =     "";

$old_prefix =     "old_";
$new_prefix =     "new_";

$session_id = mysqli_connect( $db_server, $db_username, $db_password);
if (!$session_id) { die('Connection Failed: ' . mysql_error());}

$query = "SHOW TABLES FROM `" . $db_name . "`" . " LIKE '%" . $old_prefix . "%'";
$result = mysqli_query ($session_id, $query);
if (!$result) {die("Query Failed: " . mysql_error( $session_id ));}

while ( $row = mysqli_fetch_array ($result) ) {
    $old_table_name = $row[0];
    $new_table_name = str_replace ($old_prefix, $new_prefix, $old_table_name);
    $query = "RENAME TABLE `".$db_name."`.`".$old_table_name."`"." TO `".$db_name."`.`".$new_table_name."`";

        if (mysqli_query ($session_id, $query )) {
            echo "$old_table_name was renamed TO $new_table_name <br />";
        } else {
            echo "Error: $old_table_name was not renamed" . mysql_error( $session_id ) . "<br />";
        }
}

mysqli_close($session_id);
?>
Read 3579 times