SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to use PHP to access 2 databases simultaneously?

    I need to create two database connections simultaneously( using MySQL at the moment but I need to also support MS SQL). I've been trying with the MySQL.php class script from Kevin Yank by creating a 2nd different instance but as soon as I do that the first instance seems to get lost even though I use a different resource object variable. Can this be done? I could use 1 instance and open and close the databases each time but this seems like it would be much slower not to mention a pain to code. Thanks for your suggestions.

  2. #2
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Selecting the database with mysql_select_db is only one way to do it. You can use database names in queries if you wish.

    If you have a database called store with a table called cart, with a field called amount, you can address that field using

    Code:
    store.cart.amount
    If you only use a second db occasionally, there isn't much trouble in using that syntax to get to it.

  3. #3
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP will reuse database connections that have the same host, username, and password; thus you can only have multiple connections if this data is unique per connection. I don't know how Kevin Yank's class works (never read his book), but using the base functions it can be done like so:
    PHP Code:
    $conn1 mysql_connect($host1$user1$pass1);
    $conn2 mysql_connect($host2$user2$pass2); 
    Again, this will only work if the host, user, and/or password are different between the two connections, otherwise $conn1 and $conn2 will in fact be the same connection.
    PHP questions? RTFM
    MySQL questions? RTFM

  4. #4
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,561
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    didnt' OP say MS SQL? You don't use mysql_connect() for that.

    Edit:
    Ah, ok. you are using mysql at the moment to create both connections. Well, you can always use the fourth argument (a boolean flag) to force the mysql_connect to create a new connection.

  5. #5
    SitePoint Wizard Hammer65's Avatar
    Join Date
    Nov 2004
    Location
    Lincoln Nebraska
    Posts
    1,160
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was assuming (perhaps wrongly), that the OP wanted to query two separate databases on the same server. You shouldn't need two separate connections for that.

  6. #6
    Worship the Krome kromey's Avatar
    Join Date
    Sep 2006
    Location
    Fairbanks, AK
    Posts
    1,621
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Hammer65 View Post
    I was assuming (perhaps wrongly), that the OP wanted to query two separate databases on the same server. You shouldn't need two separate connections for that.
    You're indeed correct. I read it as connecting to two separate database servers - perhaps I was the one who was wrong.

    Either way, the OP should have some good info to move forward with.
    PHP questions? RTFM
    MySQL questions? RTFM

  7. #7
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by kromey View Post
    PHP will reuse database connections that have the same host, username, and password; thus you can only have multiple connections if this data is unique per connection. I don't know how Kevin Yank's class works (never read his book), but using the base functions it can be done like so:
    PHP Code:
    $conn1 mysql_connect($host1$user1$pass1);
    $conn2 mysql_connect($host2$user2$pass2); 
    Again, this will only work if the host, user, and/or password are different between the two connections, otherwise $conn1 and $conn2 will in fact be the same connection.
    Yes, the host, user, and pass are the same, the databases are different(client wants data separated so don't go there...). I created two connections just as you illustrate and as you inform me, the 2nd connection seems to stomp the first. I wanted to be able to query from either database without have to check and open it first if not already open. I have a polling process that runs continously and does multiple queries to both DBs for each loop -- seems so inefficient to constantly open/close a shared connection?

  8. #8
    SitePoint Member
    Join Date
    Dec 2006
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Hammer65 View Post
    Selecting the database with mysql_select_db is only one way to do it. You can use database names in queries if you wish.

    If you have a database called store with a table called cart, with a field called amount, you can address that field using

    Code:
    store.cart.amount
    If you only use a second db occasionally, there isn't much trouble in using that syntax to get to it.
    Sorry didn't really digest this on first reading as I was so intent on replying to Kromey's explanation.

    Your solution looks very good -- it doesn't require a separate call prior to query to explicitly change the DB? Just add dbname as prefix to all columns?

    Now I am including DBName as fourth parm as that is what the class calls for:

    function DB ($host,$dbUser,$dbPass,$dbName)

    and includes

    mysql_select_db($this->dbName,$this->dbConn)

    I'll have to change the class to make that a separate function.

    So the mysql_select_db is really optional if I understand you correctly and I include the dbname as part of each columnname. I'll run with this info and come back if I have any more questions. Thanks!

  9. #9
    Passionate Web Developer Egyptechno's Avatar
    Join Date
    Jan 2004
    Location
    Dubai
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are 2 cases :

    1- the 2 dbs on 2 different servers, and in that case u will need :
    + create mysql_connect foreach db, 1st one the host will be " localhost" and the 2nd will be the ip address for the 2nd server
    + create 2 mysql_select_db
    + when u r trying to query, u have to pass the db resoure in the arguments

    PHP Code:
    function db_query($query$handler){
    return 
    mysql_query($query$handler);

    2- the 2 dbs on the same server, in that case u will need to :
    + create 1 mysql_connect for any db ( the 1st or the 2nd )
    + create 1 mysql_select_db

    then you can use db names in the queries normally, suppose that 1st db's name is first and the 2nd is second

    to query from the first :
    PHP Code:
    mysql_query("select f1,f2,f3 from first.table"); 
    and to query from the second :
    PHP Code:
    mysql_query("select f1,f2,f3 from second.table"); 

    using the same connection .. with no problems !
    I adore PHP


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
  •