I need to JOIN
two different databases which I've already accomplished statically for testing purposes only.
- The first database is already defined in the connection itself.
- The second database must be dynamically derived from information within the first database that must first be defined in a user-defined variable. To keep things simple I will simply refer to the second database name as
example2
. - Statically defining the second database name works and all the
JOIN
ed data is successfully pulled however with my current comprehension if I did this on a live (or "production") server I'd have to run two separate queries on the same connection with some PHP code in between those two queries. - I can not define the second database name without running a second query and the point is to avoid making multiple connections to the SQL server when I know there is a way to do it in a single query (if we do not count setting a user-defined variable a query).
Here is a static demonstration of what worked for me:
SELECT * FROM example1 AS e1
INNER JOIN example2.accounts AS e2a ON (e2a.id = e1.accounts_id);
However the following does not work:
SET @db = 'example2';
SELECT * FROM example1 AS e1
INNER JOIN @db.accounts AS e2a ON (e2a.id = e1.accounts_id);
How can I set and use a user-defined variable to dynamically define the second database's name and use it to dynamically JOIN
the tables between the two databases?
- All data is properly stored/formatted/etc.
- I already have the user-defined variable working, I need help implementing it on the database-to-database JOIN.
- This sort of relates to the fact that PHP has variable variables.