17

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?

18

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

  • sorry,It does not work for me. – bab Nov 18 '13 at 5:53
  • I don't understand where to write this code,where to perform query and what to chage in this.for example wether i type <host><![CDATA[localhost]]></host> or <host>localhost</host> etc. – bab Nov 18 '13 at 6:01
  • @bab are you wanting to make a single query or a model that works with another database? – David Manners Nov 18 '13 at 7:39
  • I am making a single query but if possible through model also.Actually i am working on a survey site . i want to access databse of this site through magento.I read so many articles for the same but i have no idea where to put this code .If possible please tell me what changes i can make in your coding to get desired result.Thanks. – bab Nov 18 '13 at 10:22
  • @bab the second config.xml example should work for the model. did you get any errors with the code? – David Manners Nov 18 '13 at 12:30
3

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.