query in multiple DB

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

query in multiple DB

Post 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
User avatar
Grizzzzzzzzzz
Forum Contributor
Posts: 125
Joined: Wed Sep 02, 2009 8:51 am

Re: query in multiple DB

Post by Grizzzzzzzzzz »

would would you have 3 different dbs on the same server?

Why not have 1 database, and 3 tables in it?
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Re: query in multiple DB

Post by PHPycho »

that is the requirement
User avatar
Grizzzzzzzzzz
Forum Contributor
Posts: 125
Joined: Wed Sep 02, 2009 8:51 am

Re: query in multiple DB

Post 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?
User avatar
PHPycho
Forum Contributor
Posts: 336
Joined: Fri Jan 06, 2006 12:37 pm

Re: query in multiple DB

Post by PHPycho »

different databases within the same instance
User avatar
Grizzzzzzzzzz
Forum Contributor
Posts: 125
Joined: Wed Sep 02, 2009 8:51 am

Re: query in multiple DB

Post 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.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: query in multiple DB

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply