connect to multiple database from drupal

Drupal has the ability to connect to multiple databases, allowing you to use Drupal’s built in database abstraction layer on more than just Drupal's primary database.

Preferably you would add your configuration in the settings.php file for your site, so that all modules can interact with the new database.

Drupal 7

In your settings.php:

  
$databases = array();
$databases['default']['default'] = array(
  // Drupal's default credentials here.
  // This is where the Drupal core will store it's data.
);
$databases['alternate_db']['default'] = array(
  // Your secondary database's credentials here.
  // You will be able to explicitly connect to this database from your modules.
);
    
 

The way to use it in module:

  
// Use the database we set up earlier
db_set_active('alternate_db');

// Run some queries, process some data
db_query('...............');

//Switch back to the default connection when finished.
// otherwise Drupal will not be able to access it's own data later on.
db_set_active();
    
 

You can setup databases on the fly when you need it

If only one of your module is using alternate database, you can define the connection directly there in module as:

  
$alternate_db = array(
    'database' => 'databasename',
    'username' => 'username',
    'password' => 'password',
    'host' => 'localhost',
    'driver' => 'mysql',
);
// replace 'AlternateDatabaseKey' with something that's unique to your module
Database::addConnectionInfo('AlternateDatabaseKey', 'default', $alternate_db);
db_set_active('AlternateDatabaseKey');

// execute queries here

db_set_active(); // without the paramater means set back to the default for the site
drupal_set_message(t('The queries have been made.'));
    
 

If you are using Drupal 6 or older version of Drupal than it's little different. You can use it by editing $db_url string in configuration file (setting.php of conf.php). There is by default single connection is defined.

  
$db_url['default'] = 'mysqli://user:password@localhost/drupal';

// you can add another connection as
$db_url['alternate_db'] = 'mysqli://user:password@localhost/alternate_db';
    
 

To query a different database, simply set it as active by referencing the key name.

  
// Use the database we set up earlier
db_set_active('alternate_db');

// Run some queries, process some data
db_query('...............');

//Switch back to the default connection when finished.
// otherwise Drupal will not be able to access it's own data later on.
db_set_active('default');