Changing the connected DB

Question

I seem to go back and forth on this, sometimes it works for me, often it doesn't and I can't figure out how/why I can get it to work sometimes, and at other times it won't.

I have 5 databases, and need to grab data from each of them during a maintenance routine. For each of the databases, I have an external file which contains all the db connection code which I include in my page.

The includes look like this

$SGcon = mysql_connect("localhost","root","");

if (!$SGcon)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("sGopen", $SGcon);

For each database I provide a different varaible, so the next database will be

$PTcon = mysql_connect("localhost","root","");

if (!$PTcon)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("pTsecond", $PTcon);

Then when i call my query, I use

 
mysql_query($getQuery,  $PTcon);
mysql_query($secondQuery, $SGcon);

The problem I'm running into is that I'm not getting the correct database used for the query , it seems my script is using the databases which was added to the page last.

Is there a better way for me to switch the database connection to be used?

There is lots of back and forth between the databases as I'm comparing data from one db to data in another, so i'm trying to be efficient with this as well.

Thanks Pete

Answer 1

I think if you close the db connection in between each query you will solve the problem.

And if you're doing a lot of compares between databases, then I would just use 1 select statement to query all databases at once.

Answer 2

By default, if you pass the same arguments to mysql_connect as a previous call it'll return a cached connection if available, rather than creating a new one. So both queries are running against whichever database you last passed to mysql_select_db.

Passing new_link=true will avoid this behaviour, although database connections can be expensive so you should try to keep as few open as reasonably possible.

The detailed explanation for new_link:

If a second call is made to mysql_connect() with the same arguments, no new link will be established, but instead, the link identifier of the already opened link will be returned. The new_link parameter modifies this behavior and makes mysql_connect() always open a new link, even if mysql_connect() was called before with the same parameters. In SQL safe mode, this parameter is ignored.

Answer 3

Unless there is a specific reason, you want to keep db connections as low as possible. I would use one connection, and write a method to extend the functionality of the mysql_query() method. You could pass it the SQL string, and the table name then the function calls mysql_select_db($tableName) and re-uses that same connection to process the SQL.