Connect two databases at the same time, different servers

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
flywheel
Forum Newbie
Posts: 4
Joined: Fri Dec 26, 2008 6:16 pm

Connect two databases at the same time, different servers

Post by flywheel »

I got a super tricky problem!!!

In PHP, how can you have 2 active connections to 2 databases that are on 2 different servers at the same time inside a while loop?????

I've tried this already:

$db1 = mysql_pconnect($hostname1, $username1, $password1) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_1, $db1);


$db2 = mysql_pconnect($hostname2, $username2, $password2) or trigger_error(mysql_error(),E_USER_ERROR);
mysql_select_db($database_2, $db2);

...and then I do my thing inside a while loop, and it only connects to one database.

Any ideas????

thanks,
Ali
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Connect two databases at the same time, different servers

Post by John Cartwright »

I'm not exactly sure what you mean. Generally you do not want to continuously open connections in a loop.

Also, is it usually not a good idea to use mysql_pconnect(), as more often than not it does more damage than good!

Post all the relevant code please.
flywheel
Forum Newbie
Posts: 4
Joined: Fri Dec 26, 2008 6:16 pm

Re: Connect two databases at the same time, different servers

Post by flywheel »

Here is my code (I really appreciate you looking at this):

Code: Select all

 
    //login to database1    
    $hostname_dw_db = "***";
    $database_dw_db = "***";
    $username_dw_db = "***";
    $password_dw_db = "***";
    $dw_db = mysql_pconnect($hostname_dw_db, $username_dw_db, $password_dw_db) or trigger_error(mysql_error(),E_USER_ERROR); 
    mysql_select_db($database_dw_db, $dw_db);
 
    //login to database2    
    $hostname_heart_db = "***";
    $database_heart_db = "***";
    $username_heart_db = "***";
    $password_heart_db = "***";
    $heart_db = mysql_pconnect($hostname_heart_db, $username_heart_db, $password_heart_db) or trigger_error(mysql_error(),E_USER_ERROR); 
    mysql_select_db($database_heart_db, $heart_db);
 
$sql = "select * from lead_stats order by id asc limit 5;";
$result = mysql_query($sql,$dw_db);
 
while ($row = mysql_fetch_assoc($result)) {
 
    $lead_id = $row["lead_id"];
 
    $sql_1 = "select * from buysell order by buysell_id asc limit 5;";
    $result_1 = mysql_query($sql_1,$heart_db);
    
    while ($row_1 = mysql_fetch_assoc($result_1)) {
    
        $bstype = $row_1["bstype"];
    
    echo "From Heart: ". $bstype;
    
    }
 
echo "From DW: ". $lead_id;
 
}

thanks
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Connect two databases at the same time, different servers

Post by John Cartwright »

I don't see anything technically wrong.

What is your current output versus expected output? Errors?

Try adding

Code: Select all

or die(mysql_error());
to all relevant mysql commands. Some errors might come to light.

Also post all code in the appropriate

Code: Select all

tags, and be sure to remove your database credentials when posting in a [b]public [/b]forum (I've removed it for you).
flywheel
Forum Newbie
Posts: 4
Joined: Fri Dec 26, 2008 6:16 pm

Re: Connect two databases at the same time, different servers

Post by flywheel »

There are no error issues....the code works, it's just that it won't pull data from the 2nd DB (it says "Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource ")...my feeling is that you can't connect to 2 different DB that are on different servers at the same time in PHP
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Connect two databases at the same time, different servers

Post by John Cartwright »

Did you try what I asked :? ? Adding the mysql error reporting will tell you why your query failed, and thus why you are passing an invalid resource to mysql_fetch_assoc()
flywheel
Forum Newbie
Posts: 4
Joined: Fri Dec 26, 2008 6:16 pm

Re: Connect two databases at the same time, different servers

Post by flywheel »

Jcart,,,can u read english...i said there are no errors in the code....its just that I dont think php can connect to two databases on two different servers....i can translate for you in any language if you like
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Connect two databases at the same time, different servers

Post by John Cartwright »

flywheel wrote:Jcart,,,can u read english...i said there are no errors in the code....its just that I dont think php can connect to two databases on two different servers....i can translate for you in any language if you like
I don't appreciate being spoken to like this. Don't expect my help or the help of others if this is how you speak to people volunteering their time and effort to help you. Consider this my last attempt to help you.

Code: Select all

 
its just that I dont think php can connect to two databases on two different servers
Yes it can.

If you added the mysql_error() reporting like I suggested you would see why the query is failing. I never said it was an issue with the code. Invalid resource means the query FAILED, thus an invalid query.

My goodness.
Post Reply