php – How to make a JOIN between two tables from two different databases?

Question:

I have the personal data (login, password, name, etc.) of users on a different server than the one I will use in a new project. I would like to know how I can cross information between two tables, from databases on different servers, with different login credentials too. I only intend to use PDO / MySQLi resources to make these connections, since I use shared hosts .

Example of what I wanted, in concept:

SELECT * FROM `serverLocal`.`banco`.`tabela` 
  INNER JOIN `serverRemoto`.`banco`.`tabela` 
  ON `serverLocal`.`banco`.`tabela`.`uniqid` = `serverRemoto`.`banco`.`tabela`.`uniqid`
WHERE `serverRemoto`.`banco`.`tabela`.`email` = 'foo@baz.com';

In this example, records are related through uniqid 's that are the same for lines with related content.

I thought about creating a REST API, but I don't know how to use this information in a JOIN , for example. How to make this work? What other solutions would be relevant to solving this problem, and why? Does PDO / MySQLi already bring some native feature so that it can "join" two connections to banks on different servers?

Answer:

Try using Federated tables , you create a replica of the table 'pointing' to the remote database.

Example:

CREATE TABLE federated_tabela (
    codigo INT(20) NOT NULL AUTO_INCREMENT,
    nome   VARCHAR(32) NOT NULL
)
ENGINE=FEDERATED
DEFAULT CHARSET=latin1
CONNECTION='mysql://usuario:senha@127.0.0.1:3306/banco/tabela';

Then just do the normal join .

More information here .