How to connect to multiple databases within Drupal
Drupal can connect to different databases with elegance and ease!
First define the database connections Drupal can use by editing the $db_url string in the Drupal configuration file (settings.php for 4.6 and above, otherwise conf.php). By default only a single connection is defined
<?php
$db_url = 'mysql://drupal:drupal@localhost/drupal';
?>To allow multiple database connections, convert $db_url to an array.
<?php
$db_url['default'] = 'mysql://drupal:drupal@localhost/drupal';
$db_url['mydb'] = 'mysql://user:pwd@localhost/anotherdb';
$db_url['db3'] = 'mysql://user:pwd@localhost/yetanotherdb';
?>Note that database storing your Drupal installation should be keyed as the default connection.
To query a different database, simply set it as active by referencing the key name.
<?php
db_set_active('mydb');
db_query('SELECT * FROM table_in_anotherdb');
//Switch back to the default connection when finished.
db_set_active('default');
?>Make sure to always switch back to the default connection so Drupal can cleanly finish the request lifecycle and write to its system tables.
Note: It is particularly important to switch back to the active Drupal database prior to any calls to Drupal functions. Errors in the error log about not being able to find the 'system' table are an indication that calls to Drupal functions preceed switching back to the default database.
This only works with two databases of the same type. For example the following code will not work.
<?php
// ... header of the settings.php file
$databases = array (
"default" => "mysql://user:pass@host/db",
"second" => "pgsql://user:pass@host/db"
);
// ...
?>For a complete reference of all Drupal Database Abstraction Layer functions see http://api.drupal.org/api/group/database.