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 2954 times

Leave a comment

Make sure you enter all the required information, indicated by an asterisk (*). HTML code is not allowed.