Multiple db tables in single custom module in Magento
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.
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 ‘
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
$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
add in the post submit action..i.e in your controller file add this code.
Add this code where you want to perform action in any file (IndexController,Blocks) Or any .phtml file
$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
wow, Works like a charm
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
thank you very very much
$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
thank you ….this is very useful for me
Thanks! Great Post, Save My lot of time………
Thankssssssssssssssssssssssssss Amatya
this code is very usefull for me.