Originally Posted by evilghost
1) I don't have a 100% positive answer but I don't think this is possible since each SQL statement is executed in the context of the opened db connection pointer. Sure you can have multiple pointers open to different DB servers but I don't believe there is a way to join these two pointers in an INNER JOIN/LEFT JOIN style syntax. I believe you'd have to do recordset enumeration in PHP and possibly some additional nested queries.
2) How many records? I guess you could use two arrays and compare them, inserting objects not found in array1 to db2 and array2 to db1.
1. I've done a few searches on the web, and there were a few suggestions that using "database.table.field" in queries would work once you connected, but you can't use mysql_select_db and it seems to have only been tested on different databases on the same server. I need something similar but the databases located on different servers. I'm not sure nested queries would work in this case since mysql_select_db will lose the connection to the other database. I'm going to test out a few things in the mean time.
2. The tables in question are around 100 rows with about 4-10 fields. I'm thinking about making a change log table to record which tables were changed, then using that information to select the changed table/s and copying all those rows to the other table/s after those tables are emptied. I'm pondering this because the data in the tables can be deleted, new data be added, or the data could also be edited. And the extra logic needed to take these into account is something I don't quite have the time to develop.
The tables in question aren't very large now, but I do expect them to grow larger in the future. By then though, I'm hoping we'll have moved on to servers that do allow Mysql replication which will make things much simpler.