Jaybill.com
Home About Links Contact Archives

Using the Zend Framework with Multiple Databases

September 12th, 2007 by Jaybill McCarthy

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.


Posted in Zend_Db_Table, databases, php, programming, tutorial, zend framework |

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

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

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

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

    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. Paul Says:
    June 3rd, 2008 at 3:07 am

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

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

    great!

    thanks!

    :D

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

    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. Jaybill McCarthy Says:
    July 15th, 2008 at 10:52 am

    @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.

Leave a Reply

© 2007 Jaybill.com

 Subscribe