SQL RENAME TABLE Command

Using the RENAME SQL command, you can change the name of a table without losing any data.

Here we will change the name of our student table name to student_new table.
RENAME TABLE student 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
SQL Reference Change the structure of the table
Subscribe to our YouTube Channel here


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com
    Edward

    11-03-2015

    Fatal error: Call to a member function prepare() on a non-object on line 18

    Line 18: $count=$dbo->prepare("rename table MyGuests to Marker");

    Thanks for trying!
    smo

    12-03-2015

    Check your config.php file for database connection, $dbo is correctly connected or not.

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer