Create a MySQL view table on one database but fill with results from another database I have 2 databases: myshop (the Drupal db) and myshop_migrate (the Migrate db).  To make things tricky - both databases reside on different AWS instances.

I need to create a mysql view on myshop to hold a SELECT * query from the myshop_migrate database.  

Currently, I have: 


db_set_active('myshop_migrate_db'); //reference migrate db
$query = db_query('CREATE OR REPLACE VIEW myshop_migrate_stock_commerce AS SELECT * FROM myshop_migrate.stock_commerce');



but this just creates the VIEW table on the myshop_migrate database (as the current active db is referencing myshop_migrate).. 



db_set_active(); //default drupal db
$query = db_query('CREATE OR REPLACE VIEW myshop_migrate_stock_commerce AS SELECT * FROM myshop_migrate.stock_commerce');



This wont work because myshop_migrate.stock_commerce doesn't exist on the myshop database!


Completely lost on this.  Can I somehow store the result of the SELECT * from myshop_migrate in a variable? Switch the active db, create a view table on myshop, then import the variable into the table?
Since that view is only a SELECT on that table, try using a FEDERATED table instead.

I never had the chance to use it so far, but it should go like this:

CREATE TABLE myshop_migrate ( exactly like myshop table )
ENGINE=FEDERATED
DEFAULT CHARSET=same of myshop
CONNECTION='mysql://user@host:9306/myshop_migrated/stock_commerce';


where user and host are those of the remote server.

create it from phpmyadmin or mysql workbench, just to have additional options and hints that drupal can't give you.