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> |
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) |
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
|
<?phpclass 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
|
<?phpclass 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
9
|
<?phpclass Fishpig_Externaldb_Model_Mysql4_Book_Collection extends Mage_Core_Model_Mysq4_Collection_Abstract{ public function _construct() { $this->_init('externaldb/book'); }} |
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
18
|
<?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!
