How to insert return value of one SQL command into another table using PHP

Question

I have two servers hosting two different sites with two separate databases. However, some of the data needs to be shared - so there's a CRON that runs to copy that data from the first site to the second site. These two sites used to run on the same server, but now we have separated them so I'm attempting to amend the PHP to be able to handle this.

Here is the code where I'm encountering the problem:

$sql = "SELECT * FROM site1.events";
$events = mysql_query($sql, $site1db);  

$sql = "INSERT INTO site2.events SELECT * FROM $events";
$doIt = mysql_query($sql, $site2db);

Now I know that the SELECT * FROM site1.events is successful, but it cannot insert into the second site and I'm not seeing any sort of error. I've never written PHP before (this is a legacy task) so if it's something that's been covered before - do point me in the right direction.

Please note that site2.events is truncated before these commands so no compare is necessary.

Answer 1

You will have to do a row by row compare and copy. What you are attempting to do right now cannot work. You could use INSERT IGNORE INTO site2.events (SELECT * FROM site1.events) if the databases run on the same server and the database user can access both databases. The downside of this, the primary keys would have to be identical or you will miss data.

A better alternative would be to simply have site1 do a callback containing all info for that event to site2 at the moment a new event is added.