Multiple MySQL database connection issues.

acidbox

I am trying to connect to two databases simultaniously, and I am having issues. I have looked at previous threads about connecting to multiple dbs and have examined php.net on the issue, however, I still have an issue.

It appears that no matter what I do, if I attempt to open two databases at once, it only uses the last one opened. I am using the following code to connect:

//connection

$dbase['conn1'] = mysql_connect($db_config['conn1_host'], $db_config['conn1_user'], $db_config['conn1_pass']);
mysql_select_db($db_config['conn1_dbas'], $dbase['conn1']) or die (mysql_error());

$dbase['conn2'] = mysql_connect($db_config['conn2_host'], $db_config['conn2_user'], $db_config['conn2_pass']);
mysql_select_db($db_config['conn2_dbas'], $dbase['conn2']) or die (mysql_error());


//sample queries

$mysql_query = mysql_query($sql, $dbase['conn1']);
$mysql_query = mysql_query($sql, $dbase['conn2']);

The end result is that on both of those queries, php tries to connect to connection 2 every time. Therefor, the first query returns an error.

I thought at first that perhaps you couldn't store 2 connections in the same variable as an array, so I tried to replace the $dbase with $db_conn1 and $db_conn2. Same result.

Does anyone know what I'm doing wrong?

DeltaRho2K

//connection 

$dbase1 = mysql_connect($db_config['conn1_host'], $db_config['conn1_user'], $db_config['conn1_pass']); 
mysql_select_db($db_config['conn1_dbas'], $dbase['conn1']) or die (mysql_error()); 

$dbase2 = mysql_connect($db_config['conn2_host'], $db_config['conn2_user'], $db_config['conn2_pass']); 
mysql_select_db($db_config['conn2_dbas'], $dbase['conn2']) or die (mysql_error()); 


//sample queries 

$mysql_query1 = mysql_query($sql, $dbase1); 
$mysql_query2 = mysql_query($sql, $dbase2);

It may be how you are defining the queries themselves... Try numbering the $mysql_querys... It looks like you are trying to store the results in the same variables. (Same thought process that prompted you to change the connection names.

acidbox

I should have been more clear. Those are just examples. They are in fact different names... =/

DeltaRho2K

Ah, I thought that was too simple ;-)

So I am looking at your code again, and wonder this: After the $dbase, you have ['conn1'] and ['conn2']... Is this right? Something doesn't seem right about it. The part inside the square brackets would indicate a variable array result wouldn't it? I have never defined DB connections like this, or used square brackets when defining variables like this. I have only used them when outputting the results of an array or query. Have you tried something like the way I rewrote the code in my previous post? (Square brackets removed) I did it on my computer, and was able to query two DB's just fine the way I wrote the code...

//connection 

$dbase1 = mysql_connect($db_config['conn1_host'], $db_config['conn1_user'], $db_config['conn1_pass']); 
mysql_select_db($db_config['conn1_dbas'], $dbase['conn1']) or die (mysql_error()); 

$dbase2 = mysql_connect($db_config['conn2_host'], $db_config['conn2_user'], $db_config['conn2_pass']); 
mysql_select_db($db_config['conn2_dbas'], $dbase['conn2']) or die (mysql_error()); 


//sample queries 

$mysql_query1 = mysql_query($sql, $dbase1); 
$mysql_query2 = mysql_query($sql, $dbase2);

acidbox

well, i figured out that no matter what I do, wether i use regular vars, an array, or whatever, php is only recognizing the last connection made to the db. I could do 2 or 3 connections, and the last one that is called is the only one that will work, even though I AM using the correct resource link for each seperate query.

This is strange, does anyone know whats up?

bad76

Hi,
the problem is that mysql_connect is like as an handle of file. If the system look that an handle to a file is already open increment reference to it and give same pointer. Mysql do the same. Don't open, by default, any new connection, but reply the old too.
To open a new link you have to append true as last parameter in your function:

$dbase2 = mysql_connect($db_config['conn2_host'], $db_config['conn2_user'], $db_config['conn2_pass'],true);

see you

DeltaRho2K

That's interesting. I reviewed my code, and realized the reason mine worked is because I had one connection defined near the beginning and closed the tion before I opened new connection...

acidbox

I got it working!

I found out there is a bug in mysql that if you use the same username/pw that it will assume the connection of only the last database you open.

So basically, i just changed the username and password and it worked.