php5 + mysql + multiple database connections issue

CoderDan

comments should explain - getting an error and can't figure out why.

<?php
// assuming you have 2 databases with different users, and define the connection variables, this should run as is:

// default open database and run a query - I can't modify this, as it's used throughout a pretty complex site, and so it needs to stay the same.
mysql_connect( DB1_HOST, DB1_USERNAME, DB1_PASSWORD );
mysql_select_db( DB1_DATABASE );
$result = mysql_query( "SELECT COUNT( * ) FROM table_in_db1 " ) or die( mysql_error() );
$row = mysql_fetch_row( $result );
echo "$row[0] matches\n";
// the above works as expected, pretty standard stuff.

// this is what i need to add, the second db connection
// open a connection to the second db (different user/pw, also) - open it as a new connection, and store a pointer to it as $db2
$db2 = mysql_connect( DB2_HOST, DB2_USERNAME, DB2_PASSWORD, true );
// specifying the new handle on db select and query
mysql_select_db( DB2_DATABASE, $db2 );
$result = mysql_query( "SELECT COUNT( * ) FROM table_in_db2 ", $db2 ) or die( mysql_error() );
$row = mysql_fetch_row( $result );
echo "$row[0] matches\n";
// the above also works as expected. but now it gets odd:

// run the same query as initially was run. no specifiers to db2, should take the default connection
$result = mysql_query( "SELECT COUNT( * ) FROM table_in_db1 " ) or die( mysql_error() );
$row = mysql_fetch_row( $result );
echo "$row[0] matches\n";
// the above fails, this is ok, I guess the default db is now db2.
// my current working solution is just to open the original db connection again, but this is less than ideal - the new code is compartmentalized as a separate module, and I'd like it to know nothing about db1
// I figured the solution would be to close the new db:
mysql_close( $db2 ); // *** triggers the error: Warning: mysql_close(): supplied argument is not a valid MySQL-Link resource in scriptname.php

// this, I figured, would close db2, meaning that I could run a query on default db and have it referencing the original db1
// ie. i thought that then the following would work: (don't know if it should, due to the error on mysql_close)

// run the same query as initially was run. no specifiers to db2, should take the default connection
$result = mysql_query( "SELECT COUNT( * ) FROM table_in_db1 " ) or die( mysql_error() );
$row = mysql_fetch_row( $result );
echo "$row[0] matches\n";
?>

bradgrafelman

One problem I see is this:

// run the same query as initially was run. no specifiers to db2, should take the default connection

There is no "default connection" in a script; as per the manual for [man]mysql_query/man:

If the link identifier is not specified, the last link opened by mysql_connect() is assumed.

So, I hate to tell you this, but it sounds like you'll have to go through and fix up all of the old code so that it specifically references which link connection is to be used.

This is one (good) reason why PHP/MySQL programmers are suggested to use newer libraries such as MySQLi or PDO, both of which require the link parameter (it's no longer optional, as in the deprecated mysql library).

EDIT: Hrm, just saw this:

mysql_close( $db2 ); // *** triggers the error: Warning: mysql_close(): supplied argument is not a valid MySQL-Link resource in scriptname.php

That's... interesting; I wouldn't have expected that based on your test script.

Can you do a [man]var_dump/man on $db2? What does PHP say about it? Do you have error_reporting set to E_ALL?

CoderDan

I pretty much assumed as much, I was just hoping if the specific connection was closed, it'd find the last open connection - it still doesn't explain why I'm getting an error on the close attempt of the stored second connection, but I guess I'll go back to the reopening of the initial db link.

The site I'm working on is about 3,000 pages of php code, all up, not sure how many lines, and it's all pre-existing, I'm just a new guy working on it, and I'd prefer to rewrite the entire site to fixing what's there, it'd be quicker 🙂, but it's not my call, and the boss won't want me to spend that time on fixing previous code, thus I'll have to take the quick 'n' dirty route 🙁