1

how to create table in magento the below file is not able to execute

<?php
//This sql file which is created is executed when any magento page is opened in the browser
$installer = $this;  //Getting Installer Class Object In A Variable
$installer->startSetup();//startSetup() runs the sql script
//here is the function used for table name $this->getTable(‘test’)
$installer->run("
-- DROP TABLE IF EXISTS {$this->getTable('test')};
CREATE TABLE {$this->getTable('test')} (
  `test_id` int(11) unsigned NOT NULL auto_increment,
  `title` varchar(255) NOT NULL default '',
  `filename` varchar(255) NOT NULL default '',
  `content` text NOT NULL default '',
  `status` smallint(6) NOT NULL default '0',
  `created_time` datetime NULL,
  `update_time` datetime NULL,
  PRIMARY KEY (`test_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");
$installer->endSetup();

?>

config file

<?xml version="1.0"?>
<config>
    <!--declaring configration of our module-->
    <modules>
        <Excellence_Test>
            <version>0.0.2</version>
            <!-- Version of module -->
        </Excellence_Test>
    </modules>
    <frontend>
        <!--front end refers to magento area-->
        <routers>
            <!--routers will turn url into actioncontroller and method-->
            <test><!--this should be lower case name of your module and this defines our route name-->
                <use>standard</use>
                <args>
                    <module>Excellence_Test</module>
                    <!--module will be used by the system to locate your Controller files.-->
                    <frontName>test123</frontName>
                    <!--this is the front name which will appear in url-->
                </args>
            </test>
        </routers>
        <layout><!--defining the layout of our web page-->
            <updates>
                <test>
                    <file>test123.xml</file><!-- This is name of the layout file for this module -->
                </test>
            </updates>
        </layout>
    </frontend>
    <global>
        <!--Enabling Model Resources in our Module-->
        <models><!--Model are used to logical + data operations in MVC pattern
        There are two parts to models in magento, 1st is the Model file and 2nd Model resource files. Models are used to for logical operation and resource files are used to perform actual database queries-->
            <test><!--this is model group name , which should match your module name-->
                <class>Excellence_Test_Model</class> <!-- Location of all model class files -->
                <!--need to create our own resource, can't just use core_mysql4  -->
                <resourceModel>test_mysql4</resourceModel> <!-- Location of resource model -->
                <!--The <resourceModel /> tag indicates which Resource Model that test group Models should use-->
            </test>
            <test_mysql4>
                <class>Excellence_Test_Model_Mysql4</class>
                <entities>
                    <!--Here basically, we add a list of all our tables we would like to use in our module.
                    if more than one table is added then code should be like this
                    <entities>
                          <test>
                                  <table>test_tablename</table>
                          </test>
                          <test2>
                                     <table>test_tablename2</table>
                          </test2>
                          <test3>
                                    <table>test_tablename3</table>
                          </test3>
                          </entities>
                          -->
                    <test><!--here we have added only one table  iw pfay_test-->
                        <table>pfay_test</table>  <!-- Actual table name in sql  -->
                    </test>
                </entities>
            </test_mysql4>
        </models>
        <resources>  <!-- These are resource setting giving access to module, read/write permission on database -->
            <test_setup>
                <setup>
                    <module>Excellence_Test</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </test_setup>
            <test_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </test_write>
            <test_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </test_read>
        </resources>
        <blocks>
            <test>
                <class>Excellence_Test_Block</class><!-- Path of the Block Folder, where all php files are located related to view -->
            </test>
        </blocks>
        <helpers>
            <test>
                <class>Excellence_Test_Helper</class><!-- Path of Helper Files -->
            </test>
        </helpers>
    </global>
</config>
2
  • please provide us with your config.xml as well Nov 15, 2014 at 9:12
  • 1
    What is the file name and path of your install script? Nov 15, 2014 at 14:02

2 Answers 2

5

Since Magento 1.6 the Resource alias has been renamed from mysql4 to resource, also the way to create a table has changed from direct SQL queries to Varien_Db_Ddl_Table. This gives you a uniform way to create tables, indexes and foreign-keys. Below a sample on how to create:

app/code/community/

<?xml version="1.0" encoding="UTF-8"?>
<config>
    <modules>
        <Example_SalesGrid>
            <version>0.1.0</version>
            <!-- define version for sql upgrade -->
        </Example_SalesGrid>
    </modules>
    <global>
        <models>
            <example_salesgrid>
                <class>Example_SalesGrid_Model</class>
                <resourceModel>example_salesgrid_resource</resourceModel>
            </example_salesgrid>
            <example_salesgrid_resource>
                <class>Example_SalesGrid_Model_Resource</class>
                <entities>
                    <demotable>
                        <table>demo_table</table>
                    </demotable>
                </entities>
            </example_salesgrid_resource>
        </models>
        <resources>
            <!-- initialize sql upgrade setup -->
            <example_salesgrid_setup>
                <setup>
                    <module>Example_SalesGrid</module>
                </setup>
            </example_salesgrid_setup>
        </resources>
    </global>
</config>

Setup script:

/app/code/community/Example/SalesGrid/sql/example_salesgrid_setup/install-0.1.0.php

$installer = $this;

$installer->startSetup();

$table = $installer->getConnection()
    ->newTable($installer->getTable('example_salesgrid/demotable'))
    ->addColumn(
        'test_id',
        Varien_Db_Ddl_Table::TYPE_INTEGER,
        null,
        array(
            'identity' => true,
            'unsigned' => true,
            'nullable' => false,
            'primary' => true,
            'auto_increment' => true,
        ),
        'Entity Id'
    )
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, array(), 'Title')
    ->addColumn('filename', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, array(), 'Filename')
    ->addColumn('content', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(), 'Content')
    ->addColumn('status', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(), 'Status')
    ->addColumn('created_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(), 'Create date/time')
    ->addColumn('update_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(), 'Update date/time');

$installer->getConnection()->createTable($table);

$installer->endSetup();

Next you will also need the create the Model, Resource Model en Resource Collection files so that you can use the model demotable.

app/code/community/Example/SalesGrid/Model/Demotable.php

class Example_SalesGrid_Model_Demotable extends Mage_Core_Model_Abstract
{
    protected function _construct()
    {
        $this->_init('example_salesgrid/demotable');
    }
}

app/code/community/Example/SalesGrid/Model/Resource/Demotable.php

class Example_SalesGrid_Model_Resource_Demotable extends Mage_Core_Model_Resource_Db_Abstract
{
    protected function _construct()
    {
        $this->_init('example_salesgrid/demotable', 'test_id');
    }
}

app/code/community/Example/SalesGrid/Model/Resource/Demotable/Collection.php

class Example_SalesGrid_Model_Resource_Demotable_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract
{

    protected function _construct()
    {
        $this->_init('example_salesgrid/demotable');
    }
}
1

There are a set of issues that may occurred and not allowing you to create the table, The XML config file you supported does not seam to have anything wrong, but I would make sure of the following:

  1. Please make sure under your extension directory to have the "sql" directory.
  2. under that make sure that the directory's name is "test_setup"
  3. the sql file inside, if it is a new installation to be mysql4-install-(the version of your extension).php
  4. If everything is there and you still can't create the table, You have to know that Magento has a table called "core_resource" where it stores all extension codes that needs to run sql file, if the code of the extension is saved and the version matches the version of your extension inside the etc/config.xml file , then it will not run the sql again since it considers it ran from before. Solution: You have to check the table core_resource for your setup code; in this example it is "test_setup", delete it, flush cache, and reload Magento, or the admin dashboard. Once reloaded, Magento will check if your extension code is in that database table, if it is not, it will run the sql inside your extension as an initial setup.

Hope this helps.

2
  • please explain step 4
    – kumar0981
    Nov 16, 2014 at 5:53
  • I updated step 4, please let me know if you need more details.
    – Rabea
    Nov 17, 2014 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.