Amazium bvba, your online partner
Using Different Databases with Zend Framework
  • Share post with Twitter
  • Share post with StumbleUpon
  • Share post with Delicious
  • Share post with Digg
  • Share post with Technorati
  • Share post with Blinklist
2010-05-03 16:50

Using Different Databases with Zend Framework

zend_db_select, zend_db_table, zend framework

A while ago I wrote about a custom application resource for loading multiple DBs. I received a lot of questions and decided it was time for a follow-up on how to use multiple DBs in ZF.

One of the first experiments I did with Application Resources was writing a application resource for loading multiple databases. We generally connect to multiple databases in reporting applications or in scripts where we want to aggregate data from the main database to an offsite database. Since that post I received a lot of questions on how to integrate this in the models and how to join different databases in queries using zend framework. In this post I'll explore how to do both. Since in the meantime someone has used the idea and added a multiple db resource to the Zend Framework, I will use the "official" application resource loader instead of my own.

This tutorial was made with Zend Framework 1.10.4.

Same host / user

So, let's start at the beginning. We have a server, with 2 schemas (databases) on it. Let's assume that the same user has access on both the schemas. You do not need to use the resource loader for loading multiple databases in this case.

You start by adding the database configuration to the application.ini :

resources.db.adapter                            = PDO_MYSQL
resources.db.params.dbname                      = dbtest
resources.db.params.username                    = dbtest
resources.db.params.password                    = ******
resources.db.params.hostname                    = localhost
resources.db.isDefaultTableAdapter              = true

I created 2 databases "dbtest" and "dbtest2". The dbtest database has a table customer and customer_product. The dbtest2 database has a product database:

-- on dbtest

