SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    winter is around the corner Tomer's Avatar
    Join Date
    Jul 2005
    Location
    Israel
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Querying from two databases

    Hey,

    I need to query from two servers at once [in the same script], both databases are on the same server [localhost] and use the same username & password. How can this be done [PHP]?

    Thanks, I've looking a lot on Google, can't find the answer.
    - Tomer

  2. #2
    SitePoint Addict
    Join Date
    Apr 2004
    Location
    Belgian in Mexico
    Posts
    307
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I use this connection function when I need to connect to various databases and/or hosts in the same script:
    PHP Code:
    $dbhost "localhost";
    $dbusername ""// Username
    $dbuserpass ""// Password
    $dbconnect NULL;
     
    function 
    db_connect($dbname$host NULL) {
    global 
    $dbconnect$dbhost$dbusername$dbuserpass;
    $dbhost $host == NULL $dbhost $host;
     
    if (!
    $dbconnect$dbconnect mysql_connect($dbhost$dbusername$dbuserpass);
    if (!
    $dbconnect) {
        return 
    0;
    } elseif (!
    mysql_select_db($dbname)) {
        return 
    0;
    } else {
        return 
    $dbconnect;
    }
    }
     
    $dbconnect db_connect($dbname$dbhost) or die(mysql_error());
    $query "SELECT ... FROM ...";
    $result mysql_query($query$dbconnect) or die(mysql_error());
    $dbconnect false//reset dbconnect value so it is possible to connect to other database 
    Hope that helps,

    MichaŽl
    MichaŽl Niessen
    http://assemblysys.com
    (Countries/states/cities with latitude & longitude,
    weathercodes & topical databases)

  3. #3
    SitePoint Member aquarelle's Avatar
    Join Date
    May 2006
    Location
    Sydney, Australia
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Tomer
    Hey,

    I need to query from two servers at once [in the same script], both databases are on the same server [localhost] and use the same username & password. How can this be done [PHP]?

    Thanks, I've looking a lot on Google, can't find the answer.
    - Tomer
    Hi Tomer,

    Do you mean query from 2 dabases at once? If both databases are on the same server all you need to do is to qualify your table names with database name as well.

    For example,
    db1:
    tblCustomer

    db2:
    tblCompany
    Code:
    Select somefields
    FROM db1.tblCustomer, db2.tblCompany
    WHERE some join
    provided it make sense to join the two tables in the first place.

    Cheers, Aq

  4. #4
    winter is around the corner Tomer's Avatar
    Join Date
    Jul 2005
    Location
    Israel
    Posts
    684
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks I'll try it.

    What I want to do is this

    while (rows in first database)
    {
    use 2nd database
    }

    So I have to reset the connection in the end of the loop?

    - Tomer

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    if the databases are on the same server, you don't need to put a query in a loop; you can just use a join like aq showed in post 3.

  6. #6
    SitePoint Member aquarelle's Avatar
    Join Date
    May 2006
    Location
    Sydney, Australia
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Tomer
    while (rows in first database)
    {
    use 2nd database
    }

    So I have to reset the connection in the end of the loop?
    Hi Tomer,

    Performing query inside a loop is a bad practice as it is not scalable. Imagine that your initial query returns a million rows, this means you will be performing a million queries in the loop!

    Since both your databases reside within the same MySQL Server, you will be able to join them.

    Let's say you want the first query to return some IDs from the first database. Then in your loop you are just looking up the second database table based on those IDs.

    db1:
    tblTable1 - customerID

    db2:
    tblTable2 - companyID, customerID, companyName, companyAddress

    So you formulate your query as:

    Code:
    SELECT b.companyName, b.companyAddress
    FROM db1.tblTable1 As a, db2.tblTable2 As b
    WHERE a.customerID = b.customerID
    Note that a, b are aliased that you can assign to the different database tables.

    Cheers, Aq


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
  •