How to add new column to existing table in Magento programmatically?

How can I add a new column to existing Magento core table via install script? (without using pure SQL)

I want to use the Magento way which is using alias methods to create install script.

So far I followed few tutorials. But seems to be not working properly. This StackOverflow ALTER TABLE in Magento setup script without using SQL answer was somewhat similar to my question. But what is the content should be put into module's confg.xml file? Do I need to just define resource model, model and setup data would be enough?

The relevant portion of the config.xml (of my module) is as follow.

<config>
 . . . 
<global>
        <models>
            <mymodule>
                <class>Mynamespace_Mymodule_Model</class>
                <resourceModel>mymodule_resource</resourceModel>
            </mymodule>
            <mymodule_resource>
                <class>Mynamespace_Mymodule_Model_Resource</class>
            </mymodule_resource>
        </models>
        <resources>
            <mymodule_setup>
                <setup>
                    <module>Mynamespace_Mymodule</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </mymodule_setup>
            <mymodule_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </mymodule_read>
            <mymodule_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </mymodule_write>
        </resources>
. . . .
</config>

And my install script is as follows.

$installer = $this;
$installer->startSetup();

$installer->getConnection()
        ->addColumn($installer->getTable('sales_flat_order'),'custom_value', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, array(
        'nullable'  => false,
), 'Title');
$installer->endSetup();

But I'm getting the following error.

SQLSTATE[42S02]: Base table or view not found: 1146 Table '255.sales_flat_order' doesn't exist

Any suggestion to fix this would be appreciated.

Solutions

You are misusing the method addColumn:

public function addColumn($tableName, $columnName, $definition, $schemaName = null)

the fourth parameter is the schemaName, in your call, the fourth parameter is 255.

->addColumn($installer->getTable('sales_flat_order'),'custom_value', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, array(
    'nullable'  => false,
), 'Title')

If you use the right parameters, it should work.

sales_flat_order is the full name of the table and so you have to use alias in $installer->getTable()

In $installer->getTable() parameter like module_alias/table_alias.

In that case try with

$installer->getTable('sales/order')

When you write this it will return table name sales_flat_order

because

module_alias = sales

table_alias = order

EDIT

You can use below script to add new column. It works fine in my system

$installer = $this;
$installer->startSetup();

$installer->getConnection()
->addColumn($installer->getTable('sales/order'),'custom_value', array(
    'type'      => Varien_Db_Ddl_Table::TYPE_TEXT,
    'nullable'  => false,
    'length'    => 255,
    'after'     => null, // column name to insert new column after
    'comment'   => 'Title'
    ));   
$installer->endSetup();

I am using Varien_Db_Ddl_Table::TYPE_TEXT insted of Varien_Db_Ddl_Table::TYPE_VARCHAR because TYPE_VARCHAR is deprecated

You can check @ Varien_Db_Adapter_Pdo_Mysql::$_ddlColumnTypes

And If you specify type TYPE_TEXT but set length to lets say 255 Magento will create a MySQL column of VARCHAR type.

I realize this is a relatively 'old' question, but as it's still pretty find-able by Google, I decided to add this bit of information.

Regarding your question, if you want to change the sales/order table, this should not be done by the traditional install scripts/setup. The Mage_Catalog module uses a different Resource_Setup class, namely Mage_Sales_Model_Resource_Setup.

If you want to add attributes to the sales/order model, in order to ensure everything is added and processed correctly, add your attribute like this:

<?php

// Start setup
/** @var Mage_Sales_Model_Resource_Setup $installer */
$installer = new Mage_Sales_Model_Resource_Setup('core_setup');
$installer->startSetup();

// Gather info
$entityType = 'order'; // Adding attribute to this entity type (must be written out in text, not the entity type ID!! That'll not work.
$attributeName = 'my_attribute_code'; // Your attribute code/name

// Add attribute, very few parameters are accepted.
$installer->addAttribute($entityType, $attributeName, array(
    'type'              => 'varchar'
));

// End setup
$installer->endSetup();

Should you wonder why, then the answer is within the addAttribute() function of the Mage_Sales_Model_Resource_Setup class:

/**
 * Add entity attribute. Overwrited for flat entities support
 *
 * @param int|string $entityTypeId
 * @param string $code
 * @param array $attr
 * @return Mage_Sales_Model_Resource_Setup
 */
public function addAttribute($entityTypeId, $code, array $attr)
{
    if (isset($this->_flatEntityTables[$entityTypeId]) &&
        $this->_flatTableExist($this->_flatEntityTables[$entityTypeId]))
    {
        $this->_addFlatAttribute($this->_flatEntityTables[$entityTypeId], $code, $attr);
        $this->_addGridAttribute($this->_flatEntityTables[$entityTypeId], $code, $attr, $entityTypeId);
    } else {
        parent::addAttribute($entityTypeId, $code, $attr);
    }
    return $this;
}

Similar questions

Need to add decimal column in existing table
I want to add new decimal column in sales_order_item table, I got this error when i run upgrade:setup command run. https://www.screencast.com/t/yGFPIhPJz Is this below code correct? Please help me
Magento PHP Created a new column in table, attempting to use column to filter and perform conditional statements
I created a new column in the "sales_flat_order" table called "gc_sent", default value of 0. From here, I'm trying to perform an if statement below in the code, saying if the Card's gc_sent is 0, then send an email to the customer with their giftcard in it. Then set the card_status to 1, and gc_sent to 1. However, what I'm running into in my dev en...
Magento2 - Add Custom Column in cart item table and insert data to custom column when product add to cart
i have added new column in 'quote_item' table. i want to add the data to custom column when user add any product to cart.
How to add new item (product) into existing order programmatically in Magento 2
I have created one virtual product in my Magento2 website. Now, I want to add it programmatically to the existing customer orders on some occasions with its custom price and name. Can someone please guide step by step to achieve this? Thanks,
How do getName of Table A in table B where table A(id)= table B(id)
I have action index in my module. And block So in index.phtml i want getName in table Slideshow() So <?php $objSlideshow = $this->_objectManager->get("Xanka\Slider\Model\Slideshow")->load($model->getId());?> what wrong ? . Thanks
magento adding a column in existing table
I am new to Magento. I want to add a column in the newsletter_subscriber table so I made a new file mysql4-upgrade-1.6.0.0-1.6.0.1.php in app/code/core/mage/newsletter_setup/ I updated the config file: It doesn't work, please guide what I am doing wrong

Also ask

We use cookies to deliver the best possible experience on our website. By continuing to use this site, accepting or closing this box, you consent to our use of cookies. To learn more, visit our privacy policy.