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 array:
$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:
db_set_active('your_other_db');
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:
db_set_active();
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 $database_info array.
And now, if you want to perform operations on the new database, simply do like in option 1:
db_set_active('your_unique_key');
After you are done with it, close the connection and reactivate the default Drupal database:
db_set_active();
And you are done. Hope this helps anyone.
Daniel Sipos
Danny founded WEBOMELETTE in 2012 as a passion project, mostly writing about Drupal problems he faced day to day, as well as about new technologies and things that he thought other developers would find useful. Now he now manages a team of developers and designers, delivering quality products that make businesses successful.
Comments
You don't necessarily need db_set_active
You don't necessarily need to use the db_set_active() call before the query. You can select the database as part of the query:
$query = Database::getConnection('default', 'your_other_db')
->select('tablename', 'alias')...
Works, and means you don't need to worry about resetting the active database afterwards. I found this in the docs to the Migrate module (https://drupal.org/node/1152150) a while ago, and have been using it since.
Alternatively...
db_set_active() isn't optimal, especially if you're intermixing calls to the external database with calls to the default Drupal database (as when you're importing data from that external database to insert as Drupal users/nodes/etc.). It's important to understand that your standard db_select (for example) is really just a shortcut for
<?php
Database::getConnection('default', 'default')->select($table_name, $table_alias)
?>
So, if you substitute the key for your external database as the second parameter to getConnection, you can query it directly without the pair of db_set_active calls around it:
<?php
$query = Database::getConnection('default', 'your_unique_key')
->select('external_table', 'et')
->fields('et', array('id', 'content'));
?>
Remote content with a user interface
Check out this new project that provides this ability through the user interface: Remote content user interface
Remote Content UI
It is great to hear a new development of remote content UI for users. As I am building a CMS for scholars, it can extend the arm to retrieve the contents from different databases.
Can it have ajax function?
In reply to Remote Content UI by Joseph Lau (not verified)
Since you are connected to a
Since you are connected to a db, you should be able to use the Ajax framework. I have not tried ajax with external db, but don't see why it would be a problem.
In reply to Since you are connected to a by admin
Noted and thanks.
Noted and thanks.
fetch data from two database
Can i fetch data from two database in single query at same time.
Option 2 - Example Module
Great Article!! exactly what I am looking for. I'm new to Drupal and getting a little frustrated. Do you have an example full module that pulls data from a database and displays it? where the connection is in the module? charlesstratton4@gmail.com
Thanks!
Redshift driver
i want to connect to reshift databse how can i connect to this
pgsql drupal 8 conect
Hi,how t connect pgsql to drupal as db external.
thanks.
Error after connecting with external database
I have changed code in settings.php file for connection of external db. Now as I send a query to db and print the result it gives error.
My query code is ->
db_set_active('ex_db');
$result = db_query('SELECT * FROM college');
$rowdata = array();
$header = array(t('ID'), t('Email id'), ('Department'));
foreach($result as $row) {
$rowdata[$row->sid][$row->name] = $row->data;
$header[$row->name] = $row->name;
}
$form['taks1_result'] = array(
'#theme' => 'table',
'#rows' => $rowdata,
'#header' =>$header
);
I got this error at browser
Additional uncaught exception thrown while handling exception.
Original
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'DATABASE1.filter_format' doesn't exist: SELECT ff.* FROM {filter_format} ff WHERE (status = :db_condition_placeholder_0) ORDER BY weight ASC; Array ( [:db_condition_placeholder_0] => 1 ) in filter_formats() (line 434 of /var/www/html/drupalprac/modules/filter/filter.module).
Additional
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'DATABASE1.semaphore' doesn't exist: SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => theme_registry:runtime:bartik:cache ) in lock_may_be_available() (line 167 of /var/www/html/drupalprac/includes/lock.inc).
Uncaught exception thrown in shutdown function.
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'DATABASE1.semaphore' doesn't exist: DELETE FROM {semaphore} WHERE (value = :db_condition_placeholder_0) ; Array ( [:db_condition_placeholder_0] => 11361054925ac35a21c3eef2.91805626 ) in lock_release_all() (line 269 of /var/www/html/drupalprac/includes/lock.inc).
Uncaught exception thrown in session handler.
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'DATABASE1.sessions' doesn't exist: SELECT 1 AS expression FROM {sessions} sessions WHERE ( (sid = :db_condition_placeholder_0) AND (ssid = :db_condition_placeholder_1) ); Array ( [:db_condition_placeholder_0] => gSAxKMpfVXzf2EVP4EWBtuF2klcg0RZie30ztTTUjKc [:db_condition_placeholder_1] => ) in _drupal_session_write() (line 209 of /var/www/html/drupalprac/includes/session.inc).
Error after connecting with external database
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'DATABASE1.filter_format' doesn't exist: SELECT ff.* FROM {filter_format} ff WHERE (status = :db_condition_placeholder_0) ORDER BY weight ASC; Array ( [:db_condition_placeholder_0] => 1 ) in filter_formats() (line 434 of /var/www/html/drupalprac/modules/filter/filter.module).
Additional
PDOException: SQLSTATE[42S02]: Base table or view not found: 1146 Table 'DATABASE1.semaphore' doesn't exist: SELECT expire, value FROM {semaphore} WHERE name = :name; Array ( [:name] => theme_registry:runtime:bartik:cache ) in lock_may_be_available() (line 167 of /var/www/html/drupalprac/includes/lock.inc).
Uncaught exception thrown in shutdown function.
option two for a lot of records
Hi I was wondering what I need to do to use the option two to read an external db with a lot of records because it sometimes throws a timeout, there's a way to run it on a thread or in background?
How to create new table in other database?
How to install new database table to other database in hook_schema()?
Add new comment