How to connect to multiple Databases in Drupal 7?

Question

I'm building an encyclopedia website. There are about 20 subjects that must be stored in the Database (media,library,article,translations,...) and we want to create 20 databases and allocate one DB for every subject. is it possible to do such thing? The main site has 3 sub domains and every sub domain covers specific rows of the subjects. for example the library is a subject and math.mainsite.com must have access only to the math type of the library subject.

the sub-domains must not have not any data, they all should get their data from the 20 above mentioned DBs, so every site must connect to 20 DB.

We selected this strategy because the subjects are subdomain as well, I mean library.mainsite.com and article.mainsite.com and ... are valid urls.

thanks

Answer 1

Two steps:

1. Add database credentials to the $databases array in settings.php file. You can have as many additional databases as you want. Example code:

   $databases = array (
     'default' => 
     array (
       'default' => 
       array (
         'database' => 'YOUR_DEFAULT_DATABASE',
         'username' => 'username',
         'password' => 'password',
         'host' => 'localhost',
         'port' => '',
         'driver' => 'mysql',
         'prefix' => '',
       ),
     ),
     'your_second_db_alias' => 
     array (
       'default' => 
       array (
         'database' => 'YOUR_SECOND_DB',
         'username' => 'username',
         'password' => 'password',
         'host' => 'localhost',
         'port' => '',
         'driver' => 'mysql',
         'prefix' => '',
       ),
     ),
   );
   

2. Use the following code to change the database and run queries. Note that you must switch back to the original database afterwards.

   // Change active database.
   db_set_active('your_second_db_alias');
   // Run queries.
   $query = db_query("SELECT * FROM yourtable");
   // IMPORTANT: return current active database to the default one. If you
   // don't do this, everything after that point will be executed against the
   // above database, and cause errors most probably. If you are connecting
   // multiple Drupal installations, you can cause a pretty big mess if data is
   // partially saved in the other database.
   // Always double check that you have this line!
   db_set_active();
   

Hope this helps!