Handling Connections to Multiple Databases in the Zend Framework

Written by James Mansson on March 29, 2013 Categories: Databases, Zend Framework 1

While many websites and web applications work with a single database, there are occasions when they need to work with more than one database. The Zend Framework provides a resource plugin called Zend_Application_Resource_Multidb, which enables multiple database connections to be configured via the application configuration file.

The first step is to add the settings to your application configuration file, usually called application.ini, and located in the application/configs directory. If we had three database connections, the configuration would look something like this:

resources.multidb.mydb1.adapter = PDO_MYSQL
resources.multidb.mydb1.host = localhost
resources.multidb.mydb1.username = mydb1user
resources.multidb.mydb1.password = password
resources.multidb.mydb1.dbname = mydb1

resources.multidb.mydb2.adapter = PDO_MYSQL
resources.multidb.mydb2.host = localhost
resources.multidb.mydb2.username = mydb2user
resources.multidb.mydb2.password = password
resources.multidb.mydb2.dbname = mydb2

resources.multidb.mydb3.adapter = PDO_MYSQL
resources.multidb.mydb3.host = localhost
resources.multidb.mydb3.username = mydb3user
resources.multidb.mydb3.password = password
resources.multidb.mydb3.dbname = mydb3

Obviously, the number of configuration blocks will vary by the number of connections; each block should have its own unique identifier (mydb1, mydb2 and mybd3 in the example above), which corresponds to the database name.

The next step is less obvious, but one technique I have found useful is to create a common base file for all my database connection files that derive ultimately from Zend_Db_Table_Abstract. This is called Application_Model_DbTable_Abstract and in located in application/models/DbTable/Abstract.php, so the default autoloader can find it. The class implementation is as follows:

class Application_Model_DbTable_Abstract 
  extends Zend_Db_Table_Abstract
{
    protected function _setupDatabaseAdapter()
    {
        $bootstrap = Zend_Controller_Front::getInstance()->getParam('bootstrap');
        $multidb = $bootstrap->getPluginResource('multidb');
        $this->_db = $multidb->getDb($this->_schema);
    }
}

The classes derived from this must not only set the $_name member variable to the name of the table, but also $_schema to the name of the database. For instance:

class Application_Model_DbTable_User 
  extends Application_Model_DbTable_Abstract
{
    protected $_schema = 'mydb1';
    protected $_name = 'user';

    ...
}

When an instance of the class is created, Application_Model_DbTable_Abstract will call _setupDatabaseAdapter. This will load in the configuration associated with the database, using the value in $_schema. The value in $_name will be used as usual to specify the table.

No Comments on Handling Connections to Multiple Databases in the Zend Framework

Leave a Reply

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