如何在PHP中构造跨数据库查询? [英] How do I construct a cross database query in PHP?

问题描述

在我们的最后一集中(我如何构建一个MySQL中的跨数据库查询)，我学习了如何在MySQL中构造跨数据库查询.效果很好，但是当我们的英雄尝试在PHP中使用这种新发现的知识时，他发现了他的最好的朋友FAIL在等他.

In our last episode (How I constructed a cross database query in MySQL) I learned how to construct a cross database query in MySQL. This worked great but when our hero tried to use this newfound knowledge in PHP he found his best friend FAIL waiting for him.

我查看了PHP的mysql_select_db.这似乎暗示着，如果我想将MySQL与PHP结合使用，我有两种选择:

I took a look at mysql_select_db for PHP. This seems to imply that if I want to use MySQL with PHP, I have a couple of choices:

1. 使用mysql_select_db，但每次只能使用一个db.这是我们当前的设置，将数据库作为名称空间标识符似乎不起作用(它在MySQL Shell中运行良好，因此我知道这与我们的MySQL服务器设置无关).

1. Use mysql_select_db but be stuck with only using one db at a time. This is our current setup and putting a database as a namespace identifier doesn't seem to work (it works fine in the MySQL shell so I know it's not a problem with our MySQL server setup).

请勿使用mysql_select_db.从我看到的一些示例中，这似乎意味着我必须为我进行的每个查询指定数据库.这是有道理的，因为我没有使用mysql_select_db告诉PHP我想访问什么数据库.这也让我很难过，因为我不想遍历所有代码并为每个查询添加一个数据库名称.

Don't use mysql_select_db. From some of the examples I've seen, this seems to mean that I have to specify the db for every query that I make. This makes sense since I haven't used mysql_select_db to tell PHP what db I want to access. This also makes sad since I don't want to go through all my code and prepend a db name to every query.

还有什么比这更好的了吗?有没有一种方法可以让我在PHP中执行跨数据库MySQL查询，而不必像(2)那样疯狂呢?

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

澄清:建议的答案中没有一个实际上允许我执行跨数据库查询.相反，它们允许我分别访问两个不同的DB.我想要一个解决方案，使我能够做类似SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where ...的事情，而不仅仅是对不同的数据库进行不同的查询.对于它的价值，(2)对我不起作用.

CLARIFICATION: None of the proposed answers actually let me do a cross db query. Instead, they allow me to access two different DBs separately. I want a solution that allows me to do something like SELECT foreign_db.login.username, firstname, lastname from foreign_db.login, user where ... NOT just make different queries to different DBs. For what it's worth, (2) doesn't work for me.

推荐答案

您将需要数据库在同一主机上运行.

You will need your databases to run on the same host.

如果是这样，您应该能够在收藏夹/默认数据库上使用mysql_select_db并手动指定外部数据库.

If so, you should be able to use mysql_select_db on your favourite/default db and manually specify a foreign database.

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

如果数据库在其他主机上运行，​​则将无法直接加入.但是您可以进行2个查询.

If your databases run on a different host, you won't be able to join directly. But you can then make 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);

这篇关于如何在PHP中构造跨数据库查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！