Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 4 of 4
  1. #1
    Regular Coder
    Join Date
    Dec 2005
    Posts
    217
    Thanks
    1
    Thanked 0 Times in 0 Posts

    query in multiple DB

    Suppose i have three dbs on the same server.
    DB #1:
    Code:
    host: localhost
    user: root1
    pass: pass1
    Db: db1
    DB #2:
    Code:
    host: localhost
    user: root2
    pass: pass2
    Db: db2

    DB #3:
    Code:
    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:
    products2
    --------
    id
    sku
    name
    ...
    DB #3 has table called products3
    Code:
    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
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by PHPycho View Post
    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

  • #3
    Regular Coder
    Join Date
    Dec 2005
    Posts
    217
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by oesxyl View Post
    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.
    PHP Code:
    $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?

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by PHPycho View Post
    how would be that possible for a)
    since we have one connection id for a db i.e.
    PHP Code:
    $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:
    Code:
    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/...ualifiers.html

    best regards


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •