Using the Zend Framework with Multiple Databases

This article is what I hope will be the first in a series about my favorite way to develop web applications, the Zend Framework. I’m not going to try and sell the framework to you, I’m going to assume you already know all about it and that’s how you got here.

The Zend_Db_Table class provides a powerful and easy-to-use way of abstracting your data model into PHP objects. One problem I recently encountered was that the design of the class assumes you’re only going to use one database, which may not be the case.

Let’s assume you’ve got a Zend application that follows the de facto standard laid out in this tutorial. You’d have something in your application/config.ini file that looked like this:

  1.  
  2. [general]
  3.  
  4. db.adapter = PDO_MYSQL
  5.  
  6. db.config.host = localhost
  7.  
  8. db.config.username = zfuser
  9.  
  10. db.config.password = ninjabunnies
  11.  
  12. db.config.dbname = zftest
  13.  
  14. db.config.port = 3306

Then in your index.php bootstrapper, you’d have something like this:

  1.  
  2. Zend_Loader::loadClass(‘Zend_Controller_Front’);
  3.  
  4. Zend_Loader::loadClass(‘Zend_Registry’);
  5.  
  6. Zend_Loader::loadClass(‘Zend_Config_Ini’);
  7.  
  8. Zend_Loader::loadClass(‘Zend_Db’);
  9.  
  10. Zend_Loader::loadClass(‘Zend_Db_Table’);// load configuration
  11.  
  12. $config = new Zend_Config_Ini(‘./application/config.ini’, ‘general’);
  13.  
  14. Zend_Registry::set(‘config’, $config);
  15.  
  16. // setup database
  17.  
  18. $dbAdapter = Zend_Db::factory($config->db->adapter,
  19.  
  20. $config->db->config->toArray());
  21.  
  22. Zend_Db_Table::setDefaultAdapter($dbAdapter);
  23.  
  24. Zend_Registry::set(‘dbAdapter’, $dbAdapter);

In the above example, we set all the database parameters in the config.ini file. We load these values into dbAdapter and decalre that adapter as the default via the setDefaultAdapter method of Zend_Db_Table. Any classes derived from Zend_Db_Table abstract will use this database parameter. This works fine for one database, but what if we’ve got more than one? We can’t really use the default adapter anymore, at least not in the usual way.

One way to get around this problem is to create an array of adapters and make the key of the array the name of the database. We can then implement an application specific table class that will allow us to easily choose the adapter we want to use. We can then have our application table classes extend that.

For this example, let’s say we’re building a site to sell textbooks, and there’s two databases: library and order_system

Your config.ini would now look something like this:

  1.  
  2. [databases]
  3.  
  4. db.library.adapter = PDO_MYSQL
  5.  
  6. db.library.config.host = localhost
  7.  
  8. db.library.config.username = libraryuser
  9.  
  10. db.library.config.password = bunch0crunch
  11.  
  12. db.library.config.dbname = library
  13.  
  14. db.library.config.port = 3306
  15.  
  16. db.library.default = truedb.order_system.adapter = PDO_MYSQL
  17.  
  18. db.order_system.config.host = localhost
  19.  
  20. db.order_system.config.username = ecommerceuser
  21.  
  22. db.order_system.config.password = showMeThe$$$
  23.  
  24. db.order_system.config.dbname = order_system
  25.  
  26. db.order_system.config.port = 3306

The in your index.php bootstrapper, you would do something more like the following:

  1.  
  2. Zend_Loader::loadClass(‘Zend_Controller_Front’);
  3.  
  4. Zend_Loader::loadClass(‘Zend_Registry’);
  5.  
  6. Zend_Loader::loadClass(‘Zend_Config_Ini’);
  7.  
  8. Zend_Loader::loadClass(‘Zend_Db’);
  9.  
  10. Zend_Loader::loadClass(‘Zend_Db_Table’);// setup databases
  11.  
  12. $databases = new Zend_Config_Ini(‘./application/config.ini’, ‘databases’);
  13.  
  14. $dbAdapters = array();
  15.  
  16. foreach($databases->db as $config_name => $db){
  17.  
  18.  $dbAdapters[$config_name] = Zend_Db::factory($db->adapter,
  19.  
  20.                     $db->config->toArray());
  21.  
  22.  if((boolean)$db->default){
  23.  
  24.     Zend_Db_Table::setDefaultAdapter($dbAdapters[$config_name]);
  25.  
  26.  }
  27.  
  28. }
  29.  
  30. Zend_Registry::set(‘dbAdapters’, $dbAdapters);

All we’re doing here is creating an array of adapters instead of just one. If we set the parameter “default” on one of them to true, that will become our default database. This will save you a bit of typing later if have one database that you’ll use most often and one or more other ones you use less.

Then we create a new class, called App_Db_Table_Abstract. You can put this in /application/models folder, but I like to create a folder called /application/lib for my abstract classes. Either way, just make sure it ends up in your include path.

  1. abstract class App_Db_Table_Abstract extends Zend_Db_Table
  2.  
  3. {
  4.  
  5.     function App_Db_Table_Abstract($config = null){
  6.  
  7.         if(isset($this->_use_adapter)){
  8.  
  9.             $dbAdapters = Zend_Registry::get(‘dbAdapters’);
  10.  
  11.             $config = ($dbAdapters[$this->_use_adapter]);
  12.  
  13.         }
  14.  
  15.         return parent::__construct($config);
  16.  
  17.     }
  18.  
  19. }

All we’re doing here is looking for a class property called “$_use_adapter”. If that’s set (in our derived classes) then we’ll look for that dbAdapter in the registry. If not, we’ll use whatever the default is set to.

Here’s an example of a derived class that would do this. It uses the “orders” table in the “order_system” database.

  1. class Orders extends App_Db_Table_Abstract {
  2.  
  3. protected $_name    = ‘groups’;
  4.  
  5.  protected $_primary    = ‘group_id’;
  6.  
  7.  protected $_use_adapter = ‘order_system’;
  8.  
  9. }

Now this class will use the correct adapater instead of the default. That’s all there is to it!

This method of implementation is nice because you could have one database, two databases or a hundred databases and the only thing you’d need to do would be to add additional sections to the config.ini file.

21 Responses to “Using the Zend Framework with Multiple Databases”

  1. On January 16th, 2008 at 2:27 am links for 2008-01-16 | Dave Kelly :: Blog said:

    [...] Jaybill.com » Blog Archive » Using the Zend Framework with Multiple Databases (tags: webdev zend php database scalability) [...]


  2. On April 3rd, 2008 at 11:26 pm eko said:

    dear sir,
    i’m is student from indonesia and i’m beginer in php, i try to study zf.
    i have question, how to connect 2 db with zf, for example Ms. SQL server (main db) and MySQL (2nd db).
    i use ms. sql to get master data, and i save it to mysql.

    thx’s f u help
    and sorry my english is to bad.


  3. On June 3rd, 2008 at 3:07 am Paul said:

    Excellent article, solved my multi database problems immediately, thanks a lot :)


  4. On June 17th, 2008 at 5:54 am Alan said:

    great!

    thanks!

    :D


  5. On July 11th, 2008 at 7:50 am monk.e.boy said:

    App_Db_Table_Abstract this name is a bit confusing, you’d assume (from the ZF naming convention) that it is found in app/db/table/abstract.php

    What happens if you have 2 DBs both with a ‘foo’ table? Do you put your models in app/model/db/foo.php ?

    In my app I read the ini file/db section in the controller class. This means each different controller can access a different database. But your way is an interesting approach.

    monk.e.boy


  6. On July 15th, 2008 at 10:52 am Jaybill McCarthy said:

    @monk.e.boy – Thanks for the response! Having multiple tables with the same name in different databases shouldn’t be an issue at all – you’d just give them different class names.

    As for your other assertion, I’m not sure why you’d want controllers to know anything about the databases – that pretty much violates the MVC pattern.


  7. On July 21st, 2008 at 7:44 am Larry Eitel said:

    Awesome. Thank you :)


  8. On July 21st, 2008 at 12:25 pm Kris said:

    Thanks for covering this topic! I am having trouble getting it to work with my setup however. I set up all my files just the same as you except my bootstrap file is different. I suspect that the problem may have something to do with using the autoload feature. When I run my app I get an error message that indicates it is looking in the default database even though my derived class is telling it to use the other database. This is what my bootstrap file looks like:

    error_reporting(E_ALL|E_STRICT);
    ini_set(‘display_errors’, 1);
    date_default_timezone_set(‘America/Los_Angeles’);

    // directory setup and class loading
    set_include_path(‘.’ . PATH_SEPARATOR . ‘../library/’
    . PATH_SEPARATOR . ‘/var/application/models’
    . PATH_SEPARATOR . get_include_path());
    include “Zend/Loader.php”;
    Zend_Loader::registerAutoload(); //everthing after this point seems to be the same as what you have

    // set up databases

    include “App_Db_Table_Abstract.php”;
    $databases = new Zend_Config_Ini(‘/var/application/config.ini’, ‘databases’);
    $dbAdapters = array();
    foreach($databases->db as $config_name => $db)
    {
    $dbAdapters[$config_name] = Zend_Db::factory($db->adapter,$db->config->toArray());
    if((boolean)$db->default)
    {
    Zend_Db_Table::setDefaultAdapter($dbAdapters[$config_name]);
    }
    }
    Zend_Registry::set(‘dbAdapters’,$dbAdapters);

    // setup controller
    $frontController = Zend_Controller_Front::getInstance();
    //change to false before the “unveiling”
    $frontController->throwExceptions(true);
    $frontController->setControllerDirectory(‘/var/application/controllers’);
    Zend_Layout::startMvc(array(‘layoutPath’=>’/var/application/layouts’));

    // run!
    $frontController->dispatch();


  9. On July 23rd, 2008 at 4:23 pm Kris said:

    Well I found a typo in one of my files, but it still isn’t working. At least I have a new error message now. “no entry is registered for key dbAdapters”. Can anyone help me shed any light on my situation? Thanks!


  10. On August 4th, 2008 at 5:51 pm Panagiotis Kosmidis said:

    Hi, I’m new to ZF also, i like it, i also like your article about multiple databases, the way you took solves from many lines of code. Well everything works great, i can connect to databases, make queries etc, but when i need to authenticate user login with ZF but i get the error:

    exception ‘Zend_Auth_Adapter_Exception’ with message ‘A value for the identity was not provided prior to authentication with Zend_Auth_Adapter_DbTable.’

    My code is:

    $dbArray = Zend_Registry::get(‘dbAdapters’);
    $adapt = new Zend_Auth_Adapter_DbTable ( $dbArray['sugar'] );
    $adapt->setTableName ( ‘users’ );
    $adapt->setIdentityColumn ( ‘user_name’ );
    $adapt->setCredentialColumn ( ‘user_hash’ );
    $adapt->setCredential ( md5 ( $params ['user_hash'] ) );
    $adapt->setIdentity ( htmlspecialchars ( $params ['user_name'] ) );

    i think that i have to pass into Zend_Auth_Adapter_DbTable an Zend_Db_Adapter_Pdo_Mysql object instead of an array thats why i used $dbArray['sugar'], sugar database contains user authentication data and the other database contains other data.

    but if i try:

    $adapt = new Zend_Auth_Adapter_DbTable ( Zend_Registry::get(‘dbAdapters’) );
    $adapt->setTableName ( ‘users’ );
    $adapt->setIdentityColumn ( ‘user_name’ );
    $adapt->setCredentialColumn ( ‘user_hash’ );
    $adapt->setCredential ( md5 ( $params ['user_hash'] ) );
    $adapt->setIdentity ( htmlspecialchars ( $params ['user_name'] ) );

    i will get the error:

    Catchable fatal error: Argument 1 passed to Zend_Auth_Adapter_DbTable::__construct() must be an instance of Zend_Db_Adapter_Abstract, array given

    so I’m pretty sure that i have to use $dbArrays['sugar']

    Thanks in advance.


  11. On August 4th, 2008 at 6:48 pm Panagiotis Kosmidis said:

    Never mind, silly me, was my fault, all ok now, again thanks for article.


  12. On March 30th, 2009 at 4:45 am Garrett Griffin said:

    Thanks a lot for this! Was exactly what I was looking for and you explained it very well.


  13. On May 6th, 2009 at 7:49 pm Arthur said:

    i´m having some touble… here is my code:
    config.ini ….
    [databases]
    db.dado6.adapter = PDO_MYSQL
    db.dado6.config.host = localhost
    db.dado6.config.username = root
    db.dado6.config.password =
    db.dado6.config.dbname = dado6
    db.dado6.default = truedb.cliente.adapter = PDO_MYSQL
    db.cliente.config.host = localhost
    db.cliente.config.username = root
    db.cliente.config.password =
    db.cliente.config.dbname = cliente
    ……
    index.php
    …
    $databases = new Zend_Config_Ini(‘./application/config.ini’, ‘databases’);
    $dbAdapters = array ();
    foreach ( $databases->db as $config_name => $db ) {
    $dbAdapters [$config_name] = Zend_Db::factory ( $db->adapter, $db->config->toArray () );
    if (( boolean ) $db->default) {
    Zend_Db_Table::setDefaultAdapter ( $dbAdapters [$config_name] );
    }
    }
    Zend_Registry::set (‘dbAdapters’, $dbAdapters );
    …

    And the error is:

    Warning: Error parsing ./application/config.ini on line 8 in C:\wamp\www\Dado6\library\Zend\Config\Ini.php on line 106

    Warning: Invalid argument supplied for foreach() in C:\wamp\www\Dado6\library\Zend\Config\Ini.php on line 108

    Fatal error: Uncaught exception ‘Zend_Config_Exception’ with message ‘Section ‘databases’ cannot be found in ./application/config.ini’ in C:\wamp\www\Dado6\library\Zend\Config\Ini.php:156 Stack trace: #0 C:\wamp\www\Dado\admin\index.php(138): Zend_Config_Ini->__construct(‘./application/c…’, ‘databases’) #1 {main} thrown in C:\wamp\www\Dado6\library\Zend\Config\Ini.php on line 156

    does anybody know why is that?
    thank you!


  14. On May 11th, 2009 at 9:13 am Jaybill McCarthy said:

    @Arthur – The error message you’re getting is telling you exactly what happened, actually. Line 8 of your config.ini file is:

    db.dado6.default = truedb.cliente.adapter = PDO_MYSQL

    That’s wrong. Should have a line break after “true”.


  15. On September 9th, 2009 at 1:42 am Carlos said:

    i can connect to my DB’s, so when i do a query: $licensasTable = new App_Models_Licencas();
    $licensa = $licensasTable->fetchRow($licensasTable->select());

    i have the error: Notice: Trying to get property of non-object

    My Licencas model is:
    class App_Models_Licencas extends Zend_Db_Table_Abstract
    {
    protected $_name = ‘licencas’;
    protected $_primary = ‘ID’;
    protected $_use_adapter = ‘iqdoclicencas’;
    }

    i’m doing something wrong?


  16. On October 7th, 2009 at 3:06 pm Greg said:

    I have insert in boostrap “Zend_Registry::set(‘config’, $dbAdapters);”

    but i have “no entry is registered for key config”


  17. On October 27th, 2009 at 4:06 am chris jones said:

    I came across this webpage and seeing as the post are quite recent I hope you can help. I am hoping you can shed some light on this problem.

    I have the config.ini in the correct directory and I have also specified the section as [general] in the config.ini file and created the line on the index.php
    $config = new Zend_Config_Ini(‘./application/config.ini’, ‘general’);

    When executing the page, all i get is:
    Fatal Error: Class ‘Zend_config_Ini’ not found in /path/to/index.php on line 6

    I’ve tried moving the config.ini to different dirs and using every possible path suggestion to point at it like ./ ../ //full_path_from_hard_drive/config.ini but cant get it to work

    Any ideas??


  18. On October 27th, 2009 at 7:44 am Jaybill McCarthy said:

    @chris jones – It’s not your ini file that’s missing, it’s the Zend Framework. “Fatal Error: Class ‘Zend_config_Ini’ not found in /path/to/index.php on line 6″ means that your script can’t find the Zend Framework classes. Check your include path or add the following line to your index.php

    set_include_path(get_include_path() . PATH_SEPARATOR . “/path/to/ZendFramework/Zend”);

    Make sure your include path is to the folder that contains “library”.


  19. On November 6th, 2009 at 10:23 am links for 2009-11-06 | AndySowards.com :: Professional Web Design, Development, Programming Freelancer, Hacks, Downloads, Math and being a Web 2.0 Hipster? said:

    [...] Jaybill.com » Using the Zend Framework with Multiple Databases Another good look at Zend DB – This time tackling the problem of needing to access 2 databases instead of just one. (tags: zend database php zendframework multiple db tutorial framework) [...]


  20. On November 7th, 2009 at 2:35 am links for 2009-11-07 | AndySowards.com :: Professional Web Design, Development, Programming Freelancer, Hacks, Downloads, Math and being a Web 2.0 Hipster? said:

    [...] Jaybill.com » Using the Zend Framework with Multiple Databases Another good look at Zend DB – This time tackling the problem of needing to access 2 databases instead of just one. (tags: zend database php zendframework multiple db tutorial framework) [...]


  21. On January 26th, 2010 at 1:03 am Steve Dowe said:

    Great tip, thanks very much.

    For those who are having trouble implementing this after following a Zend Framework tutorial, note that the Orders class declaration will go into models/DbTable/Orders.php and not models/Orders.php.

    This is because you are abstracting the Zend_Db_Table class.

    HTH somebody!


Leave a Reply

Note: If you've never commented on Jaybill.com before, your comment will not be visible immediately. Someone will need to approve it first. After we approve one of your comments (which means we think you're not a robot) all future comments will show up immediately. Please do not post your comment twice.

September 12th, 2007
Tags: , , , , ,
 
 

Picture