SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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. #2
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    1 no
    2 yes

  3. #3
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by hash View Post
    1 no
    2 yes
    is it really impossible for #1 by using a single query.

    Can't we perform like:
    Code MySQL:
    SELECT fields FROM db2.products2 INNER JOIN db3.products3 ON products2.sku = products3.sku

    by any means?

  4. #4
    rajug.replace('Raju Gautam'); bronze trophy Raju Gautam's Avatar
    Join Date
    Oct 2006
    Location
    Kathmandu, Nepal
    Posts
    4,013
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I personally don't know other than the following:
    Code php:
    $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.
    Mistakes are proof that you are trying.....
    ------------------------------------------------------------------------
    PSD to HTML - SlicingArt.com | Personal Blog | ZCE - PHP 5

  5. #5
    SitePoint Wizard PHPycho's Avatar
    Join Date
    Dec 2005
    Posts
    1,201
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What will be the possible values of '?' in below to make the thing work?
    Note: Consider the example mentioned above(initial post)
    PHP Code:
    $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($arraytrue).'</pre>'//required result 

  6. #6
    SitePoint Wizard
    Join Date
    Nov 2005
    Posts
    1,191
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

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