Magento - Creating and accessing An External Database Connection

In this article I'm going to explain about How t create and access an external database connection in Magento. Most of the developers facing some troubles when trying to create and access the external database connection in Magento. This article will help the developers to reach their goals by creating and accessing the external database connection in Magento.

Step 1 : Creating the database connection

To create the external database connection you need to create a custom module and add the following code to your config.xml. The code is used to get the external database connection to be work.

<?xml version="1.0"?>
<config>
    <modules>
        <Phpcmsframework_Externaldb>
            <version>0.1.0</version>
        </Phpcmsframework_Externaldb>
    </modules>
    <global>
        <resources>
            <externaldb_write>
                <connection>
                    <use>externaldb_database</use>
                </connection>
            </externaldb_write>
            <externaldb_read>
                <connection>
                    <use>externaldb_database</use>
                </connection>
            </externaldb_read>
            <externaldb_setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </externaldb_setup>
            <externaldb_database>
                <connection>
                    <host><![CDATA[localhost]]></host>
                    <username><![CDATA[db_username]]></username>
                    <password><![CDATA[db_password]]></password>
                    <dbname><![CDATA[db_name]]></dbname>
                    <model>mysql4</model>
                    <type>pdo_mysql</type>
                    <active>1</active>
                </connection>
            </externaldb_database>
        </resources>
    </global>
</config>

After adding the above code to your config.xml, clear the cache and from now on, each time you load Magento, a second database connection will be created.

Step 2: Accessing the Database

You can use the following Zend_Db to test your database connection is working or not.

<?php
     $resource   = Mage::getSingleton('core/resource');
    $conn       = $resource->getConnection('externaldb_read');
    $results    = $conn->query('SELECT * FROM tblName');     
    print_r($results)

This works fine, however kind of takes the point away from having this connection available in Magento.

Step 3: Accessing The External Database Using Models

Using models to access the database keeps our code style uniform throughout Magento. Also, it means we can integrate any other CMS or database driven application without learning it's coding practices. To achieve this, simply add models to your custom module like you would for any other module.

Create the following files

code/local/Phpcmsframework/Externaldb/Model/Book.php

<?php
class Phpcmsframework_Externaldb_Model_Book extends Mage_Core_Model_Abstract
{
    public function _construct()
    {
        $this->_init('externaldb/book');
    }
}

code/local/Phpcmsframework/Externaldb/Model/Mysql4/Book.php

<?php
class Phpcmsframework_Externaldb_Model_Mysql4_Book extends Mage_Core_Model_Mysql4_Abstract
{
    public function _construct()
    {
        $this->_init('externaldb/book', 'book_id'); // book_id refers to the primary key of the book table
    }
} 

code/local/Phpcmsframework/Externaldb/Model/Mysql4/Book/Collection.php

<?php
class Phpcmsframework_Externaldb_Model_Mysql4_Book_Collection extends Mage_Core_Model_Mysq4_Collection_Abstract
{
    public function _construct()
    {
        $this->_init('externaldb/book');
    }
}

Adding the Models to The Config

To inform Magento about our models, we need to register them in config.xml. Below is an updated version of config.xml with the models for Book registered.

<?xml version="1.0"?>
<config>
    <modules>
        <Phpcmsframework_Externaldb>
            <version>0.1.0</version>
        </Phpcmsframework_Externaldb>
    </modules>
    <global>
        <models>
            <externaldb>
                <class>Phpcmsframework_Externaldb_Model</class>
                <resourceModel>externaldb_mysql4</resourceModel>
            </externaldb>
            <externaldb_mysql4>
                <class>Phpcmsframework_Externaldb_Model_Mysql4</class>
                <entities>
                    <book>
                        <table>library_book</table>
                    </book>
                </entities>
            </externaldb_mysql4>
        </models>
        <resources>
            <externaldb_write>
                <connection>
                    <use>externaldb_database</use>
                </connection>
            </externaldb_write>
            <externaldb_read>
                <connection>
                    <use>externaldb_database</use>
                </connection>
            </externaldb_read>
            <externaldb_setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </externaldb_setup>
            <externaldb_database>
                <connection>
                    <host><![CDATA[localhost]]></host>
                    <username><![CDATA[db_username]]></username>
                    <password><![CDATA[db_password]]></password>
                    <dbname><![CDATA[db_name]]></dbname>
                    <model>mysql4</model>
                    <type>pdo_mysql</type>
                    <active>1</active>
                </connection>
            </externaldb_database>
        </resources>
    </global>
</config>

Testing The Models

Testing them is easy enough, just treat them like normal Magento models.

<?php
    // Load the book with a primary key value of 4
    $_book = Mage::getModel('externaldb/book')->load(4);

    // This would print out the value in the field isbn in the external database
    echo $_book->getIsbn();

    //You can even update records!
    $_book->setName('1984');
    $_book->setAuthor('George Orwell');

    try {
      $_book->save();
    } catch (Exception $e) {
      exit($e->getMessage());
    }