Most of the time working with Magento, a single database connection is just enough. Magento has excellent system of adding new tables in database or extending existing ones. So, why would there be a need for an external database connection outside the Magento system? Well, one of the examples is data migration from another ecommerce system. In this article, a simple connection to external database is explained with CRUD (create, read, update, delete) examples.
Configuration
This external database connection is similarly defined as the Magento default one – in an XML configuration. The difference is that foreign connection is defined inside particular module’s XML configuration. It defines read and write adapters, setup and database credentials information. Foreign tables are defined in the same way as magento tables. They are under inchoo_foreignconnection_resource node so the model resource can be invoked later in the code. For demonstration purpose, there’s a frontend node in XML configuration that defines front name of the controller (fconn).
<?xml version="1.0"?>
<config>
<modules>
<Inchoo_ForeignConnection>
<version>1.4.2</version>
</Inchoo_ForeignConnection>
</modules>
<global>
<models>
<inchoo_foreignconnection>
<class>Inchoo_ForeignConnection_Model</class>
<resourceModel>inchoo_foreignconnection_resource</resourceModel>
</inchoo_foreignconnection>
<inchoo_foreignconnection_resource>
<class>Inchoo_ForeignConnection_Model_Resource</class>
<entities>
<product>
<table>product_description</table>
</product>
</entities>
</inchoo_foreignconnection_resource>
</models>
<resources>
<inchoo_foreignconnection_write>
<connection>
<use>inchoo_foreignconnection_database</use>
</connection>
</inchoo_foreignconnection_write>
<inchoo_foreignconnection_read>
<connection>
<use>inchoo_foreignconnection_database</use>
</connection>
</inchoo_foreignconnection_read>
<inchoo_foreignconnection_setup>
<connection>
<use>core_setup</use>
</connection>
</inchoo_foreignconnection_setup>
<inchoo_foreignconnection_database>
<connection>
<host><![CDATA[localhost]]></host>
<username><![CDATA[username]]></username>
<password><![CDATA[password]]></password>
<dbname><![CDATA[db_name]]></dbname>
<initStatements><![CDATA[SET NAMES utf8]]></initStatements>
<model><![CDATA[mysql4]]></model>
<type><![CDATA[pdo_mysql]]></type>
<pdo_type><![CDATA[]]></pdo_type>
<active>1</active>
</connection>
</inchoo_foreignconnection_database>
</resources>
</global>
<frontend>
<routers>
<inchoo_foreignconnection>
<use>standard</use>
<args>
<module>Inchoo_ForeignConnection</module>
<frontName>fconn</frontName>
</args>
</inchoo_foreignconnection>
</routers>
</frontend>
</config>Model
Next thing is a model that will use defined foreign connection to get data or to save data in a foreign database. Here, the model is initialized with the product table from XML configuration, that in this case defines product_description table.
class Inchoo_ForeignConnection_Model_Product extends Mage_Core_Model_Abstract
{
protected $_eventPrefix = 'inchoo_foreignconnection_product';
protected $_eventObject = 'product';
protected function _construct()
{
$this->_init('inchoo_foreignconnection/product');
}
}Model resource class is also defined with the same xml configuration node in _init() function, but with the TABLE_PRIMARY_KEY parameter. In this class, several functions can be created that will work with external data.
First example is createDataInResource function, which inserts data in model’s table. It takes array of parameters that will be inserted.
Second example is a readDataFromResource function that fetches all data from model’s table. Read adapter must be defined first. It is a configuration node from xml that defines read connection. After read adapter definition, Magento database functions can be used (select(), from(), limit(), etc..). When query is constructed completely, it can be executed with read adapter. Data can be retrieved with fetchPairs() or fetchAll() function. fetchAll() is used to get all records returned from mysql.
updateDataInResource and deleteDataFromResource functions take additional $id parameter that defines which record will be updated or deleted.
class Inchoo_ForeignConnection_Model_Resource_Product extends Mage_Core_Model_Resource_Db_Abstract
{
const TABLE_PRIMARY_KEY = 'product_id';
protected function _construct()
{
$this->_init('inchoo_foreignconnection/product', self::TABLE_PRIMARY_KEY);
}
public function createDataInResource($values = array())
{
$writeAdapter = $this->_getWriteAdapter();
try {
$writeAdapter->insert(
$this->getMainTable(),
$values
);
} catch (Exception $e) {
Mage::log('Unable to insert data to external resource. ' . $e->getMessage(), null, null, true);
}
}
public function readDataFromResource()
{
$data = array();
$readAdapter = $this->_getReadAdapter();
$select = $readAdapter->select()
->from($this->getMainTable(), '*')
->limit(20);
try {
$data = $readAdapter->fetchAll($select);
} catch (Exception $e) {
Mage::log('Unable to fetch data from external resource. ' . $e->getMessage(), null, null, true);
}
return $data;
}
public function updateDataInResource($id, $values = array())
{
$writeAdapter = $this->_getWriteAdapter();
try {
$writeAdapter->update(
$this->getMainTable(),
$values,
self::TABLE_PRIMARY_KEY . '=' . $id
);
} catch (Exception $e) {
Mage::log('Unable to update data in external resource. ' . $e->getMessage(), null, null, true);
}
}
public function deleteDataFromResource($id)
{
$writeAdapter = $this->_getWriteAdapter();
try {
$writeAdapter->delete(
$this->getMainTable(),
self::TABLE_PRIMARY_KEY . '=' . $id
);
} catch (Exception $e) {
Mage::log('Unable to delete data from external resource. ' . $e->getMessage(), null, null, true);
}
}
}class Inchoo_ForeignConnection_Model_Resource_Product_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract
{
public function _construct()
{
$this->_init('inchoo_foreignconnection/product');
}
}Usage in controller
All these functions are demonstrated in IndexController class but since they are defined in model’s resource class, they can be called in any controller class.
class Inchoo_ForeignConnection_IndexController extends Mage_Core_Controller_Front_Action
{
public function indexAction()
{
// Create
$foreignProductCreate = Mage::getModel('inchoo_foreignconnection/product')->getResource();
$foreignProductCreate->createDataInResource(
array(
'product_name' => 'Product name',
'product_description' => 'Product description'
)
);
// Read
$foreignProductRead = Mage::getModel('inchoo_foreignconnection/product')->getResource();
$result = $foreignProductRead->readDataFromResource();
var_dump($result);
// Update
$foreignProductUpdate = Mage::getModel('inchoo_foreignconnection/product')->getResource();
$foreignProductUpdate->updateDataInResource(
3394,
array(
'product_name' => 'Product name updated',
'product_description' => 'Product description updated'
)
);
// Delete
$foreignProductDelete = Mage::getModel('inchoo_foreignconnection/product')->getResource();
$foreignProductDelete->deleteDataFromResource(3394);
}
}In most scenarios, Magento will use different type of external connection to retrieve or send data, but sometimes an external database connection like this will be the best way to go. One of the examples would be when you want to import products from another system to Magento with their xsell or upsell products. In that case, read connection would be used to retrieve product data and write connection would be used to save xsell or upsell product ids in a temporary table so they can be assigned to Magento product when all products from external system are imported.
