connecting to two different databases with Zend Framework

Question

I have here a medium sized intranet site which is written entirely in Zend FW. The database for the intranet is located on another server. Now I need to extend the intranet with some new functionality. In order to do this I need to connect to another database on the same server (and same DBMS).

The question is now: What is the best way to do this? Should I create a new Zend_Config object and a new Zend_Db_Adapter? Or should I use the existing one and try with the "use otherdbname;" statement to connect within the same session to the new database?

Or is there an even better way to do it?

Solution 1

One option is to register 2 database handles from within your bootstrap.php, one for each connection. E.g.:

$parameters = array(
                    'host'     => 'xx.xxx.xxx.xxx',
                    'username' => 'test',
                    'password' => 'test',
                    'dbname'   => 'test'
                   );
try {
    $db = Zend_Db::factory('Pdo_Mysql', $parameters);
    $db->getConnection();
} catch (Zend_Db_Adapter_Exception $e) {
    echo $e->getMessage();
    die('Could not connect to database.');
} catch (Zend_Exception $e) {
    echo $e->getMessage();
    die('Could not connect to database.');
}
Zend_Registry::set('db', $db);

$parameters = array(
                    'host'     => 'xx.xxx.xxx.xxx',
                    'username' => 'test',
                    'password' => 'test',
                    'dbname'   => 'test'
                   );
try {
    $db = Zend_Db::factory('Pdo_Mysql', $parameters);
    $db->getConnection();
} catch (Zend_Db_Adapter_Exception $e) {
    echo $e->getMessage();
    die('Could not connect to database.');
} catch (Zend_Exception $e) {
    echo $e->getMessage();
    die('Could not connect to database.');
}
Zend_Registry::set('db2', $db);

In your controllers (e.g.):

public function init()
{
     $this->db = Zend_Registry::get('db');
     $this->db2 = Zend_Registry::get('db2');
}

public function fooAction()
{
    $data = $this->db2->fetchAll('select foo from blah');
    ...
}

Solution 2

I am Using this Config.ini you can you can use it also :

[production]
#Debug output
phpSettings.display_startup_errors = 0
phpSettings.display_errors = 0
# Include path
includePaths.library = APPLICATION_PATH "/../library"
# Bootstrap
bootstrap.path = APPLICATION_PATH "/Bootstrap.php"
bootstrap.class = "Bootstrap"
# Front Controller
resources.frontController.controllerDirectory = APPLICATION_PATH "/controllers"
resources.frontController.env = APPLICATION_ENV
# Layout
#resources.layout.layout = "layout"
#resources.layout.layoutPath = APPLICATION_PATH "/layouts/scripts"
# Views
resources.view.encoding = "UTF-8"
resources.view.basePath = APPLICATION_PATH "/views/"
# Database
resources.db.adapter = "pdo_mysql"
resources.db.params.host = "localhost"
resources.db.params.username = "root"
resources.db.params.password = ""
resources.db.params.dbname = "world"
resources.db.isDefaultTableAdapter = true
# Session
resources.session.save_path = APPLICATION_PATH "/../data/session"
resources.session.remember_me_seconds = 864000
[testing : production]
#Debug output
phpSettings.display_startup_errors = 1
phpSettings.display_errors = 1
# Database
resources.db.params.dbname = "myproject_testing"
[development : production]
#Debug output
phpSettings.display_startup_errors = 1
phpSettings.display_errors = 1
# Database
resources.db.params.dbname = "myproject_development"

you can use it for production , testing and development enviroment if you need to connect to another DB at the same time you can double the config of database like :

resources.db2.adapter = "pdo_mysql"
resources.db2.params.host = "localhost"
resources.db2.params.username = "root"
resources.db2.params.password = ""
resources.db2.params.dbname = "world"
resources.db2.isDefaultTableAdapter = true

then you can load it on the bootstap or where ever you like :) and its also easy

Solution 3

one of the best way is:

create new model table for any table on database:

class Article extends Zend_Db_Table_Abstract  
{    
    protected $_name = 'id';
    public  function __construct()  {
        $adaptor = new Zend_Db_Adapter_Pdo_Mysql(array(
            'host'     => 'localhost',
            'username' => 'username',
            'password' => 'password',
            'dbname'   => 'database'

        ));
        $this->_db = $adaptor;
        parent::__construct();
    }

    // your functions goes here
    public function add($data) {
        // any syntax
    }
}

Solution 4

I think this depends on how often you have to switch databases. Using two different adapters will differentiate between the two databases more cleanly and would be my preference.

When you're switching databases on your single adapter you'll surely have a hard time tracing which database is currently active - keep in mind that your database connection is most likely a singleton which is passed on between modules, their controllers and their respective models.

The third option would be to use explicit table names throughout your application. MySQL for example provides the db_name.table_name-syntax to address tables in different databases on the same server. The default database does not matter this way and Zend_Db_Table and Zend_Db_Select support this syntax out of the box.

EDIT:

I must add that option 2 and 3 will only work if your database user has the appropriate access rights on all the databases, tables and columns you want to use. Option 1 will be the only option left, if your database requires a different user on each of your databases.

Solution 5

From what I've found here, in order to use different databases in a Zend application, you can follow one of these two possible ways, according to your needs:

- Having same host/user for the two databases

You can specify the database you want to use initializing the $_schema variable in the model, as follows:

class Customer extends Zend_Db_Table_Abstract
{
    protected $_name   = 'customer';
    protected $_schema = 'db_name';

    ....
}

- Having different host/user for the two databases

In application.ini you have to write the configuration for both databases as follows:

resources.multidb.local.adapter                 = pdo_mysql
resources.multidb.local.host                    = localhost
resources.multidb.local.username                = user
resources.multidb.local.password                = ******
resources.multidb.local.dbname                  = db_name_1
resources.multidb.local.default                 = true

resources.multidb.remote.adapter                = pdo_mysql
resources.multidb.remote.host                   = remote_host
resources.multidb.remote.username               = user
resources.multidb.remote.password               = ******
resources.multidb.remote.dbname                 = db_name_2
resources.multidb.remote.default                = false

Adding the _initDbRegistry block to bootstrap will add the databases to the registry, so you'll be able to access them:

<?php

class Bootstrap extends Zend_Application_Bootstrap_Bootstrap
{

    /**
     * Add databases to the registry
     * 
     * @return void
     */
    public function _initDbRegistry()
    {
        $this->bootstrap('multidb');
        $multidb = $this->getPluginResource('multidb');
        Zend_Registry::set('db_local', $multidb->getDb('local')); //db_local is going to be the name of the local adapter
        Zend_Registry::set('db_remote', $multidb->getDb('remote')); //db_remote is going to be the name of the remote adapter
    }

}

Now you can specify the adapter you want to use for each model, as follows:

class Customer extends Zend_Db_Table_Abstract
{
    protected $_name    = 'customer';
    protected $_schema  = 'db_name_1';
    protected $_adapter = 'db_local'; //Using the local adapter

    ....
}

class Product extends Zend_Db_Table_Abstract
{
    protected $_name    = 'product';
    protected $_schema  = 'db_name_2';
    protected $_adapter = 'db_remote'; //Using the remote adapter

    ....
}

View more solutions