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?

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

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.

Answer 3

Hi ! ; 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