How to do External Database Connection in Magento

Mostly, a single database connection is required only while working with Magento. Magento has excellent features to add new tables to the database or extend the existing ones. So why do we need an external database connection outside the Magento? Well, data migration from other e-commerce systems is one of the examples.

In this blog here, I am going to tell you a simple way as how to do external database connection in Magento with CRUD (create, read, update, delete) examples.

Configuration

In XML configuration, the external database connection is similarly defined as the Magento default one. The difference between internal and foreign connection is that foreign connection is defined inside the particulars module’s XML configuration. It defines read and write adapters and sets up the database credentials. Foreign tables are also defined in the same way as Magento table. They are defined under abc_foreignconnection_resource node so the model resource can be called later in the code. For a demo, here is a frontend node in XML config.

<?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

The next thing is the model which is used to define a foreign connection to get or save data in any foreign database. Here the model is initialized with the product table from XML configuration, which 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’);

}

}

The model resource class is defined as the same xml config node in _init()function, but with the table_primary_key parameter. Several functions can be created in this class which can work with external data.

1^st Example is CreateDataInResource function, which will insert data into model’s table. It takes an array of parameters which are required to be inserted.

2^nd Example is ReadDataFromResource function which fetches all the data from the model’s table. Read adapter should be defined first. It’s a configuration node from XML which defines read connection. After the read adapter definition, other Magento database connection such as (select(), from(), limit(), etc) can be used. Read adapter will execute the query once it is constructed completely.

To fetch data, one can use these functions FetchPairs() or FetchAll(). FetchAll is used to get all the records returned from MySQL.

UpdateDataInResource and DeleteDataInResource functions requires additional $id parameter that defines which record is going to 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 below functions describes in IndexController class, but as they are defined in Model’s resource class, they can also be called in any controller class.

class Inchoo_ForeignConnection_IndexController extends Mage_Core_Controller_Front_Action

{

publicfunction 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 cases, Magento uses different types of external connection to retrieve or send data, but sometimes an external database connection is a good to go thing. Give it a try and do let us know in the comment section below as how it worked.