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

问题描述

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

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.这似乎暗示如果我想在 PHP 中使用 MySQL，我有几个选择:

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 但一次只能使用一个数据库.这是我们当前的设置，将数据库作为命名空间标识符似乎不起作用(它在 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)?

澄清:建议的答案实际上都没有让我进行跨数据库查询.相反，它们允许我分别访问两个不同的数据库.我想要一个解决方案，它允许我执行诸如 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屋！