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
– M1ke, Commented Feb 3, 2015 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
– Daniel Le, Commented Feb 3, 2015 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
– M1ke, Commented Feb 3, 2015 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
– Daniel Le, Commented Feb 3, 2015 at 15:47

Community · Accepted Answer · 2017-05-23 12:28:31Z

6

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

edited May 23, 2017 at 12:28

CommunityBot

11 silver badge

answered Feb 3, 2015 at 15:44

M1ke

6,4364 gold badges35 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Daniel Le Over a year ago

yeah iv seen that. what i cant figure out is how would you prepare 2 databases?

Collectives™ on Stack Overflow

pdo multiple database connection in query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related