How to build cross database query in PHP?

Question

In our last episode (How to Build Cross-Database Queries in MySQL), I learned how to build cross-database queries in MySQL. This worked great, but when our hero tried to use this newfound knowledge in PHP, he found that his best friend failed.

I looked at PHP's mysql_select_db. This seems to mean that if I want to use MySQL with PHP, I have a few options:

1. Use mysql_select_db but only one database can be used at a time. This is our current setup, having the database as a namespace identifier doesn't seem to work (it works fine in the MySQL shell, so I know it's not an issue with our MySQL server setup).

2. Do not use mysql_select_db. From some examples I've seen, this seems to mean that I have to specify the database for every query I make. This makes sense since I'm not using mysql_select_db to tell PHP which database I want to access. This is also sad because I don't want to go through all the code and prefix each query with the database name.

Is there anything better than this? Is there a way for me to do a cross-database MySQL query in PHP without having to do something crazy like (2)?

Clarification: None of the answers proposed actually let me do cross-database queries. Instead, they allow me to access two different databases separately. I want a solution that allows me to do something like SELECTforeign_db.login.username,firstname,lastname fromforeign_db.login,user where... instead of just doing different queries to different databases . For what it's worth, (2) doesn't work for me.

Answer 1

After reading your instructions, I have the impression that you actually want to query a table that resides in two separate MySQL server instances. At least, your clarifying text:

It is recommended that you wish to run a query while logged in as two users (which may or may not reside on the same mysql server instance).

In your question, you say you want to query data from two different databases, but it's important to realize that a MySQL instance can have many, many databases. For multiple databases managed by the same mysql instance, the solution proposed in the question you linked to is simple: just prefix the database name before the table name, separating the database and table names with a dot: ..

But, as I pointed out, this only works if:

1. All databases you access in a query reside on the same server - i.e. managed by the same MySQL instance
2. The user connected to the database has the correct permissions to access these two tables.

Scenario 1: Database on the same host: grant appropriate permissions and qualify table names

So if these tables actually reside on the same mysql instance, there is no need for a second login or connection - just grant the database user you are using to connect to the database the appropriate permissions, from where you need Select from all tables. You can do this using the GRANT syntax, documented here: http://dev.mysql.com/doc/refman/5.1/en/grant.html

For example, GRANT SELECT ON sakila.film TO 'test'@'%' will allow the user test@% to select data from film sakila Table in the database. Once this is done, the user can reference the table using sakila.film (the so-called qualified table name), or if the current database is set to sakila, simply look like this 电影

Scenario 2: Database managed by different MySQL instances: FEDERATED engine

If the table you are accessing is actually managed by two different MySQL instances, there is a trick that may or may not work depending on your configuration. Starting from MySQL 5.0, mysql supports FEDERATED storage engine. This allows you to create a table that is not actually a table, but a peephole into the table on the remote server. The engine is documented here: http://dev.mysql.com/doc/refman/5.1/en/federated-storage-engine.html

For example, if you know that the misc database on the remote host has this table:

CREATE TABLE t (
    id int not null primary key
,   name varchar(10) not null unique
)

You can create a local "pointer" to the remote table using the following command:

CREATE TABLE t (
    id int not null primary key
,   name varchar(10) not null unique
)
ENGINE = FEDERATED
CONNECTION='mysql://<user>@<remote-server>:<remote-port>/misc/t';

Unfortunately, the FEDERATED engine is not always available, so you must first check if you can use it. But assuming that is the case, then you can simply use the local table t in your query, just like any other table, and MySQL will communicate with the remote server and perform the appropriate operations on the physical table on the other end.

Warning: FEDERATED tables have multiple optimization issues. You should understand whether and to what extent these apply to you. For example, in many cases applying WHERE to a union table may cause the entire table contents to be pulled over the network to the local server, where the actual filtering is applied. Another issue is table creation: you have to be very sure that the definitions of the union table and the table it points to match exactly, except for the ENGINE clause (and CONNECTION). For example, if you have different character sets, the data may be completely garbled after being transmitted over the network.

If you want to use FEDERATED tables, please read this articlehttp://oreilly.com/pub/a/databases/2006/08/10/mysql-federated-tables. html to decide whether it is suitable for your specific use case.

If you think you really need it, I have a utility to create a union table here: http://forge.mysql.com/tools/tool.php?id=54

Scenario 3: FEDERATED cannot be used, but the table is on a different MySQL instance

Finally, if you have tables on different MySQL instances, but for some reason can't use the federated table engine, then I'm afraid you're out of luck. You simply execute a query against two MySQL instances, receive the results, and perform some smart operations in PHP. Depending on your specific requirements, this may be a perfectly viable solution

I guess you need to decide for yourself which part of my answer best solves your problem and add a comment in case you need more help. Tia Loren.

Answer 2

You will need the database to be running on the same host.

If so, you should be able to use mysql_select_db on your favorite/default database and specify the external database manually.

$db = mysql_connect($hots, $user, $password);
mysql_select_db('my_most_used_db', $db);

$q = mysql_query("
    SELECT *
    FROM   table_on_default_db a, `another_db`.`table_on_another_db` b
    WHERE  a.id = b.fk_id
");

If your database is running on a different host, you will not be able to join directly. But then you can do 2 queries.

$db1 = mysql_connect($host1, $user1, $password1);
$db2 = mysql_connect($host2, $user2, $password2);

$q1 = mysql_query("
    SELECT id
    FROM   table
    WHERE  [..your criteria for db1 here..]
", $db1);
$tmp = array();
while($val = mysql_fetch_array($q1))
    $tmp[] = $val['id'];

$q2 = mysql_query("
    SELECT *
    FROM   table2
    WHERE  fk_id in (".implode(', ', $tmp).")
", $db2);