how to connect another database from magento

Question

Is it possible to connect to another database from Magento and accessing data?

If I need to create a module, how can I create a module to access another database? Is there any tutorial which tells about the same from scratch? Any Idea?

Answer 1

First thing you need to do is create a connection in your module's config.xml. It should look similar to the default_setup in your /app/etc/local.xml. Here you can specify the host to be localhost and then set a different dbname or you can specify a different host completely. I have also used a socket before which works also.

<resources>
    <new_db>
        <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>
    </new_db>
</resources>

Now after this you will be able to connect to this database an perform queries as follows:

$new_db_resource = Mage::getSingleton('core/resource');
$connection = $new_db_resource->getConnection('new_db');
$results    = $connection->query('SELECT * FROM table');

If you want to do this via a model then you can specify the read, write and setup resources as follows. This will again be done inside the resources node in your config.xml and you should replace test with what your model has been setup as.

<resources>
    <new_db>
        <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>
    </new_db>
    <test_write>
        <connection>
            <use>new_db</use>
        </connection>
    </test_write>
    <test_read>
        <connection>
            <use>new_db</use>
        </connection>
    </test_read>
    <test_setup>
        <connection>
            <use>new_db</use>
        </connection>
    </test_setup>
</resources>
<models>
    <test>
        <class>My_Test_Model</class>
        <resourceModel>test_resource</resourceModel>
    </test>
    <test_resource>
        <class>My_Test_Model_Resource</class>
        <entities>
            <test>
                <table>test</table>
            </test>
        </entities>
    </test_resource>
</models>

The model itself will try to find it's connection information in the function getConnection /app/code/core/Mage/Core/Model/Resource.php. If you log the $name passed in you will see values like poll_write, tag_write and cms_read where the first part matches the models section in the config.xml, in our case you would see test_write, test_read or test_setup. If it cannot find a connection matching this then it will use the default connections core_read, core_write or core_setup

Answer 2

After reading all these answers,searching and making some test i found this solution. Here is my blog where I wrote the solution.

Working with Magento 1.9 I was asked to make multiple read and write connections. Magento has the possibility to configure read and write connections in the /etc/local.xml. Just set the tag use to let Magento know which one is available.

<default_setup>
    <connection>
        <!-- LOCALHOST -->
        <host>localhost</host>
        <username>root</username>               
        <password>123456</password>
        <dbname>magento_db</dbname>
        <initstatements>SET NAMES utf8</initstatements>
        <model>mysql4</model>
        <type>pdo_mysql</type>
        <pdotype></pdotype>
        <active>1</active>
    </connection>
</default_setup>
<default_read>
    <connection>
        <use/>
        <!-- ANOTHER SERVER -->
        <host>other_server</host>
        <username>root</username>               
        <password>123456</password>
        <dbname>magento_db</dbname>
        <initstatements>SET NAMES utf8</initstatements>
        <model>mysql4</model>
        <type>pdo_mysql</type>
        <pdotype></pdotype>
        <active>1</active>
        </use></connection>
</default_read>
<default_write>
    <connection>
        <use/>
        <!-- LOCALHOST -->
        <host>localhost</host>
        <username>root</username>               
        <password>123456</password>
        <dbname>magento_db</dbname>
        <initstatements>SET NAMES utf8</initstatements>
        <model>mysql4</model>
        <type>pdo_mysql</type>
        <pdotype></pdotype>
        <active>1</active>
        </use></connection>
</default_write>

We can define n connections in the same config file like this test example

<test_read>
 <connection>
   <!-- TEST SERVER -->
   <host>test_server</host>
   <username>root</username>
   <password>123456</password>
   <dbname>magento_db</dbname>
   <initstatements>SET NAMES utf8</initstatements>
   <model>mysql4</model>
   <type>pdo_mysql</type>
   <pdotype></pdotype>
   <active>1</active>
 </connection>
</test_read>

The limit is that the connections are applied to the whole system but my idea is to set only for certain resources. In this case I have a custom report module where I only want to make read connections in the Order table. After overriding the Order resource Mage/Sales/Model/Resource/Order.php Just make 3 updates

1. Make a flag to know if its time to change the connection $reportConnection.
2. Update function _construct() to create the custom connection and add it to the array of resources.
3. Update function _getConnection() to decide if use the custom connection.

//flag
public $reportConnection = false;

/**
*Just add the connection defined in the local.xml 'test_read'
*/
protected function _construct(){
    $this->_init('sales/order', 'entity_id');
    $this->_resources->getConnection('test_read');
}

/**
*Make the connection if the flag is set
*/
protected function _getConnection($connectionName){
 if (isset($this->_connections[$connectionName])) {
   return $this->_connections[$connectionName];
    }

   if($connectionName == 'read' && $this->reportConnection)
        $this->_connections[$connectionName] = $this->_resources->getConnection('test_read');
   else{
   if (!empty($this->_resourcePrefix)) {
      $this->_connections[$connectionName] = $this->_resources->getConnection(
      $this->_resourcePrefix . '_' . $connectionName);
  } else {
   $this->_connections[$connectionName] = $this->_resources->getConnection($connectionName);
  }
   }
   return $this->_connections[$connectionName];
}

Last step is to make call an Order collection but using the test_read connection.

//Get the Order model
$model = Mage::getModel('sales/order');
//set the flag
$model->getResource()->reportConnection = true;
//get the collection
$collection = $model->getCollection();

Answer 3

In your module etc/config.xml add following code:

<global>
    <resources>
        <modulename_write>
            <connection>
                <use>modulename_database</use>
            </connection>
        </modulename_write>
        <modulename_read>
            <connection>
                <use>modulename_database</use>
            </connection>
        </modulename_read>
        <modulename_setup>
            <connection>
                <use>core_setup</use>
            </connection>
        </modulename_setup>
        <modulename_database>
            <connection>
                <host><![CDATA[localhost]]></host>
                <username><![CDATA[db_username]]></username>
                <password><![CDATA[db_password]]></password>
                <dbname><![CDATA[tablename]]></dbname>
                <model>mysql4</model>
                <type>pdo_mysql</type>
                <active>1</active>
            </connection>
        </modulename_database>
    </resources>
</global>

To get data from table using new database:

<?php 
    $resource   = Mage::getSingleton('core/resource');
    $conn       = $resource->getConnection('modulename_read');
    $results    = $conn->fetchAll('SELECT * FROM tablename');

    echo "<pre>";
    print_r($results);
?>