Magento - Create an external Database Connection

Normally we are all experienced with connecting the database with Magento. But in this article, we are going to discuss about How to create an external database connection in Magento. Hopefully, by presenting this code here, I can save you from the pain I endured :)

Creating a database Connection

To create a new connection, create a custom module and add the following codes to your config.xml. The code below is needed to get the external database connection working.

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

Clear the cache after creating your new module and from now on, each time you load Magento, a second database connection will be created.

Accessing an external Database

When I initially checked out how to create an external database connection, I was using Zend_Db to retrieve all of my information. You can use the following to test your database connection is working:

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

The above code is working fine, however kind of takes the point away from having this connection available in Magento.

Accessing an 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.


I will attempt to demonstrate how to set up the models and config.xml files now.

Creating The Model Class Files

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');
    }

}

That's the minimum needed for your models to be able to access an external database!

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>

That's it, the models should now be registered in Magento!

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());
    } 

This is one of my favaourite article. So I probably haven't written this up as well as I could have, however, I think that using a second database in Magento can be extremely useful.