How to Alter , create new table, delete or update table using upgrade script in magento

Hello Friends,

You can perform database query in magento upgrade script.

you are facing problem with alter,update,delete or create new table in magento but its very easy just write defined code in your upgrade script and you can see your table is altered or insert

create or delete

you must create script for this like beloved name

Name of file : 

syntax : mysql4-upgrade-<old_version>-<new_version>.php

 

Ex : mysql4-upgrade-0.1.0-0.1.1.php

Now you have quesion is that how to find it rifgh?

but it also easy   please open your config.xml file of your module and check the version which is your old_version.

syntax for create , alter and delete table

<?php $this->startSetup()->run("<write your query>")->endSetup();?>

code for ALTER Table

<?php

$this->startSetup()->run("

   ALTER TABLE {$this->getTable('faqs')}

   ADD COLUMN `preview` text AFTER `steps`

")->endSetup();

?>

code for Create New Table

<?php

$this->startSetup()->run("

  

DROP TABLE IF EXISTS {$this->getTable('mytable')};
CREATE TABLE {$this->getTable('mytable')} (
`entity_id` int(11) unsigned NOT NULL auto_increment,
`title` varchar(255) NOT NULL default '',
`content` varchar(255) NOT NULL default '',
`status` smallint(6) NOT NULL default '0',
`created_time` datetime NULL,
`update_time` datetime NULL,
PRIMARY KEY (`entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

")->endSetup();

?>

code for DELETE Table

<?php

$this->startSetup()->run("

DELETE from table_name where column_name = value

")->endSetup();

?>

code for UPDATE Table

<?php

$this->startSetup()->run("

UPDATE table_name set column_name=value where column_name = value

")->endSetup();

?>

Thanks,

Enjoy Your Self