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?

Sorbose

Answer 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');
    ...
}

Lifeordeath

This would be a solution. But then I would not be using Zend_Db_Table. I would have to be able to choose the Zend_Db_Adapter within Zend_Db_Table. But your answer already helped. – Sorbose

@Raffael Luthiger - I made no mention of Zend_Db_Table, I don't see where that fits in to my answer :) – Lifeordeath

@karim79: I thinks he's talking about the default database adapter for Zend_Db_Table. If he uses two differen adapters he'll have to associate his table-classes with the correct db-adapter respectively. That's why I proposed using the fully qualified table-name syntax. – Kensell

@Stefan Gehrig - ah, I get it now. I'm not familiar with Zend_Db_Table, that comment confused me a bit. Thanks for the clarification. – Lifeordeath

@Stefan. Correct. Thank you for clarifying it. You couldn't have wrote it better myself. – Sorbose

I have taken your answer now. Thanks!.. One remark: If you are instantiating DbTable object then you can provide the name of the adapter like this: $table = new Default_Model_DbTable_Users(array('db' => 'dbAdapter2')); This way you do not have to use Zend_Registry::get('dbAdapter2'); – Sorbose

@Lifeordeath I know this is an old post, but I am actually trying ZF for the first time and I also need to make connections to multiple databases. If I use this solution, does it mean that I have several DB connections opened all the time or only when I call Zend_Registry::get('db') for instance. This is from a performance point of view. Thank you – Preempt

$db->getConnection() at bootstrap could be considered expensive if you don't use both databases in each request. – Jockey

Answer 2

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.

Kensell

I will have the same user for both databases. So option 2 and 3 would work. I have to say that I tried option 3 but it did not work. But it could very well could have been a problem with my syntax. – Sorbose

Which DBMS? Which errors do you get? When using MySQL you perhaps will have to quote the db-name as well as the table-name using back-ticks. – Kensell

Sadly it is not MySQL. My customer "forced" me to use MSSQL. According to the documentation I have to use there db.dbo.table. But probably my problem is with the different quoting and the spaces in the table names. The quoting is normally like this: [table name].[column name] But Zend_DB is change by default to standard "table name". – Sorbose

Answer 3

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

Combatant

Thanks for the information. It is about the same as Karim79 proposed. You are doing it with the config file only. It would have been nice you would have copied only the relevant code.. and put it within pre tags. then it would have been better readably. But thanks anyway. – Sorbose

Thank you for reformatting your answer. I gave you one point because I will use your answer together with answer from Karim79. – Sorbose

Thank you Raffael very much , i am really appreciate it :) – Combatant

+1 Thanks for this useful configuration. It contains most of solutions for my errors/problems. – Popple

It contains resources.db.params.dbname line two time. One for 'world' and one for 'myproject_testing' in the end. Will this generate any problem or conflict. – Popple

@Awan: No. it is not a problem because they are in different "environment". Once in [production] and once in [testing]. – Sorbose

Answer 4

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

Unbated

Like this you have the configuration hardcoded in the model. When you would like to have the software installed on three different servers with different databases then you run into problems whenever you are making updates of the software. – Sorbose

Answer 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

    ....
}

Loris