Hi! Let's see how to connect to remote mysql database using php script. Usually your web application and database will be hosted on the same web server. But this is not always the case. Some Applications could be resource hog, in which case, the database will be hosted on server separate from the application. Let's say you have a PHP script on server S1 and MySQL DB on Server S2 and you want to remotely connect the script from S1 to the DB on S2. So, how could you do it?
When your Application server and Database server are different, you must establish remote connection to db. To do this, you need remote access enabled on the server.
Connecting Remote MySQL Database/Server with PHP:
For security reasons, remote access to the MySQL database is disabled by default. In order to connect the MySQL database from a different server, you need to enable remote access to MySQL server.
That is, you must allow the connection within cPanel for the IP address from which you connect. First get the IP address of the host and follow the steps below.
- Login to the cPanel account where the MySQL database is hosted.
- Go to the section 'Databases' > 'Remote MySQL®'.
- Provide the IP address of the server where you have your php script and click on 'Add Host'.
- Done! You have successfully enabled remote access on the MySQL database.
Once you have the remote access enabled, you can connect to the database from php as usual.
In PHP, you can connect to MySQL DB using either MySQLi api or PDO. MySQli supports both procedural and object oriented programming approach (OOP), and we will see how to connect using all of the three methods below.
1. Connect to MySQL using MySQLi (OOP Method)
Here is the php script to connect to the remote mysql database using PHP OOP method. You have to provide the host name, username and password of the server where the db is hosted.
<?php
$hostname = 'localhost';
$username = 'username';
$password = 'password';
$database = 'mydemo';
$con = new mysqli($hostname, $username, $password, $database);
if($con->connect_errno){
die('Error ' . $this->con->connect_error);
}
echo 'Connected successfully!';
?>
2. MySQLi Procedural Method
<?php
$hostname = 'localhost';
$username = 'username';
$password = 'password';
$database = 'mydemo';
$con = mysqli_connect($hostname, $username, $password, $database);
if(!$con){
die('Error ' . mysqli_connect_error());
}
echo 'Connected successfully!';
?>
3. PHP MySQL Connection using PDO
One of the main advantages of working with PDO is that it is compatible with several databases, since MySQLi supports MYSQL alone. Then, if you want to change to a different database for your php application later, PDO makes the process easier. All you have to do is change the connection string and some queries.
<?php
$hostname = 'localhost';
$username = 'username';
$password = 'password';
$database = 'mydemo';
try {
$pdo = new PDO("mysql:host=$hostname;dbname=$database", $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo 'Connected successfully!';
}
catch(PDOException $e) {
die('Error ' . $e->getMessage());
}
?>
PDO comes with exception handling, so if there is a connection problem, an exception is thrown and it will go directly to the catch {} block.
That explains about connecting to mysql database remotely from php. One good thing about remote access is that you can grant and revoke access to the database at any time you want. I hope you like this tutorial and meet you in another interesting one. And please don't forget to share it on social media.
