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` = 'email@example.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?
Try using Federated tables , you create a replica of the table 'pointing' to the remote database.
CREATE TABLE federated_tabela ( codigo INT(20) NOT NULL AUTO_INCREMENT, nome VARCHAR(32) NOT NULL ) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://usuario:firstname.lastname@example.org:3306/banco/tabela';
Then just do the normal
More information here .