In this article I am going to show you how to query for information in a database other than the default one you set up when you installed Drupal. There are 2 main ways to do this properly and choosing one over the other depends of course on your needs.
One of the ways is mostly for when multiple modules interact with an external database. This is prefered and helpful as you don’t need to always add the db information in the code of every module. The other way is mostly for when you have a single module that needs a one-off interaction with an external database: it connects, performs operations, disconnects and that’s it.
Please disregard the .php opening and closing tags as they are only for the purpose of formatting and displaying the code snippets on this page.
Option number 1
Let’s first see how to configure the Drupal site to quickly allow connections to another database to all modules. So in a typical Drupal 7 installation, you’ll see that in the settings.php file (where the db information is stored), there is a big array like this:
$databases = array ( 'default' => array ( 'default' => array ( 'database' => 'db-name', 'username' => 'db-username', 'password' => 'db-password', 'host' => 'localhost', 'port' => '', 'driver' => 'mysql', 'prefix' => '', ), ), );
If it’s not exactly like this for you, don’t worry. The username, password and database name should all be there and that is how Drupal knows how to connect to its default database. To allow modules to quickly make connections to other databases, you’ll need to add additional information to the
$databases['your_other_db']['default'] = array ( 'database' => 'other-db-name', 'username' => 'other-db-username', 'password' => 'other-db-password', 'host' => 'localhost', 'driver' => 'mysql', );
As you can see, this code defines another database identified by the array key your_other_db. So when you need to query this other database in your modules, you have to switch the connection to it with this function:
When you are finished, you have to close it and revert back to the default database connection in order for Drupal to be able to access its data:
As you don’t pass a parameter to this function, it will set the default database active and everything goes back to normal.
Option number 2
This option is more for when you need to connect to another database only in one module. So let’s see how it works.
To perform operations on it, first you need to store the connection data in an array:
$database_info = array( 'host' => 'localhost', 'database' => 'db-name', 'username' => 'db-username', 'password' => 'db-password', 'driver' => 'mysql', );
In case you are using a PostgreSQL, just replace the driver with pgsql.
Next up, you need to add the database connection info using the following line:
Database::addConnectionInfo('your_unique_key', 'default', $database_info);
The function takes three parameters. First, you need to set a unique database key so that you can later activate it. The second parameter is the target database name. You can leave this as ‘default’ since you only need to connect to one database. The third parameter is the database connection information that you stored in the
And now, if you want to perform operations on the new database, simply do like in option 1:
After you are done with it, close the connection and reactivate the default Drupal database:
And you are done. Hope this helps anyone.