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

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.

Answer 2

Have you tried this?

$mysqli1 = new mysqli("example.com", "user", "password", "database1");

$mysqli2 = new mysqli("example.com", "user", "password", "database2");

Answer 3

Why do you need two connections? The pros/advantages of two databases are actually primarily performance issues. But if you are on the same machine actually the only advantage you have, would be a cleaner separation. So it would be better to use one DB with two different prefixes for the table. So you seperate the diffent data by prefix and not by DB.

Answer 4

You can do this by following object oriented approach

First of all create connection with two databases:

$Db1 = new mysqli('localhost','root','','database1'); // this is object of database 1
$Db2 = new mysqli('localhost','root','','database2'); // this is object of database 2

$query1 = 'select * from `table_name_of_database1`';  // Query to be run on DB1
$query2 = 'select * from `table_name_of_database2`';   // Query to be run on DB2

$result1 = $Db1->query($query1); // Executing query on database1 by using $Db1
$result2 = $Db2->query($query2); // Executing query on database2 by using $Db2

echo "<pre>";

/* Print result of $query1 */

if ($result1->num_rows > 0) {
    while($row = $result1->fetch_assoc()) {
        print_r($row);
    }
} else {
    echo "0 results";
}


/*========================================================*/


/* Print result of $query2 */

if ($result2->num_rows > 0) {
    while($row = $result2->fetch_assoc()) {
         print_r($row);
    }
} else {
    echo "0 results";
}

Conclusion: When you want to use database1 use $Db1 object and if you want to use database2 then use $DB2.

Answer 5

I don't think how to connect to 2 DBs simultaneously is the problem, as you have successfully done it ( or know how to do it ). I can gather that from your question. So I won't show how to do this. See other answers if you need to.

But to address your issues directly:

1. Would it be bad practice to do so ? Generally, you should avoid 2 simultaneous DB connection handles as much as possible. If you only need to get data from one DB and use them to do something on the other, your best bet is to put the data from DB1 into appropriate PHP variables, close the connection; then make the second connection. That would be cheaper than keeping 2 DB connections open at the same time. However, if you are doing something like INSERT INTO db1.table SELECT FROM db2.table AND ALSO NEED TO COMMIT OR ROLLBACK depending on success or failure of some queries, then AFAIK, you need to keep both connections open until your processes are over. You see, there always trade-offs. So you decide based on the need of your application and bear the cost.

As a practical example of this scenario, I once worked on a project where I needed to SELECT a table1, INSERT INTO a table2, if the INSERT succeeds, I delete all the rows from table1, if the DELETE fails, I rollback the INSERT operation because the data cannot live in the two tables at the same time.

Of course, my own case involved only one DB, so no need of a second connection. But assuming the two tables were on different DBs, then that may be similar to your situation.

2. Would it consume more objects ? No other objects other than the ones pointed out in 1 above, namely the DB connection handles according to your question.

3. Should we compulsory require to close first one anyhow ? Once again, depending on your application needs.

Answer 6

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 7

Instead of mysql_connect use mysqli_connect.

mysqli is provide a functionality for connect multiple database at a time.

Answer 8

1) Is it possible to connect with more than one database in one script ?

Yes we can create multiple MySQL link identifier in a same script.

2) It should be not like to close one connection with mysql_close and open new one,rather both connection should open at a time and user can use any table from any of the database ?

Use Persistent Database Connections like mysql_pconnect

3) If it is possible,what can be disadvantage of this ? Will there create two object and this will going to create issue ?

I don't think so it create any issue other than increasing some load on server.

Answer 9

You can use like this

$db1 = mysql_connect($hostname, $username, $password); 
$db2 = mysql_connect($hostname, $username, $password, true); 

mysql_select_db('abc', $db1);
mysql_select_db('def', $db2);

For Database 1

mysql_query('select * from table1', $db1);

For Database 2

mysql_query('select * from table2', $db2);

Answer 10

The best way to use multiple databases is to use PDO functions

