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, Feb 03 '15 at 15:42
i thought as they have different database name that they needed there own class. Could you show me an example? — Daniel Le, Feb 03 '15 at 15:44
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, Feb 03 '15 at 15:45
then when you make a join query would you just call the pdo class and that would access both databases? — Daniel Le, Feb 03 '15 at 15:47

score 6 · Answer 1 · edited May 23 '17 at 12:28

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
");

yeah iv seen that. what i cant figure out is how would you prepare 2 databases? — Daniel Le, Feb 03 '15 at 15:46

pdo multiple database connection in query

1 Answers1