CODEWITHSUNDEEP
phpmysql
sidd
sidd

Reputation: 669

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

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..

Upvotes: 1

Views: 1240

Answers (3)

Gouda Elalfy
Gouda Elalfy

Reputation: 7023

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

Upvotes: -1

Toby Allen
Toby Allen

Reputation: 11213

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.

Upvotes: 2

Shadow
Shadow

Reputation: 34252

You have 3 options:

  1. 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.

  1. 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.

  2. 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.

Upvotes: 0

Related Questions