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.

Use mysqli instead of mysql. Not too sure how it handles mutliple servers but you could have a look at Federated tables. — Coloco
– Coloco, Commented May 11, 2015 at 9:19
$events is a mysql resource and I don't think you can use it as a string, however you can write ` * FROM ($sql)` since $sql is a string. Please notice that you're using mysql_ which is deprecated and consider using mysqli_ as @Coloco suggested or PDO. — Ofir Baruch
– Ofir Baruch, Commented May 11, 2015 at 9:23

MueR · Accepted Answer · 2015-05-11 09:25:00Z

3

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.

answered May 11, 2015 at 9:25

MueR

9777 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ediblecode Over a year ago

Thanks, I feared I would have to do the row by row insert but was hoping for a nicer solution.

MueR Over a year ago

The nicer solution is to modify your sites to inform eachother of events and having them instantly add it to the database. That way, you have no more data missing from sites until your cron syncs the database and you won't have to do a row by row comparison.

ediblecode Over a year ago

That isn't possible with the server setup we have unfortunately

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related