Home >Database >Mysql Tutorial >How can I perform cross database queries in PHP with multiple databases on the same host?
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.
The two primary options available when using mysql_select_db in PHP are:
Neither of these options aligns with the goal of performing cross database queries without laborious changes to existing code.
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 ");
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!