EXAMPLE

// database cobfigurations
$config= array(
    // first database
    array(
        'type'=>'mysql',                    // DB type
        'host'=>'localhost',                // DB host
        'dbname'=>'database1',      // DB name
        'user'=>'root',                 // DB username
        'pass'=>'12345',                // DB password
    ),
    // second database
    array(
        'type'=>'mysql',                    // DB type
        'host'=>'localhost',                // DB host
        'dbname'=>'database2',      // DB name
        'user'=>'root',                 // DB username
        'pass'=>'987654',               // DB password
    ),
);
// database connections
$mysql=array();
foreach($config as $con)
{
    $con=(object)$con;
    $start= new PDO($con->type.':host='.$con->host.';dbname='.$con->dbname.'', $con->user, $con->pass, array(
            // pdo setup
            PDO::ATTR_PERSISTENT            => FALSE,
            PDO::ATTR_DEFAULT_FETCH_MODE    => PDO::FETCH_OBJ,
            PDO::ATTR_ERRMODE               => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND    => 'SET NAMES UTF8'
    ));

    if ($start && !empty($start) && !is_resource($start))
        $mysql[]=$start;    // connection is OK prepare objects
    else
        $mysql[]=false; // connection is NOT OK, return false
}

/**********************
 ****  HOW TO USE ****
**********************/ 

// fetch data from database 1
$data1 = $mysql[0]->query("SELECT id, title, text FROM content1")->fetchAll();
if(count($data1)>0)
{
    foreach($data1 as $i=>$result)
    {
        echo $result->id.' '.$result->title.' '.$result->text.'<br>'
    }
}

// fetch data from database 2
$data2 = $mysql[1]->query("SELECT id, title, text FROM content2")->fetchAll();
if(count($data2)>0)
{
    foreach($data2 as $i=>$result)
    {
        echo $result->id.' '.$result->title.' '.$result->text.'<br>'
    }
}

If you not use PDO before, please read this short tutorial:

http://www.mysqltutorial.org/php-querying-data-from-mysql-table/

Is practicly same like mysql and mysqli connections but is more advanced, fast and secure.

Read this documentations: http://php.net/manual/en/book.pdo.php

And you can add more then 2 databases

Answer 11

Use PDO supported by php 5 version instead mysql connect

Answer 12

Here is a simple class that selects the required database automatically when needed.

class Database 
{
    private $host   = 'host';
    private $user   = 'root';
    private $pass   = 'pass';
    private $dbname = '';

    private $mysqli = null;

    function __construct() 
    {
        // dbname is not defined in constructor
        $this->mysqli = new mysqli( $this->host, $this->user, $this->pass );    
    }

    function __get( $dbname ) 
    {
        // if dbname is different, and select_db() is succesfull, save current dbname
        if ( $this->dbname !== $dbname && $this->mysqli->select_db( $dbname ) ) {
            $this->dbname = $dbname;
        }

        // return connection
        return $this->mysqli;
    }
}


// examples
$db = new Database();

$result = $db->db1->query( "SELECT DATABASE()" );
print_r( $result->fetch_row() );

$result = $db->db2->query( "SELECT DATABASE()" );
print_r( $result->fetch_row() );

$result = $db->{'dbname with spaces'}->query( "SELECT DATABASE()" );
print_r( $result->fetch_row() );

Answer 13

$con1 = mysql_connect($hostname, $username, $password); 
$con2 = mysql_connect($hostname, $username, $password, true); 

mysql_select_db('database1', $con1);
mysql_select_db('database2', $con2);

Then to query database 1 pass the first link identifier:

mysql_query('select * from tablename', $con1);

and for database 2 pass the second:

mysql_query('select * from tablename', $con2);

Answer 14

if mysql's user have permission to two database , you can join two table from two database etc:

SELECT database1.table.title title1,database2.table.title title2 
FROM database1.table
INNER JOIN database2.table 
ON (database1.table.id=database2.table.id)