Mysqli join tables from 2 different databases

Question

How Can I use this 2 connections to run a query which joins 2 tables? yesno.table1 and sushi.table1 ? lets say we join by id they both have the same id. how can I do that?

// Connect to Yesno Database
$this->yesno_db = new mysqli("red", "yesno", "***", "yesnotmp");

if ($this->yesno_db->connect_errno) {
    throw new Exception('Connection failed: '.$this->yesno_db->connect_error);
}   

// Connect to Sushi Database
$this->sushi_db = new mysqli("red", "sushi", "***", "sushi");

if ($this->sushi_db->connect_errno) {
    throw new Exception('Connection failed: '.$this->sushi_db->connect_error);
}

Answer 1

This question has nothing to do with mysqli (or any other API).

To do a join with a table from different database, a user which connects to mysql, have to have SELECT rights for both databases.

Having this done, just select one of databases in your connection statement and address another using usual dot syntax:

SELECT * FROM t1 JOIN db2.t2

To answer your question literally,

Can I use this 2 connections to run a query which joins 2 tables?

You can't

Answer 2

This is possible but not in the way you're attempting it.

First, you will need to have a mysql user with permissions to access both databases.

Then you simply reference the secondary database as part of the query.

E.g.

// Connect to Database -- dbuser has permissions for database1 and database2
$this->db = new mysqli("localhost", "dbuser", "***", "database1");

$query = "SELECT t1.*, t2.* 
              FROM table_one AS t1
              INNER JOIN database2.table_two as t2 ON ( t1.id = t2.remote_id ) ";

This isn't possible if the databases are on two different servers.

Answer 3

It is possible, but not pretty.

MySQL supports the federated storage engine, effectively allowing you to set up what looks like a table on one of your databases that is actually just a connection to the real table on the other database.

This previous reply might help:-

MySQL -- Joins Between Databases On Different Servers Using Python?