Using db_query() or db_select() to connect to multiple databases

Question

How can I execute a query that connects to different databases?

I have the following query, and I need to rewrite it for Drupal.

SELECT a.empid, b.empname FROM db1..empidmaster a, db2..emplist b
WHERE a.empid = b.empid

How can I rewrite the query using db_query(), or db_select()?

Answer 1

If the settings.php file defines the information to connect to the other database as $databases['extra']['default'], then you can use the following code, which is possible because SelectQuery::join() accepts also another SelectQuery object as $table argument.

$subquery = Database::getConnection('default', 'extra')->select('emplist', 'b');
$query = db_select('empidmaster', 'a');
$query->join($subquery, 'b', 'b.empid = a.empid');
$query->addField('a', 'empid');
$query->addField('b', 'empname');

A better code would be similar to the following one. In this case the alias used for the table used for the subquery is the one returned from SelectQuery::join().

$subquery = Database::getConnection('default', 'extra')->select('emplist', 'b');
$query = db_select('empidmaster', 'a');
$alias = $query->join($subquery, 'b', '%alias.empid = a.empid');
$query->addField('a', 'empid');
$query->addField($alias, 'empname');

SelectQuery::join(), and SelectQuery::addField() don't return the $query object; that is the reason you cannot the following code.

$query = db_select('empidmaster', 'a')
  ->join($subquery, 'b', 'b.empid = a.empid')
  ->addField('a', 'empid')
  ->addField('b', 'empname');

PHP would return an error about a method not being called on an object. In fact, SelectQuery::join() and SelectQuery::addField() return a string: the alias for the table, or the field.

Answer 2

If both database are in the same server and same user, you can do this:

db_query("SELECT a.empid, b.empname FROM {empidmaster} a JOIN database2.emplist b ON b.empid = a.empid");

Table a represents drupal database, and b, the external database.