Pros and cons of connecting more than one database in single script

Question

Let's say user have two databases hosted on single host and I need to connect to both of them so that I can use any table anytime without adding connection code multiple times.

I have implemented this in CodeIgniter with adding authorization details of both databases in database.php file and to load required database with $this->load->database('dbname'); in script.

Now, for core PHP, we can do this like:

mysql_connect ('host','user','password','port','dbname'); // connection with one database.   

It was connected with my first database.

Now, I want to connect with second database:

1) I have not closed above connection and connected with second one with

mysql_connect ('host','user','password','port','dbname1');.

2) Would it be bad practice to do so ? Would it consume more objects ? Should we be required to close first one anyhow?

Answer 1

Q: What cons are there to connect with other database without closing previous database?

A: When you connect to a database server physically are assigning resources to interact with you, if two databases are on the same server you would unnecessarily using resources that could be used to address other connections or other activities. Therefore you would be right close connections that do not need to continue using.

Q: Is this a appropriate practice to do so ? What is the best way to do so without opening this connection in every script multiple times ? I want this to get done in core php only as I have already know this in codeigniter.

One way SESSIONS, but you can't store database conections in sessions. Read in PHP.net this Warning: "Some types of data can not be serialized thus stored in sessions. It includes resource variables or objects with circular references (i.e. objects which passes a reference to itself to another object)." MySQL connections are one such kind of resource.

You have to reconnect on each page run.

This is not as bad as it sounds if you can rely on connection pooling via mysql_pconnect(). When connecting, the function would first try to find a (persistent) link that's already open with the same host, username and password. If one is found, an identifier for it will be returned instead of opening a new connection. The connection to the SQL server will not be closed when the execution of the script ends. Instead, the link will remain open for future use (mysql_close() will not close links established by mysql_pconnect()).

Reference:

http://php.net/manual/en/function.mysql-pconnect.php

http://www.php.net/manual/en/intro.session.php

Can't pass mysqli connection in session in php

Answer 2

It is not neccessary to open 2 connection just to use tables from 2 databases on the same server. You just need to use the database.table notation. Doing that means that you can even join tables from different databases in the same query

SELECT t1.col1, t1.col2, t2.col2, t2.col2
FROM db1.table1 AS t1 JOIN db2.table1 AS t2 ON t1.col1 = t2.col3

So if you have connected to db1 initially you can use db2 tables and the same if you connected to db2 you can use db1 tables.