...

View Full Version : query in multiple DB



PHPycho
01-13-2010, 11:24 AM
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

oesxyl
01-13-2010, 11:35 AM
My Question is:
1> is it possible to make a single query for two database? If yes how?
I never need this and never try but I think is possible:
a) address the fields using database_name.table_name.field_name
b) give access to one user to all databases:
http://dev.mysql.com/doc/refman/5.0/en/grant.html

best regards

PHPycho
01-13-2010, 12:25 PM
I never need this and never try but I think is possible:
a) address the fields using database_name.table_name.field_name
b) give access to one user to all databases:
http://dev.mysql.com/doc/refman/5.0/en/grant.html

best regards

how would be that possible for a)
since we have one connection id for a db i.e.

$link_id = mysql_query($host2, $user2, $pass2);
mysql_select_db($db2);
mysql_query($sql, $link_id);

as you see we can have only one link_id for one query.

I was supposing to have like this:
SELECT fields FROM db2.products2 INNER JOIN db3.products3 ON products2.sku = products3.sku
but how's this possible?

oesxyl
01-13-2010, 12:41 PM
how would be that possible for a)
since we have one connection id for a db i.e.

$link_id = mysql_query($host2, $user2, $pass2);
mysql_select_db($db2);
mysql_query($sql, $link_id);

as you see we can have only one link_id for one query.

I was supposing to have like this:
SELECT fields FROM db2.products2 INNER JOIN db3.products3 ON products2.sku = products3.sku
but how's this possible?
you can address a field using database, table and field names from both table like this:


select db1.product1.field1, db2.product2.field2 from db1.product1, db2.product2


but the user from db2 must have access to db1 if you use the connection as you said.

http://dev.mysql.com/doc/refman/5.0/en/identifier-qualifiers.html

best regards



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum