Topic awaiting preservation: Mysql multiple connections pointers (Page 1 of 1)

Hey all,

I'm using this login script on a site using DB1 and wanting to connect to another DB to pull seperate information (DB2). I've had no problems connecting to DB2 except if I try to call information from DB1 afterwards I get errors; I'm thinking becuase it's still trying to pull info from the DB2 connection. Even though I have closed the DB2 connection and never closed DB1. I've managed to fix the problem by re-declaring DB1

$database = new MySQLDB;

(found on database.php in the login script) before and after my DB2 connection. But, because I'm just barely dabbling with classes (OOP) I'm afraid I may be missing something right in front of my face, or what I have done is in fact incorrect. Anyways, your thoughts are appreciated.

edit: I have no idea where "pointers" came from in the title



(Edited by redroy on 05-18-2006 18:47)

Well, my first question would be - why are you storing information that will be used for one site in multiple databases?

Just seems like an approach that is begging for problems...

Yeah, I thought the same thing but the decision is not mine.

I deal with the pains of having to access multiple databases at work and it sucks... but it's a problem that was laid in place long before I got there so there's nothing I can do about it but work with them.

In looking at database.php I see that it's taking the connection parameters from hard coded constants. How are you opening the new connection?

- Butcher -

Same way... I'm using new constants defined on the same page and a new function:

function showGames()
{	
	$appDB = mysql_connect(APP_DB_SERVER, APP_DB_USER, APP_DB_PASS);
	mysql_select_db(APP_DB_NAME, $appDB) or die("Unable to select DATABASE!");

	$query = "SELECT * FROM " . TBL_GAMES . " ORDER BY 'id' DESC";
	$result = mysql_query($query, $appDB);
	$num = mysql_numrows($result);
	
	if ($num == 0)
	{
		echo '<div><b>There are no new games at this time</b></div>';
	}
	else
	{
		echo '<ul>';
		
		for ($i = 0; $i < $num; $i++)
		{
			$title = mysql_result($result, $i, "title");
			$description = mysql_result($result, $i, "description");
			$price = mysql_result($result, $i, "price");
		
			echo '<li><a href="index.php">' . $title . ' (' . $price . ')</a><br />' . $description . '</li>';
		}
		
		echo '</ul>';		
	}
	mysql_close($appDB);
}

In looking around for a proper answer to your problem I've learned something that might be the solution to your problem and certainly deals with a problem I was having and didn't know why.

As I said in my previous post I deal with a setup at work that has me sometimes accessing 2 - 4 different databases in the same script. I was having the same problem you were, the connections actually seemed to be getting confused. There were times that I would try and close a connection and I would get an error message that the connection identifier didn't exist even though I knew I had opened it and run queries with it. This caused me to rewite a lot of code so that I explicitly open each connection when I need it and close it as soon as I'm done with it so there was no problem.

I now see that PHP seems to have a problem with multiple connections opened with the same HOST, USER, PASS parameters. PHP only keeps one open connection to any database server so as soon as you open the second one the first becomes invalid. Still there is hope! As noted here as of PHP 4.2.0 a new_link parameter was added and if specified will force a second connection to be opened to the same host.

I hope this helps.

- Butcher -

Thanks for the help... I've decided to let them know that if they want to use my services we are using a single database. (works beautifully with a single DB btw). Thanks again for your time.