How can I perform cross database queries in PHP with multiple databases on the same host?

Cross Database Queries in PHP

In a previous article, we explored the construction of cross database queries in MySQL. While this approach worked effectively in MySQL, its direct application in PHP proved challenging. Specifically, the use of mysql_select_db in PHP poses limitations when attempting to access multiple databases.

Options and Limitations

The two primary options available when using mysql_select_db in PHP are:

1. Restricting access to a single database, preventing cross database queries.
2. Omitting mysql_select_db, which requires specifying the database for each query.

Neither of these options aligns with the goal of performing cross database queries without laborious changes to existing code.

Solution: Same Host Configuration

To address this issue, your databases must reside on the same host. This allows you to specify a foreign database manually while maintaining a connection to your preferred default database.

$db = mysql_connect($host, $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
");

Alternate Solution: Separate Hosts

If your databases reside on different hosts, direct joins are not possible. In this scenario, you can execute separate queries to a specific host:

$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);

The above is the detailed content of How can I perform cross database queries in PHP with multiple databases on the same host?. For more information, please follow other related articles on the PHP Chinese website!