How to create an external database connection from a Magento Module

I reckon that many of you have already done this so many times, but as a newcomer to Magento, it took me a while to figure this out. So this tutorial is solely for newcomers like me, so they can save some time.

In this tutorial I'm going to show you how to connect to a external database (other than Magento database) using a custom Magento module. I’ll be using a Magento Basic Model and a Resource Model to connect to this external database. I'll also be using an index controller action to show you that this really works :).

This is what my custom module is going to do, Module name will be “Blog”, and it will connect to a database called “externaldb”. This database will have a table call “blog”. So my module will connect to this external database and read the data from the blog table. Yep, it ‘s so simple! :D

First create an empty module in you Magento instance. (Hope you already know how to do this, if not click here to learn how to do it)

Your Module structure will look like below,

Creating the config.xml file

Config.xml file plays a major part in Magento and it does the same in this case. All connection details to the external database can be placed in config.xml of your module (app/code/local/youmodulename/etc/config.xml), plus your read and write adapter details will also go in here.

Your config.xml will looks like below,

    
        
         0.1.0
  
 
 
  
   
    Externaldb_Blog_Model
    blog_resources
   
   
    Externaldb_Blog_Model_Resource
    
     
      blog

    
   
  
  
   
    
       <![CDATA[localhost]]>
       <![CDATA[someuser]]>
       <![CDATA[somepassword]]>
       <![CDATA[externaldb]]>
       <![CDATA[mysql4]]>
       <![CDATA[SET NAMES utf8]]>
       <![CDATA[pdo_mysql]]>
       1
    
   
   
    
     blog_default
    
   
   
    
     blog_default
    
   
  
 
 
      
              
                standard
                
                    Externaldb_Blog
                    externaldb 
                
             
            
        

The section that you really need to understand in above XML, (with regards to the external database connection) is between "resources" tags. Because it contains the externaldb connection details, read and write adapter configurations.
 You can see I have created a "blog_default" section with connection details of the external database. Additionally, read and write adapter that uses “blog_default” connection.

Note that "blog" in "blog_read" and "blog_write" are the module group name. You cannot have anything else since I'm going to use the resource model to connect to the database.

Model Class for Blog module 

Following is the code for Blog Model (app/code/local/Externaldb/Blog/Model/Blogpost.php),

/**
 * Description of Externaldb_Blog_Model_Blogpost
 *
 * @author thanura
 */

class Externaldb_Blog_Model_Blogpost extends Mage_Core_Model_Abstract{
 public function _construct() {
  $this->_init('blog/blogpost');
 }
 
 public function readDataFromResource(){
  return $this->getResource()->readDataFromTable();
 }
 
}

Resource Model Class associate with the Blog Model

Here is the code for Blog resource model (app/code/local/Externaldb/Blog/Model/Resource/Blogpost.php),

/**
 * Description of Externaldb_Blog_Model_Resource_Blogpost
 *
 * @author thanura
 */
class Externaldb_Blog_Model_Resource_Blogpost extends Mage_Core_Model_Resource_Db_Abstract{
 
 public function _construct() {
  $this->_init('blog/blogpost', 'blog_id');
 }
 
 //This function is to load the data using the Read Adapert
 //to show you that I have connected to the external db
 public function readDataFromTable(){
  $readAdapter = $this->_getReadAdapter();
  
  $select = $this->_getReadAdapter()->select()
     ->from($this->getMainTable());
            
        $data = $readAdapter->fetchAll($select);
        
        return($data);
 }
}

Index Controller

Following simple controller will access the Blog Model call to connect to the external database and  retrieve data from blogpost table

/**
 * Description of Index Controller for External DB Module
 *
 * @author thanura
 */

class Externaldb_Blog_IndexController extends Mage_Core_Controller_Front_Action{
 
 public function indexAction(){
  $model  = Mage::getModel('blog/blogpost');
  $results = $model->readDataFromResource();
  
  var_dump($results);
 }
 
}

So thats all about it, now you can connect to a different database using a custom Magento Module. Hopefully this tutorial will help some of you guys out there, and if you have any questions, feel free to ask me. I'll try my best to answer them.
Enjoy!!