I need a solution for save every MySQL connections in PHP and use it in sql transactions between connections.
I know that I can't do transactions between different connections but I want to handle in PHP or a solution. (I don't know "what is the solution?")
Is that right, about :
completing question :
I have multiple databases that connected with different users. One or more database are common between other databases that use it on the joins, for example :
db_common with common_user and tables: common_1, common_2, ...
db_specific_1 with user_1 and tables: s1_1, s1_2, ...
db_specific_2 with user_2 and tables: s2_1, s2_2, ...
May be join db_specific_1 with common or even with db_specific_2.
I want to remove joins between databases. Because, db_specific_1 is for a project and db_specific_2 is for other project. Since the database server is the same one, I granted select access to user_1 to db_specific_2, that is wrong, since db_specific_2 must be transfer to other server.
I need transaction between db_specific_1 and db_specific_2 and db_common.
I setup the access rights so that each project needs only one database connection. Usually each project has a main part of a database. For example:
grant all on `database`.* to `username`@localhost
If the same user have full access to another database2, I add
grant all on `database2`.* to `username`@localhost
But usually, only some tables or columns must be accessed, and sometime only readonly:
grant select on `database2`.stats to `username`@localhost
In PHP use database.table instead of table to select foreign tables:
SELECT a.name, b.stat
FROM `mytable` a
LEFT JOIN `database2`.`stats` b ON b.id = a.id