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.
The “proper” way to do it (according to Pro Drupal Development and the Module Developer’s Guide) is to change $db_url in your settings.php file from a string to an array, e.g.:
$db_url['default'] = 'mysqli://user:password@localhost/drupal';
$db_url['alternate'] = 'mysqli://user:password@localhost/alternate_db';
Then you can switch from one database to another quite easily:
db_set_active('alternate');
// do stuff...
db_set_active('default');
What if you want your alternate database to be configurable through a settings page, though? You can’t set that in the settings.php file. The solution is pretty simple, actually: just edit $db_url to add in your new database.
global $db_url;
if ( !is_array($db_url) ) {
$db_url = array( 'default' => $db_url );
}
$protocol = 'mysqli'; // or use variable_get() to get the variable you set on your settings page
$user = 'user';
$pass = 'password';
$host = 'localhost';
$db = 'alternate_db';
$db_url['alternate'] = "$protocol://$user:$pass@$host/$db";
$previous = db_set_active('alternate'); // returns the previous db name
// do stuff...
db_set_active($previous); // return to whatever database was active before
Drupal will check $db_url every time db_set_active is called, so you can edit it whenever you need.
Thanks! How are you using this…or should I just read the books?
Doesn’t that raise some security issues though, in terms of having db settings available via Drupal in the administrative settings?
@Jason: I’m working on a site for a client that uses a WordPress blog but Drupal for everything else. This code is going into a module that let’s Drupal pull post info out of WordPress’s database rather than relying on the Aggregator module to harvest the RSS feed.
@K: So long as Drupal’s database is secure and only appropriate users have access to the settings page, this shouldn’t add any additional security concerns. Granted, for my purposes, I could just put the connection info into settings.php, but I like keeping the code for my module entirely separate.
Hi,
Nice trick but for me it is not working. I am using dupal 6.x and try to use your code in my module. let me explain what am i exactly doing so you might help me. in my module i am creating one database and after that fetching values from .sql file and trying to insert it into newly created database. Now with my module i have made one database but to insert new query in that database i have to switch db and run query. When i try to use your trick it simple give me error that Access denied for module and logout from website. can you help me ? you can directly email me also if you have some solution.
Is this a perm change, a session change (single user), or for the single instance when a function is called.
@Griffin Hernandez: Drupal will switch back to the original database when you call
db_set_active($previous);. If you never call it, then the change will last until the script is finished running (and not affect any concurrent requests).