Query in multiple DB

PHP
1

Suppose i have three dbs on the same server.
DB #1:

host: localhost
user: root1
pass: pass1
Db: db1

DB #2:

host: localhost
user: root2
pass: pass2
Db: db2

DB #3:

host: localhost
user: root3
pass: pass3
Db: db3

You have noticed from above that i have three DBs on the same server but have different users.

Summary of DBs
DB #1 is use for main login page.
DB #2 has table called products2

products2
--------
id
sku
name
...

DB #3 has table called products3

products3
---------
id
sku
name
....

What i want to do is:
I want perform query in DB #2’s products2 table & DB #3’s products3 so that i can display the products with common sku.
Hope my problem is clear.

My Question is:
1> is it possible to make a single query for two database? If yes how?
2> if above is not possible then,
Would it be effective to make separate fetching from two dbs as an arrays.
And manipulating that array for the required results?

Thanks

2

1 no
2 yes

3

is it really impossible for #1 by using a single query.

Can’t we perform like:

SELECT fields FROM db2.products2 INNER JOIN db3.products3 ON products2.sku = products3.sku

by any means?

4

I personally don’t know other than the following:


$con1 = mysql_connect('localhost', 'root1', 'pass1') or die(mysql_error() . ' At first connection');
mysql_select_db('db1', $con1);
$con2 = mysql_connect('localhost', 'root2', 'pass2') or die(mysql_error() . ' At second connection');
mysql_select_db('db2', $con2);
$con3 = mysql_connect('localhost', 'root3', 'pass3') or die(mysql_error() . ' At third connection');
mysql_select_db('db3', $con3);

$products = array();

$result = mysql_query("SELECT id,sku,name FROM products2", $con1) or die(mysql_error());
while($rows = mysql_fetch_object($result))
	$data[] = $rows;

$result = mysql_query("SELECT id,sku,name FROM products2", $con2) or die(mysql_error());
while($rows = mysql_fetch_object($result))
	$data[] = $rows;

There is one php function mysql_db_query() but this function has been DEPRECATED as of PHP 5.3.0 and REMOVED as of PHP 6.0.0.

ADODB and mysqli extensions might have such facilities if anyone have used so far, would be grateful to them who can share here.

5

What will be the possible values of ‘?’ in below to make the thing work?
Note: Consider the example mentioned above(initial post)

$link = mysql_connect('localhost', ?, ?); // 
mysql_select_db(?);
$sql = "SELECT 
			`db2`.`products2`.`name`
			,`db2`.`products2`.`sku` 
		FROM
			`db2`.`products2` INNER JOIN `db3`.`products3`
			ON `db2`.`products2`.`sku` = `db3`.`products3`.`sku`";
$result = mysql_query($sql, $link);
$array 	= array();
while($row = mysql_fetch_array($result)){
	$array[] = $row;
}
echo '<pre>'.print_r($array, true).'</pre>'; //required result
6

If you have three different users then it would require three connections, so not possible. But if you give one user access to all dbs then that will be fine, just don’t use select_db, and reference db.table.field as you are doing.