MySQL RENAME: changing name of a table by query using sql commands

We can rename any table by using RENAME sql command. The data will not be lost. Only the table name will be changed to new name. Here is the command to change the name of a table. Here we will change the name of our student table name to student_new table.

We can also copy data from one table to a new table. Same way we can copy data from one table with WHERE clause to an existing table.

RENAME TABLE student TO student_new

This sql command will rename the student table to student_new

Sample PHP Script to change name of the table.

We will use PDO functions to manage MySQL database from PHP. We will establish connection to MySQL database first by including the config.php file. Read more on database connection using PDO here.
Below code will print success or failure message based on the status of the query execution.

<?Php

require "config.php"; // Database Connection



$count=$dbo->prepare("rename table student2 to student");



if($count->execute()){

echo " Success <br>";

echo " Your table name is changed<br> "; 

$row = $count->fetch(PDO::FETCH_OBJ);

}else{

print_r($dbo->errorInfo());  // Printing database error messages

echo " <br>Failed to change the name of the table <br>";

}

?>

After changing the name of the table we can check by displaying all the name of the tables present in the database. Here is the code for that. You can add below code at the end of the table name change code given above.

echo "<br>-----<br><b>Table List</b> <br>-----<br>";

$result = $dbo->query("SHOW TABLES");

while ($row = $result->fetch(PDO::FETCH_NUM)) {

echo $row[0]."<br>";

}

Renaming all the tables of a database

We can add ( prefix ) a text string to all the tables present in a database or we can remove a fixed length of characters from all the tables names.

We will be using PHP script with PDO support for this.
While listing all the tables by using show tables query inside while loop we will prepare two variables.

$old_name=$row[0];

$new_name=$row[0];

Adding string before the name

The first variable will store the present name of the table by taking the data from the query. The second variable we will prepare by prefixing a string to the names. Here we have added plus2net_ before the name of the table.

$old_name=$row[0];

$new_name="plus2net_" . $row[0];

Removing string from the name of the table. We have added the string plus2net_ to all the tables, now to remove the strings we will change the variable like this.

$new_name=substr($row[0],9);

Note that the length of the string we want to remove plus2net_ is of 9 char length. You can read more on substr string function here.

Replacing the string with new string from the name of the table.

We can replace part of the table name with another string by using str_replace() string function. Here is how the variables is changed.

$new_name=str_replace("plus2net_","mysite_",$row[0]);

The full script is here, based on requirements you can use the variable, lines commented inside the script

<?Php

require "config.php"; // Database Connection



$result = $dbo->query(" SHOW TABLES in photos2net_forum ");

$str='';



while ($row = $result->fetch(PDO::FETCH_NUM)) {

$old_name=$row[0];

$new_name="plus2net_" . $row[0];



// To remove chars from starting we can use below line // 

// $new_name=substr($row[0],9);



// To replace chars with new string, use below line //

//$new_name=str_replace("plus2net_","mysite_",$row[0]);



$str ="RENAME TABLE $old_name TO $new_name;";

echo $str;

echo "<br><br>";

$dbo->exec("$str");

}



?>

In the above script database connection is kept inside config.php file.

Note that the database you selected inside config.php file should be same as you select in show table query

Subscribe to our YouTube Channel here

SQL RENAME TABLE Command

Sample PHP Script to change name of the table.

Renaming all the tables of a database

Adding string before the name

Replacing the string with new string from the name of the table.

Subscribe