Multiple db tables in single custom module in Magento

07 / Mar / 2013 by amatya 13 comments

Creating a module which interact with a database table is quite simple. Most of the developers use magento module creator to create such a module. However, what if you want a module with multiple database tables. Following is the example of module with two database tables.

Step 1. Create setup file of your custom module with following queries.

[php]
CREATE TABLE `test` (
`test_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 25 ) NOT NULL
) ENGINE = MYISAM

CREATE TABLE `test2` (
`test2_id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 25 ) NOT NULL
) ENGINE = MYISAM
[/php]

Step 2. Create pool file to register your module under app/etc/modules/Mypackage_Mymodule.xml

[php]
<?xml version="1.0"?>
<config>
<modules>
<Mypackage_Mymodule>
<active>true</active>
<codePool>local</codePool>
</Mypackage_Mymodule>
</modules>
</config>
[/php]

Step 3. Your module configuration file should looks like following

app/code/local/ Mypackage/Mymodule/etc/config.xml

[php]
<?xml version="1.0"?>
<config>
<modules>
<Mypackage_Mymodule>
<version>0.1.0</version>
</Mypackage_Mymodule>
</modules>
<global>
<models>
<Mymodule>
<class>Mypackage_Mymodule_Model</class>
<resourceModel>mymodule_mysql4</resourceModel>
</mymodule>

<!– model vs db table relation –>
<mymodule_mysql4>
<class>Mypackage_Mymodule_Model_Mysql4</class>
<!– db table with name test –>
<entities>
<test>
<table>test</table>
</test>
<test2>
<table>test2</table>
</test2>

</entities>
</mymodule_mysql4>
</models>
<resources>
<mymodule_write>
<connection>
<use>core_write</use>
</connection>
</mymodule_write>
<mymodule_read>
<connection>
<use>core_read</use>
</connection>
</mymodule_read>
</resources>
</global>
</config>
[/php]

Step 4. Now create models Test.php and Test2.php. Here we configure these model with the handler of table test and test2.

/app/code/local/Mypackage/Mymodule/Model/Test.php

[php]
<?php

class Mypackage_ Mymodule_Model_Test extends Mage_Core_Model_Abstract
{

public function _construct()
{
parent::_construct();
$this->_init(‘mymodule/test’);
}
}
[/php]

/app/code/local/Mypackage/Mymodule/Model/Test2.php

[php]
<?php

class Mypackage_Mymodule_Model_Test2 extends Mage_Core_Model_Abstract
{

public function _construct()
{
parent::_construct();
$this->_init(‘mymodule/test2’);
}
}
[/php]

Step 5. Now create the resource models for model test and test2. In these files we also set the primary key of both the tables test and test2.

/app/code/local/Mypackage/Mmodule/Model/Mysql4/Test.php.

[php]
<?php

class Mypackage_Mymodule_Model_Mysql4_Test extends Mage_Core_Model_Mysql4_Abstract
{
public function _construct()
{
$this->_init(‘mymodule/test’, ‘test_id’);
}
}
[/php]

/app/code/local/Mypackage/Mmodule/Model/Mysql4/Test2.php.

[php]
<?php

class Mypackage_Mymodule_Model_Mysql4_Test2 extends Mage_Core_Model_Mysql4_Abstract
{
public function _construct()
{
$this->_init(‘mymodule/test2’, ‘test2_id’);
}
}
[/php]

Step 6. Create a collection classes so that we can retrieve data from table test and test2.

/local/Mypackage/Mymodule/Model/Mysql4/Test/Collection.php

[php]
<?php

class Mypackage_Mymodule_Model_Mysql4_Test_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{
public function _construct()
{
parent::_construct();
$this->_init(‘mymodule/test’);
}
}
[/php]

/local/Mypackage/Mymodule/Model/Mysql4/Test2/Collection.php

[php]
<?php

class Mypackage_Mymodule_Model_Mysql4_Test2_Collection extends Mage_Core_Model_Mysql4_Collection_Abstract
{
public function _construct()
{
parent::_construct();
$this->_init(‘mymodule/test2’);
}
}
[/php]

So, now you have a custom module with two tables. Your module can interact with these tables with their models and respective collections as follows :

[php]
$testModel = Mage::getModel(‘mymodule/test’)
->setName("abcd")
->save();

$test2Model = Mage::getModel(‘mymodule/test2’)
->setName("abcd")
->save();
[/php]

That’s it. Hope this will help you.

FOUND THIS USEFUL? SHARE IT

comments (13)

  1. pradip

    a:5:{i:0;s:402:”Error in file: “C:\xampp\htdocs\ext\app\code\local\Magecomp\Pquestion\sql\pquestion_setup\mysql4-install-1.0.0.php” – SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘

    Reply
  2. Pavan Yadav

    Here all files are created in ‘Model’ part in .config file and Model folder that’s why we can get and set data using
    Mage::getModel(‘pfay_films/test’) ->setName(“abcd”)->save();

    OR

    Mage::getModel(‘pfay_films/test2’)->getCollection();

    My question is, how can I these action using ‘Helper’ class. Where all above codes and files are defined in .config file and in folders.

    Please explain with example so that all including me can get it.
    Thanks

    Reply
  3. Dhanoop

    $testModel = Mage::getModel(‘mymodule/test’)
    ->setName(“abcd”)
    ->save();

    $test2Model = Mage::getModel(‘mymodule/test2′)
    ->setName(“abcd”)
    ->save();

    this is code which file in include. i have no idea . plz help me

    Reply
    1. Pavan Yadav

      Add this code where you want to perform action in any file (IndexController,Blocks) Or any .phtml file

      Reply
  4. Nikunj Vaghasiya

    $testModel = Mage::getModel(‘mymodule/test’)
    ->setName(“abcd”)
    ->save();

    $test2Model = Mage::getModel(‘mymodule/test2′)
    ->setName(“abcd”)
    ->save();

    this is code which file in include. i have no idea . i am new in magento. plz help me

    Reply
  5. Fekad

    Nice post … but the configuration is missing something like

    Mypackage_Mymodule

    core_setup

    ….

    This is required to run the sql script in the sql/mymodule_setup/mysql4-install-1.0.0.php

    Reply
  6. shalini

    $testModel = Mage::getModel(‘mymodule/test’)
    ->setName(“abcd”)
    ->save();

    $test2Model = Mage::getModel(‘mymodule/test2’)
    ->setName(“abcd”)
    ->save();

    in which file we have to save this code and call as i am new in magento and want to change the existing functionality of magento like when category listed then on hover i have to list vendor name so for this i have to change all code with DB please advise

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *