CODEWITHSUNDEEP
mysqlmagento
Josh Pennington
Josh Pennington

Reputation: 6408

Can Magento Connect to Another MySQL database?

I was wondering if it is possible for Magento have a connection to its primary database, as well as another database that does not contain Magento's core information?

For example, I would like to be able to query tables from a WordPress install that is stored on a different database on a different server?

My first instinct was to create a new database connection using mysql_connect, but it does not feel right to do it this way.

Is there a more "proper" way to accomplish this?

Upvotes: 3

Views: 10465

Answers (4)

Jaydeep Raj
Jaydeep Raj

Reputation: 1

If you want to connect to another MySQL database directly from Magento, you can add additional database connections in the app/etc/env.php file.

`'db' => [
    'table_prefix' => '',
    'connection' => [            
        'default' => [
            'host' => 'host_server_ip',
            'dbname' => 'custom_database',
            'username' => 'your_db_username',
            'password' => 'your_db_password',
            'active' => '1',
        ]
    ]
],

`

Upvotes: 0

Prince Patel
Prince Patel

Reputation: 3060

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

Upvotes: 0

OSdave
OSdave

Reputation: 8587

here the complete explanation: http://fishpig.co.uk/magento-tutorials/create-an-external-database-connection-in-magento
in short, you need to create a new resource and tell magento to use this resource for your model, ie, in the config.xml, inside <global> tag: 

    <resources>
        <external_db>
            <connection>
                <host><![CDATA[host]]></host>
                <username><![CDATA[username]]></username>
                <password><![CDATA[password]]></password>
                <dbname><![CDATA[dbname]]></dbname>
                <model>mysql4</model>
                <type>pdo_mysql</type>
                <active>1</active>
            </connection>
        </external_db>
        <yourmodelalias_read>
            <connection>
                <use>external_db</use>
            </connection>
        </yourmodelalias_read>
    </resources>

Upvotes: 9

Tim Reynolds
Tim Reynolds

Reputation: 734

If you look in /lib/Varien/Db/Adapter/Mysqli.php you can see that Magento is extending the Zend_Db_Adapter_Mysqli, and in the _connect() function it is checking if the cached copy of the connection exists immediately.

To me it seems unlikely that using the Magento Db Adapter directly will work, as you would have to override this functionality. So, that leaves two options:

  1. Use the Zend DB adapter, which is pretty easy if you have used it before.
  2. Just use PHP's MySQL functions directly. Make sure to use the PDO or MySQLi libraries, not the ext/mysql. The ext/mysql library is getting deprecated and is a pain to use securely.

http://php.net/manual/en/ref.pdo-mysql.php

http://php.net/manual/en/book.mysqli.php

http://framework.zend.com/manual/en/zend.db.html

Edit: Adding Magento Connect Module

http://www.magentocommerce.com/magento-connect/fishpig/extension/3958/fishpig_wordpress_integration

That is a free Magento->Wordpress bridge. It may be worth looking into their source to see what path they took.

Upvotes: 1

Related Questions