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

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 codewill not work.

<? php
// ... header of the settings.php file
 $db_url = array ("default" => "mysql://user:pass@host/db","second" => "pgsql://user:pass@host/db"); 
// ...
?>

Set up multiple database on the fly

<? php
global $db_url; // the internal variable that contains database linkif if(!is_array($db_url)) {
  $default_db = $db_url;
  $db_url = array('default' => $default_db);
} 
//set up the new database value
$db_url['mydb'] = 'mysql://user:pwd@localhost/anotherdb';db_set_active('mydb');    // activation & execution same as explained above 
$results = db_query($sql); //sql represents the query to be executed db_set_active('default'); // set back to original
?>

Ref:http://drupal.org/node/18429