Add extra field in existing table

Question

I added following codes in my app\code\local\Svi\Form\sql\form_setup\mysql4-install-0.1.0.php

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
-- DROP TABLE IF EXISTS {$this->getTable('svi_form')};
CREATE TABLE {$this->getTable('svi_form')} (
  `form_id` int(11) unsigned NOT NULL auto_increment,
  `fname` varchar(255) NOT NULL default '',
  `lname` varchar(255) NOT NULL default '',
  `fathername` varchar(255) NOT NULL default '',
  `mothername` varchar(255) NOT NULL default '',
  `mobile` bigint(10) NOT NULL default '0',
  PRIMARY KEY (`form_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    ") or die("error in local/svi/form/sql folder.....");


 //Add new column to the 'svi_form' table

        $installer->getConnection()->addColumn(
        $this->getTable('svi/form'), //table name
        'address' 'varchar(255) NOT NULL DEFAULT '''  //'column name' 

//datatype definition

        );

$installer->endSetup();

app\code\local\Svi\Form\etc\config.xml

<?xml version="1.0"?>
<config>
    <modules>
        <Svi_Form>
            <version>0.1.0</version>
        </Svi_Form>
    </modules>
    <frontend>
        <routers>
            <form>
                <use>standard</use>
                <args>
                    <module>Svi_Form</module>
                    <frontName>form</frontName>
                </args>
            </form>
        </routers>
        <layout>
            <updates>
                <form>
                    <file>form.xml</file>
                </form>
            </updates>
        </layout>
    </frontend>
    <global>
        <models>
            <form>
                <class>Svi_Form_Model</class>
                <resourceModel>form_mysql4</resourceModel>
            </form>
            <form_mysql4>
                <class>Svi_Form_Model_Mysql4</class>
                <entities>
                    <form>
                        <table>svi_form</table>
                    </form>
                </entities>
            </form_mysql4>
        </models>
        <resources>
            <form_setup>
                <setup>
                    <module>Svi_Form</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </form_setup>
            <form_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </form_write>
            <form_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </form_read>
        </resources>
        <blocks>
            <form>
                <class>Svi_Form_Block</class>
            </form>
        </blocks>
        <helpers>
            <form>
                <class>Svi_Form_Helper</class>
            </form>
        </helpers>
    </global>
</config>

But it is not working. What to do?

this code " $installer->getConnection()->addColumn(..." u add after run your script ?? — ND17, Aug 06 '15 at 07:21
@ND17 table is already in database. but when i added code for new field. I didn't get new field. what to do? can you explain your answer. — Amit Kushwaha, Aug 06 '15 at 07:26

ND17 · Accepted Answer · 2015-08-06T08:55:01.230

8

When you want to update your module in terms of new database fields to provide new functionality, you need to alter your schema, change fields datatypes, introduce new columns, and so on.

This is when the upgrade script comes in handy. You can not make any direct changes via an SQL editor by running row queries, because whenever another user installs your module, that new database change will not work in their setup. It’s Magento’s recommendation to use an upgrade script for altering the database schema.

An upgrade script is similar to an install script – the only change is the name and a different version number.

we will create an upgrade script named mysql4-upgrade-0.1.0-0.1.1.php under app\code\local\Svi\Form\sql\form_setup\. Add the following code.

  <?php
$installer = $this;
$installer->startSetup();
$installer->getConnection()->addColumn(
    $this->getTable('form/form'),//table name 

    'address',      //column name
    'varchar(255) NOT NULL'  //datatype definition
    );

$installer->endSetup();

change in config.xml

 <modules>
    <Svi_Form>
        <version>0.1.1</version> // here 0.1.0 to 0.1.1
    </Svi_Form>
</modules>

edited Aug 06 '15 at 08:55

answered Aug 06 '15 at 07:37

ND17

5,181
9
50
78

Hey dude, I have to create new file when i will change version of sql file? – Amit Kushwaha Aug 06 '15 at 08:02
yes u have to create new file and change version – ND17 Aug 06 '15 at 08:05
Hey dude, when i will create new file of sql then only above code have to put there or some other code should be there? – Amit Kushwaha Aug 06 '15 at 08:36
noo bt make sure u will change version in config.xml – ND17 Aug 06 '15 at 08:47
I did dude as you said here but it is not working.... – Amit Kushwaha Aug 06 '15 at 08:48
is there any error? and make sure that your new file name suppose this mysql4-upgrade-0.1.0-0.1.1.php than u have to write version this 0.1.1 – ND17 Aug 06 '15 at 08:50
no dude there is no error..... – Amit Kushwaha Aug 06 '15 at 08:52
wait I am posting here what i did. – Amit Kushwaha Aug 06 '15 at 08:54
hey change this $this->getTable('form/form') replace svi with form – ND17 Aug 06 '15 at 08:55
I changed to svi from form but till now not working. – Amit Kushwaha Aug 06 '15 at 09:04
you are so caring person, thanks for your effort...... – Amit Kushwaha Aug 06 '15 at 09:10
ok in database find table "core_resource" in that find "form_setup" and tell me the version – ND17 Aug 06 '15 at 09:16
Hey dude there is 0.1.1 in "form_setup" – Amit Kushwaha Aug 06 '15 at 09:22
than change it to 0.1.0 in both column and than refresh ur website page – ND17 Aug 06 '15 at 09:24
Accepted and also gave you reputation. – Amit Kushwaha Aug 06 '15 at 09:33

Amit Kushwaha · Answer 2 · 2015-08-06T09:04:49.983

I created new file in app\code\local\Svi\Form\sql\form_setup\mysql4-upgrade-0.1.0-0.1.1.php

<?php
$installer = $this;
$installer->startSetup();
$installer->getConnection()->addColumn(
    $this->getTable('form/form'),//table name
    'address',      //column name
    'varchar(255) NOT NULL'  //datatype definition
    );
$installer->endSetup();
?>

and change the version in app\code\local\Svi\Form\etc\config.xml

<modules>
    <Svi_Form>
        <version>0.1.1</version>
    </Svi_Form>
</modules>

score 0 · Answer 3 · answered Sep 07 '16 at 13:04

An upgrade script is similar to an install script – the only change is the name and a different version number.

example: create an upgrade script named mysql4-upgrade-0.1.0-0.1.1.php

<?php
$installer = $this;
$installer->startSetup();
$installer->getConnection()->addColumn(
    $this->getTable('test/test'),//table name 

    'address',      //column name
    'varchar(255) NOT NULL'  //datatype definition
    );

$installer->endSetup();

change in config.xml

 <modules>
    <Svi_Form>
        <version>0.1.1</version> // here 0.1.0 to 0.1.1
    </Svi_Form>
</modules>

score -1 · Answer 4 · edited Aug 05 '16 at 10:32

-1

Try This :

<?php
$installer = $this;
$installer->startSetup();

$sql = <<<SQLTEXT
ALTER TABLE `YOUR_tABLE_NAME` ADD `COLUMN_NAME` VARCHAR( 500 ) 
SQLTEXT;

$installer->run($sql);

$installer->endSetup();

edited Aug 05 '16 at 10:32

Siarhey Uchukhlebau

15,957
11
54
83

answered Aug 05 '16 at 10:09

NVN

1

Add extra field in existing table

4 Answers4