Set active MySQL connection

TruckStuff

I've spent the last 4 hours searching these forums, google and php.net without finding a solution that works for me.

I have a script that requires two connections due to access rights on teh mysql db. I am able to succesfully connect as both users, but when I close one connection, the first connection does not become the active one. I would like for the first to become active again so that I don't have to specify a resource identifier in followup queries. I have tried using mysql_ping, and that does not work (all queries after the close/ping die):

$host = 'a.b.c.d';
$uname = 'blah';
$pw = 'blah';
$uname2 = 'blah2';
$pw2 = 'blah2';
$db = 'blah';

// Establish first connection
$db1 = mysql_connect ($host, $uname, $pw);
mysql_delect_db ($db);

// All queries work fine right now.
mysql_query ('insert into table1 set Field1="value"') or die (mysql_error());

// Establish second connection using new_link parameter to force new connection
$db2 = mysql_connect ($host, $uname2, $pw2, true);
mysql_delect_db ($db);

// I can query with either connection now just fine.
mysql_query ('insert into table1 set Field1="value"', $db1) or die (mysql_error());
mysql_query ('insert into table2 set Field2="value2"', $db2) or die (mysql_error());

// Close $db2 and ping $db1 to make sure it still there.  Both of these statements execute successfully
mysql_close ($db2) or die (mysql_error());
mysql_ping ($db1) or die (mysql_error());

// This statement fails:
mysql_query ('insert into table3 set Field3="value3"') or die (mysql_error());
// But this statement does not:
mysql_query ('insert into table3 set Field3="value3"', $db1) or die (mysql_error());

I want to be able to perform the last query without having to specify a resource identifier. How can I force $db1 to be the active connection after closing $db2 if mysql_ping doesn't do it? Thanks.

ksandom

// This statement fails: 
mysql_query ('insert into table3 set Field3="value3"') or die (mysql_error()); 
// But this statement does not: 
mysql_query ('insert into table3 set Field3="value3"', $db1) or die (mysql_error());

If the second statement works, where's your problem? You've specified the db reference that you want to use in the second statement while in the first statement you haven't. Thus the first is trying to use the default, which you say has now been closed.

If you want the first statement to success, just specify the db you want to use.

Am I missing the point? 🙂

TruckStuff

Am I missing the point?

Yes. 🙂 I don't want to have to specify the connection in each subsequent query. When the code you mentioned is executed, there is only once active mysql connection, but PHP doesn't identify it as the only active connection.

This is a large page and this code appears at the beginning of the page, so yes, I'm lazy. 😃 But that's why we are all here, isn't it? 😉

Weedpacket

Put

mysql_select_db ($db, $db2);

after closing $db1? Haven't tried that myself, just took it off the mysql_select_db manual page.

TruckStuff

Yeah, I tried that one too at one point yesterday after reading the same thing. It still didn't work unless I specified the DB connection in the query.

My work around right now is to perform the admin functions before the regular connection is opened so there is only one connection at a time. This is still a frustrating little "feature" of PHP. Seems like there alter to be a mysql_set_active_connection() function or something along those lines.