在php中连接多个数据库并跨数据库进行联接查询 [英] Connecting multiple database and join query across database in php

问题描述

我要使用php在2个数据库中进行联接查询.

这是我的第一次联系.

$conn = mysql_connect('localhost','root1','pass1'); 
@mysql_select_db('database1',$conn);

这是我的第二次联系.

$conn1 = mysql_connect('localhost','root2','pass2'); 
@mysql_select_db('database2',$conn1);

如果我想从database1中获取数据，我正在做以下事情.

$sql = 'SELECT * FROM users';

$result = mysql_query($sql, $conn);

print_r(mysql_fetch_array($result));

类似地，对于第二个数据库2

$sql = 'SELECT * FROM orders';

$result = mysql_query($sql, $conn1);

print_r(mysql_fetch_array($result));

但是当我进行如下的联接查询时，我遇到了问题

$sql = 'SELECT a.fname AS fname, a.lname AS lname FROM database1.users a JOIN database2.orders b ON b.creator_id = a.id';

$result = mysql_query($sql);//what should be second parameter over here.

print_r(mysql_fetch_array($result));

解决方案

您可以通过在表名之前加上数据库名来实现此目的，如示例中所建议的那样.但是，已登录的用户需要使用相同的凭据访问两个数据库.最后一部分非常重要，否则您将无法做到.

这为您提供了一个简单而直接的示例:链接

该链接中的示例(仅在具有相同凭据的情况下有效):

SELECT
    c.customer_name,
    o.order_date
FROM
    db1.tbl_customers c LEFT JOIN
    db2.tbl_orders o ON o.customer_id = c.id

Using php i wants to make a join query across 2 database.

This is my first connection.

$conn = mysql_connect('localhost','root1','pass1'); 
@mysql_select_db('database1',$conn);

This is my second connection.

$conn1 = mysql_connect('localhost','root2','pass2'); 
@mysql_select_db('database2',$conn1);

If i wants to get the data from database1 i am doing following thing.

$sql = 'SELECT * FROM users';

$result = mysql_query($sql, $conn);

print_r(mysql_fetch_array($result));

Similarly for second database2

$sql = 'SELECT * FROM orders';

$result = mysql_query($sql, $conn1);

print_r(mysql_fetch_array($result));

But i am facing problem when i am making join query as follows

$sql = 'SELECT a.fname AS fname, a.lname AS lname FROM database1.users a JOIN database2.orders b ON b.creator_id = a.id';

$result = mysql_query($sql);//what should be second parameter over here.

print_r(mysql_fetch_array($result));

解决方案

You can do this by preceding the table name also with the database name, like you proposed in the example. But the logged on user needs to have access to both databases under the same credentials. This last part is very important, else you won't be able to do it.

This gives you an easy but straightforward example: link

Example taken from that link (will only work with same credentials):

SELECT
    c.customer_name,
    o.order_date
FROM
    db1.tbl_customers c LEFT JOIN
    db2.tbl_orders o ON o.customer_id = c.id

这篇关于在php中连接多个数据库并跨数据库进行联接查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！