I was in a situation that I need to connect to 2 different databases.
I started by putting the additional credentials to login into the second database inside my config file: /application/config/app.ini
[production] database.adapter = "PDO_MYSQL" database.params.host = "localhost" database.params.dbname = "Database_A" database.params.username = "root" database.params.password = "1234" psdatabase.adapter = "PDO_MYSQL" psdatabase.params.host = "localhost" psdatabase.params.dbname = "Database_B" psdatabase.params.username = "root" psdatabase.params.password = "1234" [development : production] database.params.dbname = "Database_A" database.params.username = "root" database.params.password = "1234" psdatabase.params.dbname = "Database_B" psdatabase.params.username = "root" psdatabase.params.password = "1234" [testing : production] database.params.dbname = "Database_A" database.params.username = "root" database.params.password = "1234" psdatabase.params.dbname = "Database_B" psdatabase.params.username = "root" psdatabase.params.password = "1234" |
This config file now has 2 sets of database login credentials identified by: database and psdatabase…
Now like any other Zend Framework application, we setup the database connection in the bootstrap file. /application/bootstrap.php
//bootstrap code... $dbAdapter = Zend_Db::factory($configuration->database); Zend_Db_Table_Abstract::setDefaultAdapter($dbAdapter); $registry = Zend_Registry::getInstance(); $registry->configuration = $configuration; $registry->dbAdapter = $dbAdapter; $psdbAdapter = Zend_Db::factory($configuration->psdatabase); $registry->psdbAdapter = $psdbAdapter; //bootstrap code |
What we now have 2 database connections stored in the registry. dbAdapter, which is set as the default adapter and psdbAdapter, which we will be calling everytime we need to call manually when we need to use it.
For example, I have these models.
<?php /** * Psdump.php * /opt/apache2/htdocs/apps/org_v2/application/models/Psdump.php */ require_once APPLICATION_PATH.'/models/PsdumpTable.php'; class Psdump extends Zend_Db_Table { protected $_table; public function getTable() { if(null === $this->_table) { $this->_table = new PsdumpTable; } return $this->_table; } public function fetchAllEmployees() { //return $this->getTable()->fetchActiveEmployees(); return $this->getTable()->getLatestPSDumpTable(); } } |
And this…
<?php /** * PsdumpTable * /opt/apache2/htdocs/apps/org_v2/application/models/PsdumpTable.php * */ class PsdumpTable extends Zend_Db_Table_Abstract { protected $_name = 'psdump'; protected $_psdb; public function init() { //load the other adapter $this->_psdb = Zend_Registry::getInstance('psdbAdapter')->psdbAdapter; } public function insert(array $data) { return parent::insert($data); } /** * fetch all employees with Status = A * * @return unknown */ public function fetchActiveEmployees() { $sql = "SELECT * FROM ".$this->_name." WHERE Status=".$this->_db->quote('A'); return $this->_db->fetchAll($sql); } /** * We are using the psdb here!!! * * @return unknown */ public function getLatestPSDumpTable() { $sql = "SHOW tables"; return $this->_psdb->fetchAll($sql); } } |
Yet another post intended for myself Comments/suggestions — just leave a reply.
Pingback: rollenc拼博
thanks,一路追寻到此处
Hi,
Thanks for sharing this. But how to use joins tables across the databases.
I will explain, I have table1 in db1 and table2 in db2. How to join table1 and table2 they have ass_id as common field name.
Thanks in Advance.
Hmmm. I do not know the recommended way to do it but the way would be something like this (the dirty way):
It is basically just a table join between two tables. The SQL output would be something like:
I follow your website for quite a extended time and should tell that your articles usually prove to be of a high value and high quality for readers.
Pingback: Connect to Multiple Databases with Zend Framework | Made of Everything You're Not | Eric Lamb
extramly ugly framework for newbie
really hate to use it.
incomplete details with less guidance from people.
your bootstrip code is showing error
we need to place your code in between class or outwsdie atleast provide proper deatils
newbiew can not read your mind.
class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{
}
@hamza, yes ZF has a very steep learning curve. Have you tried Code Igniter? It is also quite a good framework.
Hi everyone! I think you have a little mistake here:
public function fetchActiveEmployees()
{
$sql = "SELECT * FROM ".$this->_name." WHERE Status=".$this->_db->quote('A');
return $this->_db->fetchAll($sql);
}
you shoul use
public function fetchActiveEmployees()
{
$sql = "SELECT * FROM ".$this->_name." WHERE Status=".$this->_psdb->quote('A');
return $this->_psdb->fetchAll($sql);
}
Utterly indited written content, Really enjoyed examining.