PHP / MySQL - Compare tables from 2 different databases

Question

I've got 2 frameworks (Laravel - web, Codeigniter - API) and 2 different databases. I've built a function (on the API) which detect changes on one database (from 2 tables) and apply the changes in the other database.

Note: there is no way to run both web and API on the same databases - so thats why I'm doing this thing.

Anyway, this is important that every little change will recognized. If the case is new record or delete record - its simple and no problem at all. But, if the records exists in both databases - I need to compare their values to detect changes and this section become challenging.

I know how to do this in the slowest and heavy way (pick each record and compare).

My question is - how do you suggest to make it work in smart and fast way?

Thanks a lot.

Ben Hillier · Accepted Answer · 2016-09-21 07:26:39Z

1

As long as the mysql user has select rights on both databases, you can qualify the database in the query like so:

SELECT * FROM `db1`.`table1`;
SELECT * FROM `db2`.`table1`;

It doesn't matter which database has been selected when you connected to PHP. The correct database will be used in the query.

The ticks are optional when the database/table name is only alphanumeric and not an SQL keyword.

answered Sep 21, 2016 at 7:26

Ben Hillier

2,1041 gold badge11 silver badges15 bronze badges

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

1 Comment

RonZ Over a year ago

For security and performance reasons, the SQL user doesn't rights on both databases. I'm working with heavy websites which work together with API.

L00_Cyph3r · Accepted Answer · 2016-09-21 07:32:37Z

Depending on the response-time of the 'slave'-database there are a two options which don't increase the overhead too much:

If you can combine both databases within the same database by prefixing one or both of the tables, you can use FOREIGN KEYS to let the database do the tough work for you.
Use the TIMESTAMP-field which you can set to update itself by the DB whenever the row gets updated.

Option 1 would be my best guess, but that might mean a physical change to the running system, and if FOREIGN KEYS are new for you, you might wanna test since they can be a real PITA (IMHO).

Option 2 is easier to implement, but you still have to manually detect changes to deleted/rows.

Collectives™ on Stack Overflow

PHP / MySQL - Compare tables from 2 different databases

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related