Results 1 to 17 of 17
  1. #1
    Join Date
    Aug 2004
    Posts
    93

    mysql newbie: how to connect to multiple databases?

    I want to gather counts of records from tables in several different databases and display the results as just numbers next to some text like this:

    blog1
    posts (3)
    comments (12)

    blog2
    posts (11)
    comments (90)

    blog3
    posts (8)
    comments (34)

    Here's a first crack at it using 3 different databases (all have same userid/password):

    PHP Code:
    $db mysql_connect('localhost','myuserid','mypasswd'); 
    mysql_select_db('db01'); 
    $qryblog01_posts "select count(*) -1 as ct from wp_posts"
    $qryblog01_comments "select count(*) as ct from wp_comments"
    $result01_posts mysql_query($qryblog01_posts); 
    $result01_comments mysql_query($qryblog01_comments); 
    if (
    $result01_posts && $result01_comments

        
    $array01_posts mysql_fetch_array($result01_posts); 
        
    $array01_comments mysql_fetch_array($result01_comments); 

    mysql_close

    $db mysql_connect('localhost','myuserid','mypasswd'); 
    mysql_select_db('db02'); 
    $qryblog02_posts "select count(*) -1 as ct from wp_posts"
    $qryblog02_comments "select count(*) as ct from wp_comments"
    $result02_posts mysql_query($qryblog02_posts); 
    $result02_comments mysql_query($qryblog02_comments); 
    if (
    $result02_posts && $result02_comments

        
    $array02_posts mysql_fetch_array($result02_posts); 
        
    $array02_comments mysql_fetch_array($result02_comments); 

    mysql_close

    $db mysql_connect('localhost','myuserid','mypasswd'); 
    mysql_select_db('db03'); 
    $qryblog03_posts "select count(*) -1 as ct from wp_posts"
    $qryblog03_comments "select count(*) as ct from wp_comments"
    $result03_posts mysql_query($qryblog03_posts); 
    $result03_comments mysql_query($qryblog03_comments); 
    if (
    $result03_posts && $result03_comments

        
    $array03_posts mysql_fetch_array($result03_posts); 
        
    $array03_comments mysql_fetch_array($result03_comments); 

    mysql_close

    //Then I display the counts like this: 

       
    echo " (".$array01_posts[0].") (".$array01_comments[0].")"
       echo 
    " (".$array02_posts[0].") (".$array02_comments[0].")"
       echo 
    " (".$array03_posts[0].") (".$array03_comments[0].")"
    My question is this:

    Do I need to close and open the connection each time? Is there a way to just switch the database? Can someone point me to sample code that does this?

    The code above is working, but I'm sure it's a hack. Any suggestions for improvement welcome. Do I need to use an array? What kind of error handling should I use?

    Thanks in advance.

  2. #2
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,104
    You don't need to close the connection, just switch the database each time

    PHP Code:
    mysql_connect('localhost','foo','bar');
    mysql_select_db('zoo');

    // -- do some queries on 'zoo' database

    mysql_select_db('zoo1');

    // -- do some queries on 'zoo1' database

    mysql_select_db('zoo3');

    // -- do some queries on 'zoo3' database

    mysql_close(); // -- when you are finished with the CONNECTION, close it (not with the database). 
    Actually, PHP is smart in that it will not open up a new connection if you are using the same credentials to login to the server -- it will just reuse the same connection.

  3. #3
    Join Date
    Aug 2004
    Posts
    93
    Don't know why I didn't see this... man was that easy...

    PHP Code:
    $db mysql_connect('localhost','myuserid','mypasswd');  
    mysql_select_db('db01');  
    $qryblog01_posts "select count(*) -1 as ct from wp_posts";  
    $qryblog01_comments "select count(*) as ct from wp_comments";  
    $result01_posts mysql_query($qryblog01_posts);  
    $result01_comments mysql_query($qryblog01_comments);  
    if (
    $result01_posts && $result01_comments)  
    {  
        
    $array01_posts mysql_fetch_array($result01_posts);  
        
    $array01_comments mysql_fetch_array($result01_comments);  
    }  
    //mysql_close;  

    //@ $db = mysql_connect('localhost','myuserid','mypasswd');  
    mysql_select_db('db02');  
    $qryblog02_posts "select count(*) -1 as ct from wp_posts";  
    $qryblog02_comments "select count(*) as ct from wp_comments";  
    $result02_posts mysql_query($qryblog02_posts);  
    $result02_comments mysql_query($qryblog02_comments);  
    if (
    $result02_posts && $result02_comments)  
    {  
        
    $array02_posts mysql_fetch_array($result02_posts);  
        
    $array02_comments mysql_fetch_array($result02_comments);  
    }  
    //mysql_close;  

    //@ $db = mysql_connect('localhost','myuserid','mypasswd');  
    mysql_select_db('db03');  
    $qryblog03_posts "select count(*) -1 as ct from wp_posts";  
    $qryblog03_comments "select count(*) as ct from wp_comments";  
    $result03_posts mysql_query($qryblog03_posts);  
    $result03_comments mysql_query($qryblog03_comments);  
    if (
    $result03_posts && $result03_comments)  
    {  
        
    $array03_posts mysql_fetch_array($result03_posts);  
        
    $array03_comments mysql_fetch_array($result03_comments);  
    }  
    mysql_close
    Thanks for the help.

  4. #4
    Join Date
    Oct 2001
    Location
    Mountain Home Arkansas
    Posts
    131
    I use muliple databases all the time, and you don't really need to switch between them, just open them with different resource id's, and specify the resource link identifer in your queries. ( I accually always write my queries that way, incase I need to connect to a different database later on in development )


    PHP Code:
    $db1  mysql_connect('localhost','myuserid','mypasswd');  
    $sel1 mysql_select_db('db01'); 
    $db2  mysql_connect('localhost','myuserid','mypasswd');  
    $sel2mysql_select_db('db02'); 

    $res1 mysql_query($query$db1);
    $res2 mysql_query($query$db2); 
    note: then you also need to use the resource link idenitifier in functions like, mysql_affected_rows($db1), mysql_insert_id($db1), mysql_error($db1), etc...
    rocket science is more fun when you accually have rockets!

  5. #5
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,104
    In your case, PHP isn't actually going to open up another connection, just re-use the one you already made because the credentials are the same; so in essence you have really just wasted typing time

    If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters.

  6. #6
    Join Date
    Aug 2004
    Posts
    93
    Thanks for the reply, guys - helpful discussion.

    I'm still wondering if
    PHP Code:
    mysql_fetch_array 
    is the best (or only) way to grab the count here.

    Is this a hack or just the way it's done in mysql?

  7. #7
    Join Date
    Oct 2001
    Location
    Mountain Home Arkansas
    Posts
    131
    well, mysql_fetch_array() is not the only way, and I can't 100% say that it's the best way, but I think it's the most common way to loop through a database.

    You probably want to use it in a while loop.
    PHP Code:
    $res mysql_query($query$db);
    while ( 
    $array mysql_fetch_array($res) ){
      echo 
    $array['field1']. - '.$array['field2'].'<br />';

    rocket science is more fun when you accually have rockets!

  8. #8
    Join Date
    Jul 2003
    Location
    Kuwait
    Posts
    5,104
    Its not a hack, its just one of the many ways to get the information. You can use any of the mysql_fetch_* functions.

  9. #9
    Join Date
    Aug 2004
    Posts
    93
    10-4.

    The other thing I can do is lose the if statements. If the queries fail, the variables will be empty (non-printing) anyway.

    And I should probally wrap the whole thing in
    PHP Code:
    if ($db)
    {
    ...


  10. #10
    Join Date
    Oct 2001
    Location
    Mountain Home Arkansas
    Posts
    131
    the while() loop will take care of that for you without the need of an if() statement, the while() loop will only run while there is records to be found in the table
    rocket science is more fun when you accually have rockets!

  11. #11

    Unhappy multiple databases

    What I have so far is this:

    $connection="...";
    $username="...";
    $password="...";
    $database1="...1";
    $database2="...2";
    $link=mysql_connect($connection,$username,$password) or die(mysql_error());
    mysql_select_db($database1) or die( "Unable to select database");
    $query1 = "SELECT * FROM table1";
    $result1 = mysql_query($query1);

    mysql_select_db($database2) or die( "Unable to select database");
    $query2 = "SELECT * FROM table2";
    $result2 = mysql_query($query2);
    while($nt2=mysql_fetch_array($result2))
    {
    ...
    }

    These two databases are on the same server, have the same connection string, username, and password. When I get to the 'while($nt2=' line, I get a "supplied argument is not a valid MySQL result resource" error. It seems like this query is trying to pull from db1 instead of db2 as specified. I tried putting a mysql_select_db right before the query, but that didn't help. Any ideas?

  12. #12
    Join Date
    May 2009
    Posts
    766
    Quote Originally Posted by PlanetWebHost View Post
    I use muliple databases all the time, and you don't really need to switch between them, just open them with different resource id's, and specify the resource link identifer in your queries. ( I accually always write my queries that way, incase I need to connect to a different database later on in development )


    PHP Code:
    $db1  mysql_connect('localhost','myuserid','mypasswd');  
    $sel1 mysql_select_db('db01'); 
    $db2  mysql_connect('localhost','myuserid','mypasswd');  
    $sel2mysql_select_db('db02'); 

    $res1 mysql_query($query$db1);
    $res2 mysql_query($query$db2); 
    note: then you also need to use the resource link idenitifier in functions like, mysql_affected_rows($db1), mysql_insert_id($db1), mysql_error($db1), etc...
    Or, since the one user obviously has the right permissions on all three databases, you can just fully reference everything in one query...

    Code:
    SELECT * FROM db1.table
    UNION
    SELECT * FROM db2.table
    UNION 
    SELECT * FROM db3.table

  13. #13

    Almost, but not quite

    Ok, now I have this, based on the above example...

    $db1 = mysql_connect($connection,$username,$password) or die(mysql_error());
    $sel1 = mysql_select_db($database1);
    $db2 = mysql_connect($connection,$username,$password) or die(mysql_error());
    $sel2 = mysql_select_db($database2);

    $query1 = "SELECT * FROM yp_events ORDER BY event_title";
    $result1 = mysql_query($query1, $db1);
    $nt1=mysql_fetch_array($result1, $sel1);
    ^
    |
    This line produces a "supplied argument is not a valid MySQL result resource" error. I tried:

    $nt1=mysql_fetch_array($result1, $db1);
    and
    $nt1=mysql_fetch_array($result1);
    but all with the same results. What am I doing wrong?

    I need to do a while on $nt1, so I thought I could do while($nt1) instead of while($nt1=mysql_fetch_array($result1)), but I can't get the fetch array to work. Any ideas?

  14. #14

    * Solved

    Here's what I came up with from the advice:

    $connection="localhost";
    $username="user";
    $password="pass";
    $database1="db1";
    $database2="db2";

    $db1 = mysql_connect($connection,$username,$password) or die(mysql_error());
    $sel1 = mysql_select_db($database1, $db1);

    $query1 = "SELECT * FROM table1";
    $result1 = mysql_query($query1, $db1);

    $db2 = mysql_connect($connection,$username,$password) or die(mysql_error());
    $sel2 = mysql_select_db($database2, $db2);
    $query2 = "SELECT * FROM table2";
    $result2 = mysql_query($query2, $db2) or die(mysql_error());;
    while($nt2 = mysql_fetch_array($result2, $db2))
    {
    ...
    }

    This actually works. Thanks, guys!

  15. #15
    Hi Daltman, thanks for posting this code. I'm actually trying to do the same thing but i'm also using Jqeuery to display the information. Later on i'll post the code i have so far, but i was wondering if you were also using Jquery?

  16. #16
    I've never used jquery, and don't know much about it. Sorry.

  17. #17
    Join Date
    Nov 2009
    Location
    India
    Posts
    6
    Thanks for sharing..

Posting Permissions

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