Efficient Way to Connect to 10 Databases

[HoboTraveler]

Hi All,

The following is how I connect to 10 MySQL databases on PHP5.

There are times when the "Could Not Connect" error appears. The error appears even though MySQL is up. There is no load on the server.

Is this the best way to connect to multiple databases?

Why does the "Could Not Connect" error appear sometimes?

$db1 = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect 1");
mysql_query('SET NAMES utf8');
mysql_select_db(1DB_DB, $db1);

$db2 = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect 2");
mysql_query('SET NAMES utf8');
mysql_select_db(2DB_DB, $db2);

$db3 = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect 3");
mysql_select_db(3DB_DB, $db3);

$db4 = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect 4");
mysql_query('SET NAMES utf8');
mysql_select_db(4DB_DB, $db4);

$db5 = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect 5");
mysql_select_db(5DB_DB, $db5);

$db6 = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect 6");
mysql_select_db(6DB_DB, $db6);

$db7 = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect 7");
mysql_select_db(7DB_DB, $db7);

$db8 = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect 8");
mysql_query('SET NAMES utf8');
mysql_select_db(8DB_DB, $db8);

$db9 = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect 9");
mysql_select_db(9DB_DB, $db9);

// External website
$db10 = mysql_connect (ext_DB_HOST, ext_DB_USER, ext_DB_PASS, TRUE);
mysql_query('SET NAMES utf8');
mysql_select_db(ext_10DB_DB, $db10);

[brotherhood of LAN]

You could put the connection variable/resources into an array to reduce the # of code lines.

Perhaps the timeout is due to the number of connections on a particular database?

[HoboTraveler]

*bump*

Is there a way to reduce timeouts? or expire them? How can the connect speed be improved?

Thanks.

[eelixduppy]

You shouldn't be establishing 10 different links to MySQL at the same time. You should be able to get away with just one call to the mysql_connect function. Which means that you call this function once before you need MySQL and then before each query, if need be, you can change the database.

[henry0]

eelixduppy is correct he described the way I do it, although I use a few Dbs but not 10.

[HoboTraveler]

I have multiple SELECT SQL statements on a single script like the ones listed below:

Does this mean I have to call the mysql_connect before *every* SELECT statement or do I call the mysql_connect at the start of the script?

Btw, if I did call mysql_connect before and after every SELECT statement, would'nt opening and closing mysql_connect() multiple times create load?

Do I need to close the MySQL connection after the SELECT statement is complete? or is okay to keep that open?

$select_records1 = select_records1("WHERE id = '?';", $id);
$name = $select_records1['name'];
$email = $select_records1['email'];

$select_records2 = select_records2("WHERE id = '?';", $id);
$name = $select_records2['name'];
$email = $select_records2['email'];

$select_records3 = select_records3("WHERE id = '?';", $id);
$name = $select_records3['name'];
$email = $select_records3['email'];

Thanks

[henry0]

Once you are connected to a specific DB ... you are connected.
So no needs for multiple conn

As far as mysql_close() goes
Using mysql_close() isn't usually necessary, as non-persistent open links are automatically closed at the end of the script's execution [php.net]

[eelixduppy]

As far as structure, it should look something along these lines:

#connect to db
mysql_connect(..);
#
#select db 1 and run query 1
mysql_select_db(..);
$r1 = mysql_query(..);
#
#select db 2 and run query 2
mysql_select_db(..);
$r2 = mysql_query(..);
#
#select db 3 and run query 3
mysql_select_db(..);
$r3 = mysql_query(..);
#
#etc ...
#close connection to MySQL
mysql_close(..);

[HoboTraveler]

Hi All,

I changed my script so that mysql_connect() is called just once.

However, the problem is that mysql_connect() does not create persistent connections to multiple dbs, (although I've specified the TRUE parameter in mysql_connect)

Is there a way to make these connections persistent across multiple dbs?

// default db connection
$db = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect");
mysql_query('SET NAMES utf8');

// db3
mysql_select_db(3DB_DB, $db);

// db4
mysql_query('SET NAMES utf8');
mysql_select_db(4DB_DB, $db);

// db5
mysql_select_db(5DB_DB, $db);

// db6
mysql_select_db(6DB_DB, $db);

[Mahabub]

Dear HoboTraveler,

Its not good idea to conenct 10 database at a time. cause it gives you mysql error. Even its best practice to make mysql connection when its need and must close it after processing the db records otherwise for a heavy traffic site you might get mysql connection problem.

You may try the below..........

function GetMyConnection($host,$dbuser,$dbpass,$db)
{
global $g_link;
if( $g_link )
return $g_link;
$g_link = mysql_connect( $host, $dbuser, $dbpass) or die('Could not connect to mysql server.' );
mysql_select_db($db, $g_link) or die('Could not select database.');
return $g_link;
}

function CleanUpDB()
{
global $g_link;
if( $g_link != false )
mysql_close($g_link);
$g_link = false;
}

$sql = "SELECT * FROM TABLE";
$sqlres = mysql_query($sql,GetMyConnection("Specify the function paramtters based on which db you need"));

// do the need full here for db processing

CleanUpDB(); // close the db connection

Thanks
Mahabub

[Habtom]

Hobo, mysql_pconnect is for persistent connections.

[Mahabub]

Dear HoboTraveler,

Please follow the below links
[php.net...]

You'll get useful information.

Also Thanks to Habtom cause really this function is nice(mysql_pconnect) and I didnt see it before.

Thanks
Mahabub

[HoboTraveler]

Guys,

mysql_pconnect does not work.

It appears that the MySQL resource link gets overwritten.

The error that appears is:
Invalid query: Table '6DB_DB.3DB_DB' doesn't exist

This error is the reason why I was calling the mysql_connect() ten times.

Is there a way to preserve the MySQL resource link?

The following code is in an includes.php and includes.php is called all over the website.

// default db connection
$db = mysql_connect (DB_HOST, DB_USER, DB_PASS, TRUE) or die("Could not connect");
mysql_query('SET NAMES utf8');

// db3
mysql_select_db(3DB_DB, $db);

// db4
mysql_query('SET NAMES utf8');
mysql_select_db(4DB_DB, $db);

// db5
mysql_select_db(5DB_DB, $db);

// db6
mysql_select_db(6DB_DB, $db);

[milocold]

Hi,

I would research using a singleton design pattern on a database class. I'm pretty sure that will allow for only one connection to exist.

- M. Cold

[vincevincevince]

It's a little-known fact that you can prefix your field and table names with a database name and avoid selecting the database altogether:

mysql_query("SELECT `age` FROM `database1`.`members` WHERE `id`=4");
mysql_query("SELECT `age` FROM `database2`.`members` WHERE `id`=3");

The prefixing of a table name with `databasename`. does the 'select db' part automatically.