Published on: April 4, 2016 by Afsal
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 } ?>
Category : Howtos, Linux, magento, MySQL, WordPress
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.
Add new commentSIGN IN