Magento: Change Database Prefix

In this quick article we will see how you can perform database table prefix change for Magento.

Let’s imagine that you missed prefix or placed it wrong. How about changing all table (approx. 350) names manually?

Now there is a no reason to worry, I have prepared the script, using which you can perform Magento database table prefix change task in just two simple steps.

Step 1

Copy below code and save it as one PHP file. Place that file under root of your Magento directory.

[cc lang=”php”]
$db_host = ‘database_host’;
$db_user = ‘database_user’;
$db_password = ‘database_pass’;
$db_name = ‘database_name’;

// Change below prefix variable based on your setup
$old_prefix= ‘old_’;
$new_prefix = ‘new_’;

// Stop Editing Here
// Connecting MySQL Server
$db_obj = mysql_connect($db_host,$db_user,$db_password);

// Selecting Database
mysql_select_db($db_name);

// Getting All Tables
$all_tables=”SHOW TABLES”;
$result=mysql_query($all_tables) or die(‘Err’);

while($row=mysql_fetch_array($result))
{
$old_table=$row[0];

$new_table = preg_replace(‘/’.$old_prefix.’/’, $new_prefix, $old_table, 1);

// Changing Table Name
$rename_table_query=”RENAME TABLE `”.$old_table.”` TO `”.$new_table.”`”;
mysql_query($rename_table_query);
}

echo “Process Completed”;

[/cc]

Once you are done with above step you just need to execute that file, it will change table names of given database.

Step 2

Now we are done with renaming all the tables in database, now its time to tell magento that we have changed the table prefix. So for that we need to modify xml file which lives at path: [code]\app\etc\local.xml[/code]

In above you file you have to change the block which mentioned in below code block, you just need to place your new prefix for database tables.

[cc lang=”xml”]


[/cc]

So, it was so simple to change table prefix for Magento database. Let me know if you any other simple method to perform this task?