How to access the external database in drupal?

Question

I want to get the values of the current drupal database and the values from an external database in to a same template file. How can I achieve this

Answer 1

First you need to setup your external database as a second database in settings.php.

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'standard_db',
      'username' => 'username',
      'password' => 'password',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
  'external' => 
  array (
    'default' => 
    array (
      'database' => 'external',
      'username' => 'username',
      'password' => 'password',
      'host' => 'db.external.com',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

Then before you do your database queries you need to set the active database. In this example, to switch to external you need to execute db_set_active('external');

When you want to switch back to the default database you execute db_set_active();

Example code to switch to and query the external database:

db_set_active('external');
$query = db_select("your_table_name", "t");
$query->addField("t", "field_name");
$result = $query->execute();
while ($row = $result->fetchAssoc()) {
  drupal_set_message($row['field_name']);
}

db_set_active();