PHP MySQL. How to connect two (several) different MySQL databases?

The article How to connect to the MySQL database server I presented to connect to a MySQL database, one database!
If you were using those scripts trying to connect to some MySQL databases, but unfortunately can not be!
Why?
Well, PHP is configured by default allows you to connect to only one MySQL database. There is no limit! It is simply the default setting.
So how to connect to two or even several MySQL databases at the same time?

PROBLEM:
How to connect the various MySQL databases (at the same time) and send them questions?

DESCRIPTION OF THE PROBLEM:
Would you like to make your PHP script, joined at the same time with different databases and MySQL queries directed to them.
In addition, you want to control – identify which queries are to be performed by the chosen combination – the selected database.

SOLUTION:
As has already been mentioned above, the default PHP configuration allows for simultaneous connection to a MySQL database.
With this configuration corresponds to a 4 argument mysql_connect(), this is an optional argument – because, in most cases unknown and unused ;-), defined as $new_link, defaults to false.

From the documentation:
mysql_connect (string server, string username, string $password, [bool $new_link=false], [int $client_flags=0]);
$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.

What is someone, RECOMMENDATIONS AND warning messages:
Simultaneous connection to multiple databases MySQL has many benefits. Thus, at the same time, you can manipulate the keel databases running only one PHP script. You can move data from one database to another, compare, make a backup of the selected tables, etc. You can manipulate the databases located on the same server – the host, but also located on different servers.
Simultaneous connection to keel databases in one script, and hence, to have several different connection identifiers, poses many problems. You have to remember that the link connection, which connects you with the database. Specific SQL query must be channeled to the proper base. Obviously you can have problems with this, especially when they have not yet cited, the connection identifier. This was possible because many functions that operate on the database does not require it (uses the default ID).

EXAMPLE 1:
Simple PHP code that creates direct connection to two MySQL databases

<?PHP
// Create first connection to the host named locahost1.
$con1 = @mysql_connect(‘locahost1′, ‘user’, ‘pass’, false);
if(!$con1) {
        echo “ERROR MySQL: Connect to Server locahost 1\n”;
        exit;
}
// Create a second connection to the host named locahost2.
$con2 = @mysql_connect(‘locahost2′, ‘user’, ‘pass’, true);
if(!$con2) {
        echo “ERROR MySQL: Connect to Server locahost 2\n”;
        exit;
}
// Print connection identifier $con1 and $con2
// Various IDs show two different combinations!
// The same identifiers, it is really a combination!
echo $con1.”\n”;
echo $con2.”\n”;
// Connect to the selected MySQL database – using the connection $con1.
@mysql_select_db(‘baza_mysql_1′, $con1);
// Connect to the selected MySQL database – using the connection $con2.
@mysql_select_db(‘baza_mysql_2′, $con2);
//[...]
// Close the connection to the MySQL database on the host locahost1 and locahost2 ($con1, $con2)
mysql_close($con1);
mysql_close($con2);
?>

NOTES:
1. Display the connection ID will help us determine whether in fact we are connected to different databases / hosts
2. Please pay special attention to the fourth argument of a call to mysql_connect()
- For the first call: mysql_connect(‘locahost1′, ‘user’, ‘pass’, false);
- For second and subsequent calls: mysql_connect(‘locahost2′, ‘user’, ‘pass’, true);
Using the “true” in the second function call creates a new connection!

EXAMPLE 2:
The following expanded function in PHP which can be used to connect to the database or MySQL databases.
Function:
- You can make a connection with several hosts MySQL,
- Connects us to the selected MySQL database – for a particular host,
- Sets the encoding selected in this case UTF-8 (call, client, results returned, etc.)
- Returns the appropriate message in case of failure (connection, database setup, setting the correct encoding)
- Returns the connection handle,
- Ideal for developer, we always know when something has gone wrong!

<?PHP
function connect_to_db($host, $user, $pass, $db, $link) {
        if(!($con = @mysql_connect($host, $user, $pass, $link))) {
                echo “ERROR MySQL: Connect to Server\n”;
                exit;
        }
        if(!mysql_select_db($db, $con)) {
                echo “ERROR MySQL: Connect to DataBase\n”;
                exit;
        }
        if(!mysql_query(“SET NAMES ‘utf8′”, $con)) {
                echo “ERROR MySQL: SET NAMES ‘utf8′\n”;
                exit;
        }
        if(!mysql_query(“SET CHARACTER SET ‘utf8′”, $con)) {
                echo “ERROR MySQL: SET CHARACTER SET ‘utf8′\n”;
                exit;
        }
        if(!mysql_query(“SET character_set_client = ‘utf8′”, $con)) {
                echo “ERROR MySQL: SET character_set_client = ‘utf8′\n”;
                exit;
        }
        if(!mysql_query(“SET character_set_results = ‘utf8′”, $con)) {
                echo “ERROR MySQL: SET character_set_results = ‘utf8′\n”;
                exit;
        }
        if(!mysql_query(“SET character_set_connection = ‘utf8′”, $con)) {
                echo “ERROR MySQL: SET character_set_connection = ‘utf8′\n”;
                exit;
        }
        mb_internal_encoding(“UTF-8″);
        mb_regex_encoding(“UTF-8″);
        return $con;
}
// Create first connection to the MySQL database on the host locahost1
$con1 = connect_to_db(‘locahost1′, ‘user’, ‘pass’, ‘db’, false);
// Create a second connection to the MySQL database on the host locahost2
$con2 = connect_to_db(‘locahost2′, ‘user’, ‘pass’, ‘db’, true);
// Create the third connection to the MySQL database on the host locahost3
$con3 = connect_to_db(‘locahost3′, ‘user’, ‘pass’, ‘db’, true);
// [...]
// Close connection to the database – optional
mysql_close($con1);
mysql_close($con2);
mysql_close($con3);
?>

This entry was posted in MySQL, PHP and tagged , , , , , , , , , , , , , . Bookmark the permalink.

One Response to PHP MySQL. How to connect two (several) different MySQL databases?

  1. Degrees says:

    Very good job !!
    Good week !!

Leave a Reply