CREATE TABLE `customer` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `firstname` varchar(100) NOT NULL,
  `lastname` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`)
);

INSERT INTO `customer` (`id`, `firstname`, `lastname`) VALUES
(1, 'Jeroen', 'Keppens'), 
(2, 'John', 'Doe'), 
(3, 'Someone', 'Else');

CREATE TABLE `customer_product` (
    `customer_id` int(11) unsigned NOT NULL,
    `product_id` int(11) unsigned NOT NULL,
    PRIMARY KEY  (`customer_id`, `product_id`)
);

INSERT INTO `customer_product` (`customer_id`, `product_id`) VALUES
(1, 3), (2, 1), (3, 2);

-- on dbtest2

CREATE TABLE `product` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `title` varchar(100) NOT NULL,
    PRIMARY KEY  (`id`)
);

INSERT INTO `product` (id, title) VALUES
(1, 'IPad 16Gb Wifi'),
(2, 'IPad 32Gb Wifi'),
(3, 'IPad 64Gb Wifi');

Using Zend_Db_Select

Now, let's first do it the Query way. We'll try to Zend_Db_Table later. When adding your FROM or JOIN clause, you can specify the schema (optional last parameter) in Zend_Db_Select. This will enable you to tell in which schema the database is:

$db Zend_Db_Table::getDefaultAdapter();
$select = new Zend_Db_Select($db);
$select->from('customer'
               array(
'firstname''lastname'))
        ->
join('customer_product'
               
'customer.id = customer_product.customer_id'
               
null)
        ->
join('product'
               
'product.id = customer_product.product_id'
               array(
'title'), 
               
'dbtest2'); // Added "dbtest2" as schema
$customerProducts $db->query($select)->fetchAll();
var_dump($customerProducts);

This will give you the customer products per customer:

array
  0 => 
    array
      'firstname' => string 'Jeroen' (length=6)
      'lastname' => string 'Keppens' (length=7)
      'title' => string 'IPad 64Gb Wifi' (length=14)
  1 => 
    array
      'firstname' => string 'John' (length=4)
      'lastname' => string 'Doe' (length=3)
      'title' => string 'IPad 16Gb Wifi' (length=14)
  2 => 
    array
      'firstname' => string 'Someone' (length=7)
      'lastname' => string 'Else' (length=4)
      'title' => string 'IPad 32Gb Wifi' (length=14)

As you can see it joined as we wanted. Now, this only works because the databases are on the same server and the user has access to both.

Using Zend_Db_Table

When defining your table, you can specify the schema:

class Customer extends Zend_Db_Table_Abstract
{
    protected 
$_name   'customer';
    protected 
$_schema 'dbtest';
    
    protected 
$_dependentTables = array('CustomerProduct');
}

class 
CustomerProduct extends Zend_Db_Table_Abstract
{
    protected 
$_name   'customer_product';
    protected 
$_schema 'dbtest';

    protected 
$_referenceMap    = array(
        
'Customer' => array(
            
'columns'           => 'customer_id',
            
'refTableClass'     => 'Customer',
            
'refColumns'        => 'id'
        
),
        
'Product' => array(
            
'columns'           => 'product_id',
            
'refTableClass'     => 'Product',
            
'refColumns'        => 'id'
        
),
    );
}

class 
Product extends Zend_Db_Table_Abstract
{
    protected 
$_name   'product';
    protected 
$_schema 'dbtest2';
    
    protected 
$_dependentTables = array('CustomerProduct');
}

Now you can easily request the products for a customer like this:

$tblCustomer = new Customer();
$customer $tblCustomer->find(1)->current();
$products $customer->findManyToManyRowset('Product''CustomerProduct');
var_dump($products->toArray());

This will yield the following result:

array
  0 => 
    array
      'customer_id' => string '1' (length=1)
      'product_id' => string '3' (length=1)
      'id' => string '3' (length=1)
      'title' => string 'IPad 64Gb Wifi' (length=14)

Different user/host

Now, it becomes more difficult if you have a different in how mysql connects to the two schemas. For example different users that each have access on one schema or two different servers.

So let's start by setting up our test system. On a different server, I added a table "dbtest", a user dbtest with the same credentials and "%" as host. This way it can be accessed from my local test environment. Obligatory disclaimer: Don't forget to turn this off later because you open up your database to the whole world. In production environments don't use %, but the IP of the calling server.

In application.ini we remove the settings we used before and we replace it by the following:

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

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

In the bootstrap you add the _initDbRegistry block like below, this will add the different databases to the registry:

<?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'));
        
Zend_Registry::set('db_remote'$multidb->getDb('remote'));
    }
    
}

Seperated DB Calls

Let's first see how we set everything up so we can access each database trough Zend_Db_Table seperately before we go to joining.

The models you modify, so they look like this (note the extra $_adapter atribute where we added the registry key for the database):

class Customer extends Zend_Db_Table_Abstract
{
    protected 
$_name    'customer';
    protected 
$_schema  'dbtest';
    protected 
$_adapter 'db_local';
    
    protected 
$_dependentTables = array('CustomerProduct');
}

class 
CustomerProduct extends Zend_Db_Table_Abstract
{
    protected 
$_name    'customer_product';
    protected 
$_schema  'dbtest';
    protected 
$_adapter 'db_local';

    protected 
$_referenceMap    = array(
        
'Customer' => array(
            
'columns'           => 'customer_id',
            
'refTableClass'     => 'Customer',
            
'refColumns'        => 'id'
        
),
        
'Product' => array(
            
'columns'           => 'product_id',
            
'refTableClass'     => 'Product',
            
'refColumns'        => 'id'
        
),
    );
}

class 
Product extends Zend_Db_Table_Abstract
{
    protected 
$_name    'product';
    protected 
$_schema  'dbtest2';
    protected 
$_adapter 'db_remote';
    
    protected 
$_dependentTables = array('CustomerProduct');
}

Ok, now it's quite easy to access each one:

$tblCustomer = new Customer();
$customer $tblCustomer->find(1)->current();
var_dump($customer->toArray());

$tblProduct = new Product();
$product $tblProduct->find(1)->current();
var_dump($product->toArray());

This gives us the following result:

array
  'id' => string '1' (length=1)
  'firstname' => string 'Jeroen' (length=6)
  'lastname' => string 'Keppens' (length=7)

array
  'id' => string '1' (length=1)
  'title' => string 'IPad 16Gb Wifi' (length=14)

Ok, so far so good...

Querying Dependant Rowset

Now, what would happen if we tried the code from above where we got a dependant rowset?

$tblCustomer = new Customer();
$customer $tblCustomer->find(1)->current();
$products $customer->findManyToManyRowset('Product''CustomerProduct');
var_dump($products->toArray());

Well, I must say that the result I got was quite unexpected!

array
  0 => 
    array
      'id' => string '3' (length=1)
      'title' => string 'IPad 64Gb Wifi' (length=14)

In other words, this worked fine! Zend_Db_Table was smart enough to make it work. No

Pushing our luck?

I wondered if we could get Zend_Db_Select to accept this as well. So I decided to give it a shot... I soon realized however that this would never work. Zend_Db_Select does nothing else than to compose a query and then sent it to a database server. This would mean that (in my case) mysql needed to support this and that's just not the case.

Conclusion

So, what did we learn? We can connect to multiple database from our application. We can configure our Db_Tables to use the correct database so we don't have to worry about it. We can even request depending rowsets trough the use of Zend_Db_Table, without having to worry where the data came from.

What can't we do? Well, it's quite easy: you can not write your own queries (hardcoded or by using Zend_Db_Select) getting data from two different databases.

I hope you enjoyed the tutorial!

Have fun,

Jeroen

Comments and Feedback
Lovely article! Thank you for the a great and simple tutorial!
Hello Jeroen. I must say that I really enjoy your blog. It gives a good insight in Zend Framework in a structured and accessible way which is highly appreciated. However, for some reason the above tutorial doesn't work for me. I use ZF 1.10.5, and use the autoloader function, initiated trough:

require_once 'Zend/Loader/Autoloader.php';
$autoloader = Zend_Loader_Autoloader::getInstance();
$autoloader-setFallbackAutoloader(true);
But when i try calling the db class I've created in my model by using:

new Application_Models_Test();
I just get the error message:

Fatal error: Class 'Application_Models_Test' not found in
/home/lobo/application/controllers/TestController.php on line 42
Can it be because my folder is application/model instead of Applicaion/Model? All my other controllers and views work regardless of this, so it seams a bit unlikely..

Cheers
Lobo
Hi Jeroen, I am very first ZF user. Thank you for your posts and I really enjoy. but now I faced some problems when I followed as your post.

At Same host / user section, my test_proj was OK, when I followed Using Zend_Db_Select sector.

after that I create 3 modes under application/models/
name as Customer.php, Product.php and CustomerProduct.php. And I copy and paste
" $tblCustomer = new Customer();
$customer = $tblCustomer-find(1)-current();
$products = $customer-findManyToManyRowset('Product', 'CustomerProduct');
var_dump($products-toArray());
"
this code into the IndexController.php -IndexAction()

at the time it was broken. it doesn't show anything on the browser.

so I change the "$tblCustomer = new Customer();" to "$tblCustomer = new Applicatioin_Models_Customer();" and "
class Customer extends Zend_Db_Table_Abstract" to "class Application_Models_Customer extends Zend_Db_Table_Abstract". But it isn't working. Please can you tell me how should I do.

Thank
Thu Ra
Great article,
but property $_adapter doesn't exists in parent class. you have to add some logic to the init() function or?
Thanks Jeroen!
Very very helpful for me! :D
Hi,
i can't find the $_adapter attribute documented anywhere in the web (I googled a lot) and in none of my books.

Can you tell me where I can find documentation about the use of the $_adapter ?

thank you a lot
Thanks for this nice tutorial.. really impressive blog.