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 app\code\local\<Namespace>\<Module>\sql\<module>_setup\mysql4-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>