5

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()?

  • 2
    You can set multiple database access information in settings.php and db_set_active() to switch the database connection (Don't forget to set it back to default!!!) but single query through multiple databases is not possible AFAIK. – AyeshK Oct 1 '12 at 1:51
9

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.

  • 1
    Can you really do that? That's awesome! – Clive Oct 3 '12 at 14:35
  • 1
    It's documented in SelectQuery::addJoin(), the method for which SelectQuery::join() is a wrapper. It would be better if the documentation for SelectQuery::join() would remind that too. – kiamlaluno Oct 3 '12 at 15:19
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.