How access data between databases in mysql?

Question

Hi, I'm working in a project that is divided into multiple modules. Each module have it's own independent database in mysql, but now, the modules need to obtain data between them. For example we're going to develop a new "admin" module, and every other modules need to access the data in the "admin" database. I know that I can make a query like

select * from admin.table

to obtain data from other database, but each module (and the new "admin" module) are created in CakePHP. I think one possible solution is use something like Synonyms (like the ones in Oracle or SQL Server), but MySQL don't support it. Someone have a better idea? Thanks

Answer 1

How about using views:

create view admin_table as select * from admin.table

Then, you just need to set $tableName to admin_table.

Answer 2

I may be wrong, but I think querying is based on

select * from database.owner.table ... and the implied owner would be the "dbo" (database owner). So, you MIGHT be able to do the following...

select a1., b1. from database1.table1 a1, database2.table2 b1 where a1.fld1 = b1.fld1 ...

Answer 3

I have a feeling CakePHP can handle cross-database relations. Try setting $useDbConfig for each model to a connection for the respective database. CakePHP should generate multiple queries (atleast one per database connection) and join the results together for you. This approach should work fine for simple relations, but there might not be full support for relations such as HABTM.