Page 1 of 1

query in multiple DB

Posted: Wed Jan 13, 2010 4:24 am
by PHPycho
Suppose i have three dbs on the same server.
DB #1:

Code: Select all

host: localhost
user: root1
pass: pass1
Db: db1
DB #2:

Code: Select all

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

DB #3:

Code: Select all

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

Code: Select all

products2
--------
id
sku
name
...
 
DB #3 has table called products3

Code: Select all

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

Re: query in multiple DB

Posted: Wed Jan 13, 2010 4:26 am
by Grizzzzzzzzzz
would would you have 3 different dbs on the same server?

Why not have 1 database, and 3 tables in it?

Re: query in multiple DB

Posted: Wed Jan 13, 2010 4:57 am
by PHPycho
that is the requirement

Re: query in multiple DB

Posted: Wed Jan 13, 2010 5:51 am
by Grizzzzzzzzzz
:?

Then connect to one, check any values/store data in variables as required, disconnect from it, connect to the next one, process the information required against your stored variables, store any new information, disconnect from that one, connect to the next one, do your processing etc..

are you sure you have 3 instances of MySql running?

or do you mean you have 3 different databases within the same instance?

Re: query in multiple DB

Posted: Wed Jan 13, 2010 6:34 am
by PHPycho
different databases within the same instance

Re: query in multiple DB

Posted: Wed Jan 13, 2010 7:25 am
by Grizzzzzzzzzz
ahhhhh right i understand now.

i'm guessing you'd be able to use the mysqli_multi_query

If you're able to make use of the mysqli functions.

I've not used mysqli before so i'm not certain, but i'm guessing if it is possible, you'd want something like

Code: Select all

 
$mysqli = new mysqli("localhost", "root", "user");
 
$databaseName = "db1";
$databaseName2 = "db2";
 
$query = "SELECT * FROM " . $databaseName . ".tablename;";
$query .= "SELECT * FROM " . $databaseName2 . ".tablename";
 
if ($mysqli->multi_query($query)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                printf("%s\n", $row[0]);
            }
            $result->free();
        }
        if ($mysqli->more_results()) {
            printf("-----------------\n");
        }
    } while ($mysqli->next_result());
}
 
theoretically it should work.

Your biggest problem is you have different user/password connection details for different databases, unsure how you'd get around that.

Multiple queries to different databases may work through that method though.


Your second option you mentioned depends on how much imformation those arrays are going to contain, but you may have to resort to that if my guesswork above doesn't work.

Re: query in multiple DB

Posted: Wed Jan 13, 2010 2:17 pm
by AbraCadaver
Querying two databases is easy if you can use the same connection. Unfortunately the previous example has only connected to the database with one user which only has access to one table. You'll have to go the longer route, which may not be efficient but it is your only way that I can see.