A web developer's blog. PHP, MySQL, CakePHP, Zend Framework, Wordpress, Code Igniter, Django, Python, CSS, Javascript, jQuery, Knockout.js, and other web development topics.

Zend Framework: Connecting to 2 databases

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);
    }
}
This entry was posted in General and tagged , , , . Bookmark the permalink.

12 Responses to Zend Framework: Connecting to 2 databases

  1. Wenbert says:

    Yet another post intended for myself :P Comments/suggestions — just leave a reply.

  2. Pingback: rollenc拼博

  3. yakakey says:

    thanks,一路追寻到此处

  4. PixelMaker says:

    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.

  5. Wenbert says:

    Hmmm. I do not know the recommended way to do it but the way would be something like this (the dirty way):

    $sql = "SELECT a.*, b.* FROM database1.table1 a 
    LEFT JOIN database2.table2 b ON b.id=a.ass_id
    WHERE a.status=".$this->_db->quote('A');
     
    return $this->_db->fetchAll($sql);

    It is basically just a table join between two tables. The SQL output would be something like:

    SELECT a.*, b.* 
    FROM database1.table1 a 
    LEFT JOIN database2.table2 b ON b.id=a.ass_id 
    WHERE a.STATUS = "A"
  6. 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.

  7. Pingback: Connect to Multiple Databases with Zend Framework | Made of Everything You're Not | Eric Lamb

  8. hamza says:

    extramly ugly framework for newbie
    really hate to use it.
    incomplete details with less guidance from people.

  9. hamza says:

    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
    {
    }

  10. Wenbert says:

    @hamza, yes ZF has a very steep learning curve. Have you tried Code Igniter? It is also quite a good framework.

  11. Francis says:

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

  12. Utterly indited written content, Really enjoyed examining.

Leave a Reply to chun li costume Cancel reply

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>