How to Perform Cross Database Queries in PHP?

Cross Database Queries in PHP

In a previous discussion, cross database queries in MySQL were addressed. However, when attempting to implement this knowledge in PHP, challenges arose.

The Problem:

PHP offers two approaches using mysql_select_db:

1. Using mysql_select_db restricts access to a single database at a time.
2. Omitting mysql_select_db requires specifying the database with each query, which is inconvenient and impractical.

The Solution:

To perform cross database queries in PHP without excessive modifications, the following steps can be taken:

1. Ensure that the databases reside on the same host.
2. Establish a connection to the preferred database using mysql_select_db.
3. Manually specify the foreign database in the query, as seen below:

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

If the databases are on different hosts, direct joins are not possible. In that case, two queries can be performed instead:

$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 to Perform Cross Database Queries in PHP?. For more information, please follow other related articles on the PHP Chinese website!