Yireo tutorials

Learn more about Joomla!TM and Magento
You are here: Home Tutorials Magento Magento administration Renaming the Magento database table prefix

Renaming the Magento database table prefix

During the installation of Magento you have the option to use a database prefix for your Magento tables in the database. Using such a database prefix adds to the security of your site, because generic SQL Injection attacks will be more likely to fail. But if you installed Magento using the demo-data or if you forgot about this thing during installation, you are stuck with a database without prefix. Here is the procedure to update your MagentoTM instance with a different prefix.

Renaming all database tables

Renaming database tables could be done manually through a tool like phpMyAdmin, but because a default Magento install counts over 200 tables, this task is better performed by using a PHP-script. Open up a file and add the following PHP-code to it:

 
<?php
$database_host = "localhost";
$database_user = "root";
$database_password = "root";
$magento_database = "magento2";
$table_prefix = "dwz_";
 

Make sure these settings match your own Magento installation. The $database_host is most likely "localhost", but it could be different. Consult your hosting provider for these details, or open up the Magento file app/etc/local.xml which already contains these details.

The settings contain the variable $table_prefix which is set here to "dwz_". The prefix could be changed into something else. We recommend you use only alphabetical characters and end the prefix with an underscore ("_"). These configuration settings are used in the rest of the PHP-script. Add the following PHP-code to the same file:

 
$db = mysql_connect($database_host, $database_user, $database_password);
mysql_select_db($magento_database);
$query = "SHOW TABLES";
$result = mysql_query($query) or die('Err');
while($row = mysql_fetch_array($result)) {
    $old_table = $row[0];
    if(preg_match('/'.$table_prefix.'/', $old_table)) {
        echo "Table $old_table already done<br/>\n";
        continue;
    }
 
    $new_table = $table_prefix.$old_table;
    echo "Renaming $old_table to $new_table<br/>\n";
    $query = "RENAME TABLE `$old_table`  TO `$new_table`";
    mysql_query($query);
}
 

Save the file to a name like "rename_table_prefix.php" (ending with a ".php" extension) and upload it to your Magento website.

Run the script

Before you run the script you should ofcourse prepare a database backup and a file backup. Also make sure you know how to restore these backups. If you are ready for it, just run the PHP-script by entering the right URL in your browser:

http://MAGENTO/rename_table_prefix.php

This should rename all the database tables to include the new table prefix. If this went ok, remember to delete the PHP-script afterwards.

Modifying the Magento configuration

Now that the database tables are modified, you will also need to modify the Magento configuration. Open up the file app/etc/local.xml and locate the following line: 

 
<table_prefix><![CDATA[]]></table_prefix>
 

Change it to point to the new table prefix:

 
<table_prefix><![CDATA[dwz_]]></table_prefix>
 

That's it. It might be that you need to empty out the Magento cache directory (MAGENTO/var/cache) but normally everything should be working right away.

Tutorials on Magento administration

Tutorials on MageBridgeTM administration

 

Payments Methods

Payment Methods