All Posts in databases

July 20, 2012 - Comments Off on Drupal: Connecting to multiple databases in your module

Drupal: Connecting to multiple databases in your module

Have you ever needed your Drupal site to access data from a different database? from a different Drupal installation? The Drupal database abstraction layer has the ability to switch between databases! We are working on a project that requires a Drupal website to talk to another database on the same server. Updates to the website must also be made to the other database, in this case a membership directory.

By setting up multiple databases in the settings.php file for your site, you can load up your data from the website, then switch to the second database to make updates. If you are doing data migration from one Drupal site to another, this can be very handy.

First, in settings.php expand the default database declaration array:

[cce_php]
$databases['default']['default'] = array(
      'database' => 'drupalsite',
      'username' => 'drupalsite_user',
      'password' => 'drupalsite_pass',
      'host' => 'localhost',
      'port' => '3306',
      'driver' => 'mysql',
      'prefix' => '',
    );
$databases['external']['default'] = array(
      'database' => 'groucho_db',
      'username' => 'groucho',
      'password' => 'groucho',
      'host' => 'localhost',
      'port' => '3306',
      'driver' => 'mysql',
      'prefix' => '',
    );

Then in your new module you can load your data:

/**
 * Implements hook_user_save().
 */
function awesome_user_save(&$user) {
  $user_obj = user_load($user->uid);
  // Load the Users favorite Beatle (George, of course)
  if(isset($user->field_favorite_beatle['und']) {
    $favorite_beatle = $user_obj->field_favorite_beatle['und'][0]['value];
  } else {
    $favorite_beatle = 'George Harrison';
  }

  if(db_set_active('groucho')) {
    $result  = db_update('Users')
        ->fields(array('Fav_Beatle'=>$favorite_beatle))
        ->condition('user_id', $user->uid, '=')
        ->execute();
    // Check on success of query
    if(!$result) {
      // Error executing query
    }
    elseif($result->rowCount() > 0) {
      // Query succeeded- rowCount() will report number of rows affected
    }
    else {
      // Query ran, but no rows were affected
    }
    db_set_active();
  }
}
[/cce_php]

With some error checking in there, and ensuring that db_set_active() is called at the end, you have loaded data from your Drupal site and entered into an external database! This can be run in the other direction too. You can retrieve data from another database and populate Drupal objects like nodes and users directly. The things to watch out for with this method are that the database engines must be the same, i.e., mysql, postgres, etc., and to be sure you find more information of which database is active when you are accessing data.

Published by: chazcheadle in The Programming Mechanism
Tags: , , ,