Follow Me on Twitter

Follow Me on Twitter

MyApps

An apps to begin With..

Create An External Database Connection in Magento

Creating The Connection

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?xml version="1.0"?>
<config>
    <modules>
        <Fishpig_Externaldb>
            <version>0.1.0</version>
        </Fishpig_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 module and from now on, each time you load Magento, a second database connection will be created

Accessing The Database

When I initially found 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:

1
2
3
4
5
6
7
<?php
    $resource   = Mage::getSingleton('core/resource');
    $conn       = $resource->getConnection('externaldb_read');
    $results    = $conn->query('SELECT * FROM tblName');
    print_r($results)

This works fine, however kind of takes the point away from having this connection available in Magento.

Accessing The 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. For this example I will pretend I am integrating Magento with a system that lists books (reading is cool!).

Creating The Model Class Files

Create the following files:

code/local/Fishpig/Externaldb/Model/Book.php

1
2
3
4
5
6
7
8
9
<?php
class Fishpig_Externaldb_Model_Book extends Mage_Core_Model_Abstract
{
    public function _construct()
    {
        $this->_init('externaldb/book');
    }
}

code/local/Fishpig/Externaldb/Model/Mysql4/Book.php

1
2
3
4
5
6
7
8
9
<?php
class Fishpig_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/Fishpig/Externaldb/Model/Mysql4/Book/Collection.php

1
2
3
4
5
6
7
8
<?php
class Fishpig_Externaldb_Model_Mysql4_Book_Collection extends Mage_Core_Model_Mysq4_Collection_Abstract
{
    public function _construct()
    {
        $this->_init('externaldb/book');
    }
}

That’s the bare minimum needed for your models to be able to access the 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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
<?xml version="1.0"?>
<config>
    <modules>
        <Fishpig_Externaldb>
            <version>0.1.0</version>
        </Fishpig_Externaldb>
    </modules>
    <global>
        <models>
            <externaldb>
                <class>Fishpig_Externaldb_Model</class>
                <resourceModel>externaldb_mysql4</resourceModel>
            </externaldb>
            <externaldb_mysql4>
                <class>Fishpig_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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<?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());
    }

Conclusion

This is one of my first blogs 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. I’ve written a very good WordPress/Magento plugin which gives access to all WordPress post/category/image/link etc information. If you would like to discuss this plugin or would like to use it in one of your sites then please let me know!

Reference: http://fishpig.co.uk/blog/create-an-external-database-connection-in-magento.html

One thought on “Create An External Database Connection in Magento
  • Villa says:

    Hi, I try as you suggested but i get this magesse error:We were unable to preview your templatePlease correct the error below, and submit your template again. Your template could not be parsed as it is not well-formed. Please make sure all XML elements are closed properly. XML error magesse: Open quote is expected for attribute {1} associated with an element type id .Help. Thank you.Reply

Leave a Reply

Your API connection setting not working. try to change setting from module option or check your php.ini setting for (cURL support = enabled OR allow_url_fopen = On)

Your email address will not be published. Required fields are marked *

*


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>