How to Connect PHP to MySQL Database

Introduction

This tutorial will be very useful for you in case you are starting to learn about website development. Using this tutorial, you will learn how to use PHP to connect to MySQL database. That is needed if you wish to modify, view, delete or manage your created tables in any other way.

What you’ll need

Before you begin this guide you’ll need the following:

• Access to your hosting control panel

Step 1 — Creating a MySQL Database

This step is required in case you do not have a MySQL database created yet. At Hostinger, MySQL database can be created easily on MySQL Databases section. For instructions how to create a MySQL database on cPanel, see this tutorial.

Once you are in MySQL Databases section, enter the required details and hit Create button.

IMPORTANT: Write down details of the MySQL database you just created. You will need them in the next step.

Step 2 — Writing the PHP code for connecting to MySQL database

We will be connecting to a database with a name u266072517_name and this database of ours has an associated user u266072517_user. You must as well have a password to the database, in our case the password will be buystuffpwd.

Another thing worth mentioning is that you must have a correct servername or hostname. At Hostinger, MySQL hostname can be found in MySQL Databases section. In our example, it is mysql.hostinger.com.

In a lot of cases, when you are connecting to a database locally (the script which is connecting to a database is uploaded to the server where the database exists), you can use localhost. If that does not work, or if you are connecting to a database from a remote location (for example, your computer) you have to use the IP address of the server. For more details, contact your hosting provider so they could provide you with the correct information on what to use as a hostname.

There are now two methods to connect to an SQL database with PHP – they are MySQLi and PDO. One of the most important changes is that they both support ‘prepared statements’, which remove the ability for SQL injection attacks when making changes to the database. The original ‘mysql_’ functions are deprecated and should not be used as they are unsafe and no longer being maintained or developed.

MySQLi stands for MySQL Improved and adds new features to the MySQL interface specifically. PDO stands for PHP Data Object. The main difference between PDO and MySQLi is that PDO supports a number of different database types (MySQL, MS SQL, Postgre DB) in the same script, however you only have to write data related functions once. PDO is ‘object oriented’, the connection to the database is created by creating a variable object. Example of creating a new object:

MySQLi

So here is the basic PHP code example which can be used to establish a connection to a MySQL database using MySQLi:

The main method used in this script is mysqli_connect(). This is an internal PHP function, you can read more about it here.

At the very beginning of our code, we see few variable declarations and values assigned to those variables. Usually, we need four variables to establish a proper connection: $servername, $database, $username and $password. In the code, we have set our exact database details as values to those variables and later, in the function mysqli_connect() we use those variables to pass onto the function.

The next step is the function mysqli_connect(), it tries to establish a connection to a database with the provided details and the next part of the code is executed based on the success of mysqli_connect() function. As you can see, we have set that if the connection is not successful, it will give us this message:

A function die() is executed here, which basically kills our script and gives us a message that we have set. So this will by default say Connection failed: and an exact error message will follow to help us determine the issue.

If the connection is successful, next part of the code will be executed:
This will simply print a success message instead of the previous message since it has IF statement which triggers based on a failed condition.

The last part of the code is:
This will simply close the connection to a database manually. If not specified, connection will close by itself once the script ends. You can use this line to close the connection before the end of the script.

You should be able to run the code now by accessing the script through your domain.

PDO

A PDO database connection requires you to create a new ‘PDO object’ with a Data Source Name (DSN), username and password. The DSN defines the type of database, the name of the database, and any other information if required. The DSN can be a simple variable which is then used as a parameter when creating the actual PDO object, as shown in the code below.

PDO supports multiple types of database and the DSN is where you define the alternate connections, replacing the ‘mysql:’ line with the syntax for the other database. In a real script, you can allow the user to choose which connection is used and write code that choses the required DSN variable. For this tutorial, we are only using MySQL.

When creating the PDO object that represents the database connection, you can wrap it in ‘try…catch…’ code. This means that the script will try to connect using the code provided, and if there is a problem, the code in the ‘catch’ section will run. You can use the catch block to display error messages or run alternate code if the try fails. In this example, a simple error message is shown to tell you which piece of information was incorrect.

Step 3 — Checking connectivity and fixing common issue

If the PHP connection code runs successfully and the connection is established with no problems, you will see this when you visit the script on your website:

Troubleshooting PHP MySQL Connection Errors

Now if the connection was not successful, you will see something different. The errors are slightly different for MySQLi and PDO.

Incorrect Password Error

For example, if we change the password in PHP code a bit (but did not change it in the actual database).

Error with MySQLi:

Error with PDO:

So in case you see this message, the first thing to do is to check the database details. Another reason for this error could be that you do not have a user assigned to a database. In the case of Hostinger control panel, your database in MySQL Databases section should look like this:

Or in the case of cPanel, you should see your database section like this:

Cannot Connect to MySQL Server

With MySQLi:

Can’t connect to MySQL server on ‘server’ (110) means the script did not get a response from a server, that happened because we have set server instead of localhost as a servername, and this name is not recognised.

With PDO:

• [HY000] means general error.
• [2002] means Can’t connect to local MySQL server. The rest of the messages provides more detail, stating that the ‘host’ was not found.

And of course, it is always important to remember one golden rule of troubleshooting an error: checking error_log.

It can be found in the same folder where the script is running. For example, if we are running a script in public_html, you will find the error_log file in the same folder.

Inside of it, you will see all the errors that the script possibly generated and that will help you greatly when troubleshooting the issue.

Conclusion

In this tutorial we provided you the very basic knowledge about database and how to properly connect to MySQL database using MySQLi and PHP Data Objects (PDO). You can use the learned material in your more advanced scripts and configurations. Connecting to a database is the first, most important step when working with them.

Read Next:

• How to Insert Data Into MySQL Database