Blog

PHP script to Change MySQL Table Prefix

Tags: MySQLphpTable

Published on: April 4, 2016 by Afsal

PHP script to Change MySQL Table Prefix

Scenario:

When installing applications like Magento, WordPress, etc, there will be an option to specify a MySQL table prefix which is to be used with the application’s MySQL database. This prefix will be added to all the tables in the application’s database. Adding such a prefix increases the security of the application as it is a safeguard against attacks like SQL injection.

If you need to change the table prefix after installation or if you need to add a table prefix if you haven’t done so while installing, you can do it by manually changing the name of all the tables via phpMyAdmin or via SSH. As this method is time consuming considering most applications has a lot of tables, we can do this easily with the help of a PHP script.

We will be using PHP MySQLi in our script to perform the renaming operation.

We can use the following script to read the database attributes and then change the table prefix.


<?php
if(isset($_POST['submit'])){
$database_host = $_POST['dbhname'];
$database_user = $_POST['dbuname'];
$database_password = $_POST['password'];
$database_name = $_POST['dbname'];
$new_table_prefix = $_POST['newpref'];
$old_table_prefix = $_POST['oldpref'];


// Connect to database
$db = mysqli_connect($database_host, $database_user, $database_password, $database_name) or die('MySQL connect failed');

// List all tables
$result = mysqli_query($db, "SHOW TABLES") or die('SHOW TABLES failed');

// Loop through all tables
while($row = mysqli_fetch_array($result)) {
    $old_table = $row[0];

    // Check if old prefix is correct
    if(!empty($old_table_prefix) && !preg_match('/^'.$old_table_prefix.'/', $old_table)) {
        echo "Table $old_table does not match prefix $old_table_prefix<br/>\n";
        continue;
    }

    // Preliminary check: Is the old table prefix the same as the new one?
    if(preg_match('/^'.$new_table_prefix.'/', $old_table)) {
        echo "Table $old_table already done<br/>\n";
        continue;
    }

    // Construct the new table prefix and rename the table
    if(!empty($old_table_prefix)) {
        $new_table = preg_replace('/^'.$old_table_prefix.'/', $new_table_prefix, $old_table);
    } else {
        $new_table = $new_table_prefix.$old_table;
    }
    // Rename the table
    echo "Renaming $old_table to $new_table<br/>\n";
    mysqli_query($db, "RENAME TABLE `$old_table`  TO `$new_table`");
    header("Refresh: 2; URL = prefix.php"); //Name of the script file
}
echo "Renaming complete";
mysqli_close($db);
}else{
?>
<html>
<head>
<title>Change Table Prefix</title>
<body>

<form name="PrefixChanger" method="post" action="<?php echo $_SERVER['PHP_SELF']?>">
<table>
<tr><td>DB Hostname</td><td> <input type="text" required name="dbhname" value=""></td></tr>
<tr><td>DB Name</td><td> <input type="text" required name="dbname" value=""></td></tr>
<tr><td>DB Username</td><td> <input type="text" required name="dbuname" value=""></td></tr>
<tr><td>DB Password</td><td> <input type="password" required name="password" value=""></td></tr>
<tr><td>New Prefix</td><td> <input type="text" required name="newpref" value=""></td></tr>
<tr><td>Old Prefix</td><td> <input type="text" name="oldpref" value=""></td></tr>
<tr><td></td><td align="right"><input type="submit" name="submit" value="submit"></td></tr>
</table>
</form>

</body>
</head>
</html>
<?php } ?>
Upload the above PHP script to the website and call it using a web browser. It will display the following output.

MySQL

The current database structure looks like this.

MySQL

To change the prefix of the tables, fill the form appropriately and click submit.

MySQL

If the entered values are correct, we will see prefix of each table being changed.

MySQL

Check the database to verify the changes.

MySQL

Get 24/7 expert server management

Category : Howtos, Linux, magento, MySQL, WordPress

Afsal

Afsal

Afsal is a tech enthusiast who is always ready to learn new technologies and explore new territories. Although his current focus revolves around the System Administration world, his never ending passion for technologies always keeps him active in other areas as well. He is currently working as a Systems Engineer at SupportSages.

You may also read:

Comments

Add new commentSIGN IN

Let's Connect

Get new updates

Categories

$0.000 items