[Drupal] How to join two tables in Drupal

You might have joined different tables in the same database. How would it be if you could join different tables from different databases. Sounds good!! right? I had to go through an uncountable number of tutorials and blogs to make this happen. All thanks to Drupal.

There are certain conditions you have to ensure before using this method. First to make sure is that you have both databases on the same server. And the second is a fit. The second is about user, ensure both have the same.

Requirement was to get all the users in both databases.

The first modification has to be made is in the settings.php, add the new database to the databases array.

$databases = array (
'default' =>
array (
'default' =>
array (
'database' => 'new',
'username' => 'root',
'password' => '',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
'external' =>
array (
'default' =>
array (
'database' => 'old',
'username' => 'root',
'password' => '',
'host' => 'localhost',
'port' => '',
'driver' => 'mysql',
'prefix' => '',
),
),
);


Make sure you have entered your database name, username and password correctly.

To join the two tables in different databases, use,
$old_url = db_query("SELECT a.*, b.* FROM {user} a JOIN zyxware_old.user b ON a.uid = b.uid")

Here we are getting all the users that are in both tables,
For the first database we don't have to mention the database name, just enter the table to be joined in the curly braces. But for the second one we have to follow this pattern database_name.table_name.

Databases is sure an ease in managing the content managing system. Database query has a lot of scope hidden that could let more heavier jobs lighter.