php sql join together multiple tables from different databases

Question

I have 3 databases, and now I need to join several tables from each one of them into a singel query. How do I do this?

This is my connection:

try {
    $con_options = array(
        PDO::ATTR_EMULATE_PREPARES => false,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8',
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,  // _SILENT (pub) || _WARNING || _EXCEPTION  (dev)
    );

    $con_1 = new PDO('mysql:host=localhost; dbname=database_1', 'user_1', 'pass_1', $con_options);
    $con_2 = new PDO('mysql:host=localhost; dbname=database_2', 'user_2', 'pass_2', $con_options);
    $con_3 = new PDO('mysql:host=localhost; dbname=database_3', 'user_3', 'pass_3', $con_options);

} catch (PDOException $err) { 
    //  catch, record/log and do stuff with errors
}

I have 3 different users with a unique password for each database. One database stores application-data for facebook-apps and other iframe applications. Another one holds all webshop data like products, orders, customers etc. while the third one holds site structure and content.

Now; I would like to JOIN the three of them together in a single query somehow.

While I was writing this question; One idea I got was to have another "super"-user with access to all three databases and just do a regoular multi table query? Would that be an acceptable solution?

If so, do I have to specify which database aswell in the query?

German Rumm · Accepted Answer · 2012-12-04 12:58:43Z

5

You will need a user that has access to all three databases.

You will JOIN them together by specifying full table name, something like this:

SELECT * FROM database_1.logs AS d1 LEFT JOIN database_2.users AS d2 
  ON d1.username = d2.username ORDER BY d1.timestamp DESC LIMIT 10

answered Dec 4, 2012 at 12:58

German Rumm

5,8171 gold badge27 silver badges30 bronze badges

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

2 Comments

vectorialpx Over a year ago

As a completion, you cannot run an SQL for three different users, in PHP or other language. You must have only one connection, to make it work.

ThomasK Over a year ago

yep, That's what I thought when I was writing the question. So I created a user with access to all three databases, then tested a few times with error about not finding the table or view. Looked back here and found that I should use full table name with database name at the beginning. And i worked like a charm... thanks guys...

Collectives™ on Stack Overflow

php sql join together multiple tables from different databases

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related