CODEWITHSUNDEEP
phpmysqlsqldatabasepdo
Alex Ibrahim Ojea
Alex Ibrahim Ojea

Reputation: 107

PHP - Left join from two tables in different databases with different credentials using PDO

Please note: While my original issue was not possible to be solved in the way I expected, @Bamar solution marked in this post is an alternative that reaches the same goal and works perfectly. What I proposed in this post to be done doesn't seem to be viable if the databases are located in different hosts.

I've been searching for a while and I seem to be unable to solve my issue.

THE DATA I HAVE

My service provider is 1&1. In the current contract I have with them I could create up to 100 databases with a maximun size of 2GB each.
Each database that is created, is assingned a random hostname, port and username (the only item which I can choose is the password).
I've got two different databases, lets call them DB_1 and DB_2.
In the DB_1 I've got a table called T_USERS which fields of interest for this particular problem are:

In the DB_2 I've got a table called T_SCORES which fields of interest for this particular problem are:

It is important to take into account that to access both databases each of them needs different credentials!

WHAT I WANT TO ACHIEVE

What I want to achieve seems simple at a first glance but I was unable to find any documentation or solution online on how to do this using PHP and PDO.
I just want to perform a join with DB_2.ID_USER and DB_1.ID
My final result should look something like this:

DB_1.userName DB_2.score
Alex 237
Peter 120
Mark 400
... ...

WHERE I'M CURRENTLY STUCK

This is what I've currently tried.
First of all I perform the connection to my databases as follows (I normally use a try/catch when connecting to a DB but I will omit it here):

//Connection to the DB1
$db1_hostName = "hostnameofDB1";//The host name of the database 1
$db1_name = "db1";//The name of the database 1
$db1_userName = "user1";//The username in the database 1
$db1_password = "pw1";//The password for the database 1
$pdo_db1Handle = new PDO("mysql:host=$db1_hostName; dbname=$db1_name;", $db1_userName, $db1_password);
$pdo_db1Handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

//Connection to the DB2
$db2_hostName = "hostnameofDB2";//The host name of the database 2
$db2_name = "db2";//The name of the database 2
$db2_userName = "user2";//The username in the database 2
$db2_password = "pw2";//The password for the database 2
$pdo_db2Handle = new PDO("mysql:host=$db2_hostName; dbname=$db2_name;", $db2_userName, $db2_password);
$pdo_db2Handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

So basically up to this point what I've done is very simple, create a pdo_db1Handle and pdo_db2Handle. Now to the tricky part...
If I now want to perform a join my SQL syntax should be something like this:

SELECT DB_1.T_USERS.userName, DB_2.T_SCORES.score
FROM DB_2.T_SCORES 
    LEFT JOIN DB_1.T_USERS 
    ON (DB_2.T_SCORES.ID_User=DB_1.T_USERS.ID)
ORDER BY DB_2.T_SCORES.score ASC 'The ordering is optional, I'm interested in the join part first

But as far as I'm aware and with all the information I was able to find, you execute the SQL statement against one of the two handles I previously defined in the following way:

$stmt=$pdo_db1Handle->prepare($mySQLStatement);
$stmt->execute();

When I try to do this, an error shows up telling me missing credentials for the DB_2. It happens the opposite (missing credentials of DB_1) if I try to execute it against pdo_db2Handle.

How should I proceed? any solution using PDO for this?


Thanks in advance :)


Upvotes: 0

Views: 1631

Answers (2)

Barmar
Barmar

Reputation: 782166

You can't join if you have to use separate PDO connections, so use nested loops and join the data in PHP.

$stmt_user = $pdo_db1Handle->query("SELECT id, username FROM t_users");
$stmt_score = $pdo_db2Handle->prepare("SELECT score FROM t_scores WHERE id_user = :userid");

$results = [];
while ($row_user = $stmt_user->fetch(PDO::FETCH_ASSOC)) {
    $scores = [];
    $stmt_score->execute(':userid' => $row_user['id']);
    while ($row_score = $stmt_score->fetch(PDO::FETCH_ASSOC)) {
        $scores[] = $row_score['score'];
    }
    $results[$row_user['username']] = $scores;
}

This will create an associative array whose keys are usernames and values are an array of their scores.

Upvotes: 1

Alexander Higgins
Alexander Higgins

Reputation: 6905

Depending on your use case, a work around may be to copy the table from one database to another temporarily and the perform your sql once you have both tables in a single database:

$pdo1 = new PDO('mysql:host=$db1_hostName; dbname=$db1_name', $db1_userName, $db1_password);
$pdo2 = new PDO('mysql:host=$db2_hostName; dbname=$db1_name', $db2_userName, $db2_password);

$insert_stmt = $pdo2->prepare("INSERT INTO T_SCORES (col1, col2, col3, ...) VALUES (:col1, :col2, :col3, ...) ON DUPLICATE KEY IGNORE");
$select_results = $pdo1->query("SELECT * FROM T_SCORES");
while ($row = $select_results->fetch(PDO::FETCH_ASSOC)) {
    $insert_stmt->execute($row);
}

-- now work with the tables as you usually would.

You can create the table in the target database before hand and truncate the data before and/or after performing the insert.

Upvotes: 0

Related Questions