Joining two tables from different databases

Question

I am aware that there are multiple posts about this, but I was not able to make it work for my code.

As the title suggests I want to join two tables from two different DBs together.

Here is my code:

 $dbh1 = mysql_connect("$host", "$username", "$password")or die("cannot connect"); 
    $dbh2 = mysql_connect("$host2", "$username2", "$password2", true)or die("cannot connect"); 
    mysql_select_db("$db_name", $dbh1)or die("cannot select DB");
    mysql_select_db("$db_name2", $dbh2)or die("cannot select DB");

//first table  
//$sql = mysql_query("SELECT InterestedEntityId, Score FROM users.`user_interests` WHERE UserId= //$userID ORDER BY Score DESC", $dbh1);

//second table
//$sql = mysql_query("SELECT entities.`Name` FROM tags.`entities` WHERE Id = InterestedEntityId", $dbh2);

I want to get the 3 fields mentioned in select statements in one go (I.E. InterestedEntityId, Score, entities.Name)

Any idea on how to join these two tables in one sql query. I tried using inner joins and adding the tablename (as this thread suggested), but the query did not return anything.

Any ideas please?

You need to have authorization to access the two databases with your user and add the database name before the table name. — AFrenchDev, Apr 22 '13 at 14:12
Yes of course. That part of my code is present in my original script :) — r3x, Apr 22 '13 at 14:35

score 0 · Answer 1 · answered Apr 22 '13 at 14:15

0

Something like this should work.

SELECT t1.InterestedEntityId, t1.Score, t2.Name
  FROM DB1.users.`user_interests` t1
  JOIN DB2.tags.`entities` t2 ON t2.UserId = t1.Id

Note: Use PDO as mysql_* is deprecated and not secure enough.

answered Apr 22 '13 at 14:15

Kalpesh

5,635
2
23
39

score 0 · Answer 2 · edited May 23 '17 at 10:24

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

While it is theoretically possible to join tables from two different databases on the same server, what you are trying to do cannot possibly work because you appear to be accessing two different servers.

In order to get the result set you want you will need to combine them manually.

For example (using PDO):

$dsn1 = "mysql:host=$host;dbname=$db_name";
$dsn2 = "mysql:host=$host2;dbname=$db_name2";

try {
    // Create the connections
    $db1 = new PDO($dsn1, $username, $password);
    $db1->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db1->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db1->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    $db2 = new PDO($dsn2, $username2, $password2);
    $db2->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $db2->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    $db2->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

    // Get the initial recordset
    $sql1 = "
        SELECT InterestedEntityId, Score
        FROM `user_interests`
        WHERE UserId = :userId
        ORDER BY Score DESC
    ";
    $stmt1 = $db1->prepare($sql1);
    $stmt1->bindParam('userId', $userID, PDO::PARAM_INT);
    $stmt1->execute();

    // Prepare the statement for the second database
    $sql2 = "
        SELECT Name
        FROM entities
        WHERE Id = :entityId
    ";
    $entityId = 0;
    $stmt2 = $db2->prepare($sql2);
    $stmt2->bindParam('id', $entityId, PDO::PARAM_INT);

    // Loop the first result set
    $result = array();
    foreach ($stmt1 as $row1) {
        // Fetch the related data from the second DB
        $entityId = $row1['InterestedEntityId'];
        $stmt2->execute();
        $row2 = $stmt2->fetch();

        // Construct the final result row and store it
        $result[] = array(
            'InterestedEntityId' => $row1['InterestedEntityId'],
            'Score' => $row1['Score'],
            'Name' => $row2['Name']
        );
    }
} catch(PDOException $e) {
    die($e->getMessage());
}

// The result set you want should now be available
var_dump($result);

I thought of that, but doing so requires that I fetch the name, then append with every row of that table. Which is wasteful, not to mention takes too much time. — r3x, Apr 22 '13 at 14:32
@Shane If you are dealing with two different database servers, there is literally no other way. If the value of both `$host` and `$host2` is always going to be the same then you can look at a join - but you won't need the two connections in that case. If you are dealing with two different servers as you code suggests, check my edit. — DaveRandom, Apr 22 '13 at 14:35
@Shane There are quicker ways to do it than that (eg, your script reads one table, saves the contents in a CSV and then uses LOAD TABLE... to put that CSV into the other DB), but still not fast. — Kickstart, Apr 22 '13 at 14:38
@Kickstart Well indeed, realistically the *best* way to do this would be to ensure that all the required data is available in a single database, so it's just a simple `JOIN`. I'm guessing that because the OP is asking the question at all that's not possible - but maybe I'm assuming something I shouldn't be... — DaveRandom, Apr 22 '13 at 14:40
@DaveRandom - I agree. When data is split over different database servers you have a big problem. Pretty much undermines the whole idea of a relational database. But if the data is on 2 separate servers it is possible to copy it over to do a join then LOAD TABLE... is one of the less inefficient ways of doing it. Whether I would bother to go to the effort of doing this would largely depend on how often this was required, and the quantities of data involved on each server. Also possibly an idea to use a federated database - http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html — Kickstart, Apr 22 '13 at 15:17
@Kickstart with the prepared statement model above I'm not sure which would be more efficient - it really depends on the size of the data I guess. No matter what you do it's going to be :-X though. — DaveRandom, Apr 22 '13 at 15:26
@DaveRandom , indeed the prepared statement is better but still means potentially doing thousands of queries within the loop . But it is a case of any which way but lose. — Kickstart, Apr 22 '13 at 15:32

score 0 · Answer 3 · answered Apr 22 '13 at 14:16

0

Pretty much the model is:

SELECT dbName1.TableName1.ColumnName1, dbName2.TableName2.ColumnName2 FROM dbName1.TableName1 JOIN dbName2.TableName2 ON dbName1.TableName1.ColumnName1 = dbName2.TableName2.ColumnName2

answered Apr 22 '13 at 14:16

Royal Bg

6,988
1
18
24

Joining two tables from different databases

3 Answers3