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

Question

I have two servers on which two different sites with two separate databases are located. However, some data must be shared, so there is a CRON that runs to copy this data from the first site to the second site. These two sites were used to work on the same server, but now we have separated them, so I'm trying to change PHP to be able to handle this.

Here is the code where I am facing 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 SELECT * FROM site1.events successful, but it cannot insert into the second site, and I do not see any error. I have never written PHP before (this is an inherited task), so if this is something that has been considered before, take the right direction for me.

Note that site2.events truncated before these commands, so comparison is not required.

Answer 1

You will have to perform line-up comparisons and copying. What you are trying to do right now cannot work. You can use INSERT IGNORE INTO site2.events (SELECT * FROM site1.events) if the databases are running on the same server and the database user can access both databases. The disadvantage of this is that the primary keys must be identical or you will skip data.

A better alternative would be to simply force site1 to make a callback containing all the information for this event to site2 when a new event is added.