Php more than one mysql connection

How to Connect to Multiple MySQL Servers with PHP

by Alex
I have written a super-fast beginner guide to Object-Oriented PHP. Click here to get it free.

Sometimes you want to work with two or more SQL servers in the same PHP script.
For example, you may want to copy data from one SQL server to another, or compare the data among the two.

You may also want to keep two separate connections to the same SQL server for security purposes.
In this case, the idea is to use two SQL accounts with different privileges to minimize the risk of injection attacks.
(I explain exactly how this works in the “Database permissions” chapter of my Security course).

Fortunately, PHP lets you keep multiple MySQL connections open at the same time.
Let’s see how you can do that with both MySQLi and with PDO.

How to connect to multiple MySQL servers with MySQLi

You can connect to a MySQL server with MySQLi like this:

$dbHost1 = '192.168.0.1'; $dbUser1 = 'user_1'; $dbPass1 = 'password_1'; $mysql1 = mysqli_connect($dbHost1, $dbUser1, $dbPass1); mysqli_select_db($mysql1, 'my_db_1'); 

(Of course, you should use more secure passwords than “password_1”. Here’s how to generate secure passwords).

If you want to connect to a different MySQL server without closing the first connection, you can simply create a new MySQLi connection resource variable with the parameters of the new database, without affecting the other one.

/* First connection */ $dbHost1 = '192.168.0.1'; $dbUser1 = 'user_1'; $dbPass1 = 'password_1'; $mysql1 = mysqli_connect($dbHost1, $dbUser1, $dbPass1); mysqli_select_db($mysql1, 'my_db_1'); /* Second connection */ $dbHost2 = '192.168.0.2'; $dbUser2 = 'user_2'; $dbPass2 = 'password_2'; $mysql2 = mysqli_connect($dbHost2, $dbUser2, $dbPass2); mysqli_select_db($mysql2, 'my_db_2'); 

You can then use both connections at the same time:

$query = 'SELECT * FROM users'; /* Read users from first server */ $result = mysqli_query($mysqli1, $query); $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); echo 'Users on server 1:'; foreach ($rows as $row) < echo $row['user_name'] . '
'; > /* Read users from second server */ $result = mysqli_query($mysqli2, $query); $rows = mysqli_fetch_all($result, MYSQLI_ASSOC); echo 'Users on server 2:'; foreach ($rows as $row) < echo $row['user_name'] . '
'; > 

This example has two connections only, but you can create as many connections as you want.

Here’s how to connect to two SQL server using MySQLi’s OOP syntax:

/* First connection */ $dbHost1 = '192.168.0.1'; $dbUser1 = 'user_1'; $dbPass1 = 'password_1'; $mysql1 = new mysqli($dbHost1, $dbUser1, $dbPass1, 'my_db_1'); /* Second connection */ $dbHost2 = '192.168.0.2'; $dbUser2 = 'user_2'; $dbPass2 = 'password_2'; $mysql2 = new mysqli($dbHost2, $dbUser2, $dbPass2, 'my_db_2'); /* Run a query on first server */ $mysql1->query('DELETE FROM users WHERE Run a query on second server */ $mysql2->query('DELETE FROM users WHERE >

P.S. If you need help learning object-oriented PHP, be sure to grab my free PHP OOP guide to learn the basics super-quickly.

How to connect to multiple MySQL servers with PDO

You can create multiple connections to different MySQL servers with PDO too.

The same concept applies: just use a different resource variable for each connection.

/* First connection */ $dbHost1 = '192.168.0.1'; $dbUser1 = 'user_1'; $dbPass1 = 'password_1'; $mysql1 = new PDO('mysql:host=' . $dbHost1 . ';dbname=my_db_1', $dbUser1, $dbPass1); /* Second connection */ $dbHost2 = '192.168.0.2'; $dbUser2 = 'user_2'; $dbPass2 = 'password_2'; $mysql2 = new PDO('mysql:host=' . $dbHost2 . ';dbname=my_db_2', $dbUser2, $dbPass2); /* Run a query on first server */ $mysql1->exec('DELETE FROM users WHERE Run a query on second server */ $mysql2->exec('DELETE FROM users WHERE >

I hope you enjoyed this little tutorial.
To learn more about PHP and MySQL: How to use PHP with MySQL: the complete tutorial

Leave a comment below if you have any questions.

Источник

Connect to multiple MySQL databases with PHP

Within the project sometimes requires using multiple MySQL databases. It may be the existing database from another project or the new one.

To handle this with PHP requires to create separate connections for each database and use the connection accordingly while manipulating data in MySQL database.

Contents

1. Configuration

Create a config.php for database configuration.

Here, I am creating a connection with two databases – tutorial_db1 and tutorial_db2 .

Completed Code

$conn_2 = mysqli_connect($host, $user, $password,$dbname2); // Check connection if (!$conn_2)

2. Manipulating Records

From the first database select all records from the posts table and insert a record in the users table in another database.

Completed Code

 // Insert records in database 2 $insert_query = "insert into users(fname,lname,email) values('Yogesh','Singh','yogesh@makitweb.com')"; mysqli_query($conn_2,$insert_query);

3. Conclusion

If you want to use multiple MySQL databases in your PHP project then you need to create a separate connection variable for your databases and use variables according to the database on which you are performing an action.

If you found this tutorial helpful then don’t forget to share.

Источник

Connect to Multiple Databases with PHP MySQLi and PDO

Hi! In this tutorial, we’ll see how to connect to multiple databases using PHP’s MySQLi and PDO (PHP Data Object) library. At times you may want to work with multiple mysql databases in the same context. Therefore, you need to connect to two or more databases in the same mysql instance and fetch data from them simultaneously. The old ‘MySQL’ extension has been deprecated since PHP5.5, so I would recommend you to use ‘MySQLi’ or even better ‘PDO’, which provides an additional layer of security and supports multiple databases such as MySQL, MSSQL Server, PostgreSQL, Oracle and much more.

The databases can be on the same server as php script or on different server. No matter what, connecting php to remote mysql server is similar to the one on the same server, except that you must provide permission to access the remote database.

Connecting Multiple Databases with PHP MySQLi:

Establishing connection to multiple databases using mysqli api is easier than doing it with pdo. All you need to do is open a single connection to the server, and switch between different databases on go with the mysqli_select_db() method.

Consider this scenario, we have two databases, one is ‘mydatabase_1’ with a table ‘mytable_1’ and another is ‘mydatabase_2’ with a table ‘mytable_2’. Now we will connect and fetch data from these two databases with in the same php script.

Step-1) Open the Mysql Connection

Use the mysqli_connect() method to open connection to database server without specifying the name of the database.

Step-2) Select and Retrieve Records from the First Database

Next, select the database ‘mydatabase_1’ with the help of mysqli_select_db() method and display records from it as usual.

Step-3) Select and Retrieve Records from the Second Database

Now switch over to the second database and get records from it in the same way.

Step-4) Closing the Connection

When you have finished, you must close the mysql connection in this way.

Connecting Multiple Databases with PHP PDO:

With PDO, things work little differently. You can’t use the single connection to work with multiple databases here. Because, PDO requires that you provide the name of the database when connecting to the database server. It uses a data source name (DSN) and requires you to select the database via the constructor method.

Step-1) Connect First Database with PDO

We must create different pdo objects for different database connections and include the script inside the try<> catch<> block. It also supports prepared statements with named parameters, thus reducing the possibility of sql injection.

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $pdo1->prepare('select id, name, email from mytable_1'); $stmt->execute(); foreach ($stmt->fetchAll() as $row) < echo $row['id']. ' - ' . $row['name']. ' - ' . $row['email']. '
'; > > catch(PDOException $e) < die('Error ' . $e->getMessage()); > ?>

Step-2) Connect the Second Database

setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $pdo2->prepare('select id, name, designation from mytable_2'); $stmt->execute(); foreach ($stmt->fetchAll() as $row) < echo $row['id']. ' - ' . $row['name']. ' - ' . $row['designation']. '
'; > > catch(PDOException $e) < die('Error ' . $e->getMessage()); > ?>

Step-3) Close Connection

While closing the connection, you must close all the opened connection. Since we have created two pdo objects, you have to set both as null .

Although working with pdo seems little complex than mysqli, it is very beneficial to go with it since you can switch over to a different database system in the future with minimal changes.

Likewise you can connect to multiple databases in php using mysqli and pdo extension. Hope this is useful to you. Please share the post on your social circle if you like it. Good day:)

Источник

How to Connect to Multiple MySQL Databases on a Single Web Page In PHP?

New here? Like SchoolsOfWeb on Facebook to stay up to date with new posts.

Problem:

You want to use more than one database on a single PHP script. You’re looking for a way to do this.

Solution:

In this tutorial, using three PHP APIs (API – Application Programming Interface), we’ll connect two different MySQL databases on a single PHP page. The three APIs are-

An API is a set of instructions and standards used to communicate with a software.
APIs can be procedural or object-oriented. In the above APIs, the first one “MySQL Extension” is a procedural API and the next two are object oriented APIs. To use a procedural API, just call its function. On the other hand, to use an object-oriented API, first instantiate the class and then call the method on the object. You need to have knowledge on object oriented programming to understand the usage of object oriented APIs.

Here are the settings of our two sample databases –

Host: localhost
Username: root
Password:
Database 1: db_students
Database 2: db_employees

In the following, we’ll use each API to connect with the above two databases.

1. Using PHP’s MySQL Extension

The following code opens two MySQL server connections ($link1 and $link2) and then each connection will select one database to use-

else < mysql_select_db($database1, $link1); >$link2 = mysql_connect($host, $user, $password, TRUE); if(!$link2) < die(“Not connected: ”. mysql_error()); >else < mysql_select_db($database2, $link2); >?>

Line 7	mysql_connect function establishes a connection and returns that connection link identifier($link1).
Line 11	Using the connection link ($link1) that we created on line 7, we select the database db_student.
Line 14	Here, another connection is established and the connection link($link2) is returned. As we’re trying to establish a new connection with the same mysql_connect() parameters, we used TRUE in the fourth parameters. If we don’t use the TRUE, the mysql_connect() function returns the previous connection link.
Line 18	Using the new connection link ($link2), we select another database db_employee.

Use the different connection links ($link1 and $link2) above to run queries from different databases.

mysql_connect() is deprecated from PHP 5.5.0. So, it is recommended to use either PHP’s mysqli Extension or PHP Data Objects (PDO)

2. Using PHP’s mysqli Extension

The following code instantiates two instances of mysqli ($dbh1 and $dbh2), each instance uses one database –

connect_errno > 0)< die('Unable to connect to database' . $dbh1->connect_error); >else < echo "Database db_student is connected."; >$dbh2 = new mysqli($host, $user, $password, $database2); if($dbh2->connect_errno > 0)< die('Unable to connect to database' . $dbh2->connect_error); >else < echo "Database db_employee is connected."; >?>

Line 7	We instantiate a new instance($dbh1) of MySQLi. Here, we connect with the database db_students.
Line 14	We instantiate another instance($dbh2) of MySQLi. Here, we connect with the database db_employees.

3. Using PHP Data Objects (PDO)

The following code instantiates two instances of PDO ($dbh1 and $dbh2). First instance uses the database “db_students” and the second one uses “db_employees”-

 catch(PDOException $e) < die('Could not connect to the database:' . $e); >try < $dbh2 = new PDO("mysql:host=$host;dbname=$database2", $user, $password); >catch(PDOException $e) < die('Could not connect to the database:' . $e); >?>

Line 9	We instantiate a new instance($dbh1) of PDO. Here, we connect with the database db_students.
Line 15	We instantiate another new instance($dbh2) of MySQLi. Here, we connect with the database db_employees.

Courses

Источник