How to Perform Cross-Database Queries in PHP?

Cross-Database Queries in PHP: A Comprehensive Guide

This article addresses the issue of constructing cross-database queries in PHP, a problem that was previously explored in the context of MySQL. Despite successfully achieving cross-database queries in MySQL, the author stumbled upon failures when attempting to replicate the process in PHP.

Specifically, the author questions the limitations imposed by mysql_select_db on using only one database at a time, making cross-database queries impractical. Additionally, the alternative of specifying the database for every query is deemed tedious.

Cross-Database Queries in PHP

Overcoming the limitations of mysql_select_db, the author received a solution that enables direct cross-database joins:

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

In this example, the database specified in mysql_select_db becomes the default database for the script. However, the query can specify tables from different databases by manually referencing them with the appropriate database name, as seen with another_db.table_on_another_db.

Alternative Approach for Different Hosts

If the databases reside on different hosts, direct joins become impossible. Instead, a two-query approach is recommended:

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

This method involves making two separate queries: one on each database. The first query retrieves the ids of records that meet the criteria in the first database. These ids are then used as a filter for the second query, which fetches the corresponding records from the second database.

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!