Add New Column to Existing Database Table in Magento

While writing codes for Magento, sometimes you would like to add new columns to existing tables in Magento database. These situations arise very frequently in Magento programming. Suppose you want to add a custom ‘referred_by‘ column to the items in your wishlist. Magento stores the wishlist items in the ‘wishlist_item‘ table. So here we will add ‘referred_by‘ column to the table. This column will store the id of the customer who referred a certain product. Now we will see how to add this column to existing ‘wishlist_item‘ table. For this you have to create a custom module (how to create Magento module). Suppose you have created your module ‘Web’ under ‘Company’ namespace. In the sql installer file for this module, we will write the code to add the new column.

Open appcodelocal<Namespace><Module>sql<module>_setupmysql4-install-0.1.0.php
Write following lines in this file:

$installer = $this;

$installer->startSetup();

//Create the table for module
//This is optional
$installer->run("

DROP TABLE IF EXISTS {$this->getTable('<module>')};
CREATE TABLE {$this->getTable('<module>')} (
`<module>_id` int(11) unsigned NOT NULL auto_increment,
`customer_id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`<module>_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

//Add new column to the 'wishlist_item' table
$installer->getConnection()->addColumn(
$this->getTable('wishlist/item'), //table name
'referred_by', //column name
'int(11) NOT NULL DEFAULT 0' //datatype definition
);

$installer->endSetup();

How to get table name

In above example you can see that we have fetched tablename as $this->getTable(‘wishlist/item’). Here the first term ‘wishlist‘ is the module name and the second term ‘item‘ is the entity name for the table in the config.xml file of ‘wishlist‘ module. So this function is actually $this->getTable(‘<module>/<entity>’). In absence of entity name, it will as $this->getTable(‘<module>/<module>’). See the following sample from the ‘config.xml‘ of the wishlist module:

 <wishlist_mysql4>
<class>Mage_Wishlist_Model_Mysql4</class>
<entities>
<wishlist>
<table>wishlist</table>
</wishlist>
<item> <!-- Entity name -->
<table>wishlist_item</table> <!-- Table name -->
</item>
</entities>
</wishlist_mysql4>

Written by Arvind Bhardwaj

Arvind is a Magento and WordPress expert with more than 6 years of industry wide experience.

Website: http://www.webspeaks.in/

10 thoughts on “Add New Column to Existing Database Table in Magento

  1. this is the old way was engaged in magento 1.4 the new way is with objects in version 1.7

  2. $installer =new Mage_Core_Model_Resource_Setup();

    $read = $installer->getConnection(‘core_read’);
    $dbname = (string)Mage::getConfig()->getNode(‘global/resources/default_setup/connection/dbname’);

    $checkSelleridInSalesOrderitemTAble = “SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE
    TABLE_NAME = ‘{$installer->getTable(‘sales/order_item’)}’ AND COLUMN_NAME = ‘sellerid’ and TABLE_SCHEMA = ‘$dbname’ “;

    $rows = $read->fetchAll($checkSelleridInSalesOrderitemTAble);

    if(!count($rows)) {
    echo ‘Alter table now’;
    } else {
    echo ‘Already exists’;
    }

  3. It was very useful. I was searching for the Problem that, "add column if not exists in Magento"
    Your this code: $installer->getConnection()->addColumn(
    $this->getTable('wishlist/item'), //table name
    'referred_by', //column name
    'int(11) NOT NULL DEFAULT 0' //datatype definition
    );
    Solved my problem.

    Thanks a lot sir.

Comments are closed.