insert into a MySQL database on a different server

Question

Using PHP and MySQL in a script run on mysite1.com I'm trying to copy all rows and columns from a table on mysite2.com into an identical table (that has already been created) on mysite1.com. First I connect to both the databases (I've already set up remote MySQL connections on mysite2.com).

$con1 = mysql_connect("mysite1.com", "username1", "password1");
if (!$con1) {die('Could not connect: ' . mysql_error());}
mysql_select_db("database1", $con1);

$con2 = mysql_connect("mysite2.com", "username2", "password2");
if (!$con2) {die('Could not connect: ' . mysql_error());}
mysql_select_db("database2", $con2);

I can't figure out how to format the "INSERT INTO" sql string so it will get the data from mysite2.com and put it in mysite1.com. Does anyone know how to do this?

INSERT INTO my_table1 SELECT * FROM my_table2

Answer 1

Can't be done the way you're thinking. You could set up a federated table and copy from that. Probably simpler just to use mysqldump and then load the file it creates though.

Third option is to read the data through php and generate INSERT statements, but that'l be the slowest of the three options.

Answer 2

$con1 = mysql_connect("mysite1.com", "username1", "password1");
if (!$con1) {die('Could not connect: ' . mysql_error());}
mysql_select_db("database1", $con1);

$result = mysql_query('SELECT * FROM `some_table`', $con1);
$query = array();
while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
    $query[] = '('.implode(',', $row).')';
}

$con2 = mysql_connect("mysite2.com", "username2", "password2");
if (!$con2) {die('Could not connect: ' . mysql_error());}
mysql_select_db("database2", $con2);

mysql_query('INSERT INTO `some_table` VALUES '.implode(',', $query).';', $con2);