>> >How do I at the best way perform select statements over multiple
>databases?
If you're talking about different databases handled by the same
server, you can use the databasename.ta blename to refer to tables
outside the current database.
If you're talking about different databases on different servers
potentially on different continents, MySQL has the "Federated" table
type, which allows you to reference a table on another MySQL server
as though it was on this one. Requires at least MySQL 5.0, as I
recall. Don't expect it to be fast. I suspect it fetches the
entire table every time.
Gordon L. Burditt
Hi,
yes, I may use db1.table1, db2.table approach since all my databases
are on the same node at the moment. Still, I want to transparantly be
able to add or remove databases from the set without changing the
specific sql statements refering to the table.
You have to tell it where the tables are. You are stuck with that.
Why are all the tables in different databases?
You might be able to refer to the table as $database_of_ta ble1.table1
and let PHP substitute it, but there's got to be a file somewhere
you have to change if you move a table. You could also create a
federated table that points at the actual location of the table,
and re-create it if the actual table moves.
I suppose you could do something like selecting from
INFORMATION_SCH EMA.TABLES to figure out which databases have tables
named 'foo', and then try to figure out which one you should use.
(Requires MySQL 5.0 or greater, doesn't work across multiple servers,
and doesn't really solve the problem anyway).
I don't know of any way to *REMOVE* a table (vs. moving it elsewhere)
without affecting a SQL statement that uses it.
I don't know of any way to *ADD* a table to a SQL statement (presumably
a join) without affecting the SQL statement.
The best way I have found to keep databases out of the SQL is to
put all the tables in the same database, and use an include file
which defines the variables $mysql_server, $mysql_user, $mysql_password ,
and $mysql_db, which I can change for the application without
changing the rest of the page. The application sets the current
database once and never changes it.
If for some reason I can't put all the tables in the same database
I usually end up using two connections (potentially on different
servers) with two sets of these. This approach does not scale very
well, and you can't do joins between tables on different connections.
Gordon L. Burditt
