pdo multiple database connection in query

Question

I have 2 databases and i need to make a join from both databases in a query. But how is this possible when you prepare the statement?

I have 2 database connection files. But how for example could i select one table from on database (pdo) and then join from other table in the other database (pdotwo)?

private $pdo;
    private $pdotwo;

    public function __construct(DB $pdo, DBTwo $pdotwo)
    {
        $this->pdo = $pdo->pdo;
        $this->pdotwo = $pdotwo->pdotwo;
    }

Why do your two database connections need to have different Class names and internal variables? Surely each should be typed as your DB class and stored as themselves in this object? — M1ke
i thought as they have different database name that they needed there own class. Could you show me an example? — Daniel Le
They need their own object but not their own class, e.g. $db1 = new DB(); $db2 = new DB(); now both are separate objects but they are of the same class. — M1ke
then when you make a join query would you just call the pdo class and that would access both databases? — Daniel Le

M1ke M1ke · Accepted Answer · 2015-02-03T15:44:54

This is very similar to an older question on the same topic so see if that answers your needs.

It doesn't deal with the Class structure but deals with the practicalities of the connections.

The main point is that if your databases are on the same host you don't need to prepare two connections, you can simply prepare one connection and then specify the name of the 2nd database in your query:

$db = new PDO('mysql:host=localhost;dbname=db1;charset=utf8', 'username', 'password');

$result = $db->query("
    SELECT *
    FROM   table_on_db1 a, `db2`.`table_on_db2` b
    WHERE  a.id = b.fk_id
");

pdo multiple database connection in query

1 Answers