php connecting to two databases on diff servers and using insert select

Question

hi I need some help what I am trying to do is using insert select statement like this where databases are on diff servers.

insert into db1.copy2 (c1,c2) select c1,c2 from db2.copy2;

db1 is on amazon web service relation database and db2 is on local host(wamp) how can i make a PHP script that can accommodate two databases like that and that will allow me to execute a query like that. this script will execute on local host thanks..

Not possible in PHP I'm afraid. http://stackoverflow.com/questions/1999235/how-do-i-construct-a-cross-database-query-in-php — Sergey Vidusov, Feb 21 '16 at 08:46
This is not possible unless of course you get the data from both DBs to the php and aggregate the data in your php code — zion ben yacov, Feb 21 '16 at 08:48

score 2 · Accepted Answer · answered Feb 21 '16 at 09:00

2

There are many pieces of software for syncronising or copying data between to database servers, maybe you should use one of those. Otherwise you could just do it in 2 steps

in pseudo code

$sql = "select c1,c2 from db2.copy2";

$Results = pdofetchall($sql);

foreach ($Results as $row){
  $c1 = $row['c1'];
  $c2 = $row['c2'];
  $InsertSQL = "insert into db1.copy2 VALUES($c1,$c2)  ";
  executesql($InsertSQL);
}

Note this is pseudo code and defintitely cannot be copied and pasted, but give you the gist of what you need to do.

answered Feb 21 '16 at 09:00

Toby Allen

10,997
11
73
124

I was thinking about this when everyone said it cannot be done....This solution is like creating a connection fetching from the that connection and creating annother connection inserting the rows into the new table. I will give it a try thanks toby allen. but what is the synatax for doing this :-D – sidd Feb 21 '16 at 09:05

score 0 · Answer 2 · answered Feb 21 '16 at 08:58

You have 3 options:

If you can connect the 2 servers via a vpn network or ssh connection so that the 2 servers can directly see each other, then you can use federated table engine:

The FEDERATED storage engine lets you access data from a remote MySQL database without using replication or cluster technology. Querying a local FEDERATED table automatically pulls the data from the remote (federated) tables. No data is stored on the local tables.

This solution would enable the syntax you used in the question.

Again, you have to enable direct connection between the 2 servers and you set up replication between the 2 servers, essentialky making sure that the data available on the localhost is copied to the amazon server. The query would run on the amazon server and would query the copied data.
You combine data within php from the 2 data sources either by directly connecting to both instances or by setting up a web-based api on local server that can be used to transfer data from the local server to amazon.

score -1 · Answer 3 · answered Feb 21 '16 at 09:06

you can do this easily if your 2 database on the same server, just make a connection to one database and pass your query easily:

$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "insert into db1.copy2 (c1,c2) select c1,c2 from db2.copy2;";

this will work for you

i already said they are on different servers otherwise i wouldn't have asked :-p — sidd, Feb 21 '16 at 09:09

php connecting to two databases on diff servers and using insert select

3 Answers3