How to Connect to the Remote MySQL Database using PHP

There is a need for some web projects for accessing multiple databases on a different server. Therefore, you have to connect the remote database from another server. For some safety measures, disable the remote access to the MySQL database server. You have to enable remote MySQL access to connect the MySQL database from the different servers.

Remote access will permit you to access the MySQL database from another server. This access is supportive when there is a need to connect multiple databases that are hosted on a different server. This sample script will explain to you how to connect to the remote MySQL database using PHP.

Let’s assume that the MySQL database is hosted on Server A, and you want to connect this database from Server B using PHP script. So, the remote database connection process would be the following.

1. Log in to the cPanel account of the web server, where the MySQL database is hosted (Server A).
2. Under the Databases section, click on Remote MySQL®.
3. Enter the IP address of the host server (Server B) from where the database will be accessed. Click on Add Host.
4. To connect with the MySQL database hosted in another server (Server A), use the following PHP code in Server A.

   <?php
   $dbServerName = "example.com";
   $dbUsername = "exdbuser";
   $dbPassword = "exdbpass";
   $dbName = "allsweb";
   
   // create connection
   $conn = new mysqli($dbServerName, $dbUsername, $dbPassword, $dbName);
   
   // check connection
   if ($conn->connect_error) {
       die("Connection failed: " . $conn->connect_error);
   }
   echo "Connected successfully";
   ?>

5. Now you will able to reach through the database tables of the remote database server. 

<?php
/*
 * get data from remote database table
 */
$sql = "SELECT id, name FROM users";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "id: " . $row["id"]. " - Name: " . $row["name"]. "<br>";
    }
} else {
    echo "0 results";
}
?>

The following example script will fetch the user’s data from the user’s table to the remote database.

Also, read our previous blog- How to Install LAMP (Linux, Apache, MySQL, PHP) on Ubuntu