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.

    Is the databasename `255`?

    no. database name is something else.

  • Keyur Shah

    Keyur Shah Correct answer

    6 years ago

    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.

    tried this, Even though I'm getting the same response

    @Sukeshini check my edit....

    Thank you very much. It worked perfectly and +1 for the effort you made to solve my issue.

    glad to hear that it works for you @Sukeshini

    @KeyurShah how to add a new field into custom table ?

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM