Connecting To Multiple Databases In Drupal 7

There are situations when you need to transfer data from one database to another. This article is meant to cater for such instances.

As it happens at times in the website development process, the data needs to be transferred from one database to another (data export\import) both quickly and efficiently, and also in strict compliance with Drupal 7 procedures. This article is meant to cater for the instances mentioned above. Let’s consider the three options for performing the above transfer:

Now we will check up the default picture of settings.php in Drupal 7 :

$databases = array (
  //key
  'default' => 
  array (
    //target
    'default' => 
    array (
      'database' => 'users',
      'username' => 'root',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

As it is illustrated above, we are in a possession of both the key — default and template — also default.
This is the way Drupal 7 gets connected to the base by default, as it had been set out in the course of installation procedures.

Now we are going to consider all of these options one by one.

1. using Database::getConnection()

Changes in settings.php

//this is connection by default
$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'users',
      'username' => 'root',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
  //this is how the connection to other base is prescribed
  'import' => 
  array (
    'default' => 
    array (
      'database' => 'database',
      'username' => 'root',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

At this point we have got a new key, which is called «import» and it has one more template which is named «default».
Now let’s write the connection:

$import = Database::getConnection($target='default',$key='import');
// into the variable import gets the object which has query method by the help of which we can implement the requests
$result = $import->query($sql,$args,$opts);

This is by no means the best way of doing things as when we work with different types of bases we need to write a number of requests with different syntax.

2. using Database::addConnectionInfo() і db_set_active()

// form massif of connection to other base 
$other_database = array(
      'database' => 'databasename',
      'username' => 'username',
      'password' => 'password', 
      'host' => 'localhost', 
      'driver' => 'mysql',
  );
  // after this announce our connection our connection in Database::addConnectionInfo()
  // first parameter is key, the second one - template (target), the third one - masiff with parameters of connection
  Database::addConnectionInfo('YourDatabaseKey', 'default', $other_database);
  // make this connection active
  db_set_active('YourDatabaseKey');

  // all requests that are after db_set_active
  // will adress to the base connection of which we denoted in the parameter db_set_active
  db_set_active(); // to make connection active by default we write the following db_set_active with an empty parameter()

In case you don't have access to settings.php this option fits the task the best.

3. Adding the third argument of the function db_select, db_insert, db_update, etc.

Changes in settings.php

$databases = array (
  'default' => 
  array (
    'default' => 
    array (
      'database' => 'users',
      'username' => 'root',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
    'import' => 
    array (
      'database' => 'database',
      'username' => 'root',
      'password' => '',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

As you may have noticed we didn’t create a new key but added a new template (target), instead, to the default key. It is because our next example doesn’t support connection with other keys.

Let’s check the request up with the help of db_select() function

// in the db_ select function as the third parameter we transfer masiff where the key will be "target" and the meaning will be the one we prescribed in settings.php, in our case it's "import"
$import = db_select('table', 't', array('target' => 'import'))
  ->fields('t', array('fields1'))
  ->condition('t.fields2', 'field')
  ->condition('t.fielduser', $user->uid)
  ->execute()
  ->fetchAssoc();

In my opinion this one is the most suitable as the connection is constantly within a reach, and we can easily control it in (during) each request.
We can make use of mysql_connect() or PDO as well, but as long as we are having Drupal in issue, the thing is pre-installed.
Our Drupal development agency assumes that thorough research of the current article will help you avoid problems and insure easy handling of all sorts of bases in Drupal 7.