Guide to learning how to connect PHP with MySQL

Learn how to duplicate a page in WordPress
How to create and register a domain – Complete Guide

This tutorial will be very useful if you are starting to learn about the development of sites. Using this tutorial, you will learn how to connect PHP with MySQL . But do not know exactly why you need it? Simple: it is this feature that you can edit, view, delete, and manage your tables created in any other way. 

Let’s start!

What will you need 

Before beginning this guide, you will need the following:

  • Access to the control panel of your web hosting

Step 1 – Creating a MySQL database

This step is necessary only if you have not already created a MySQL database. In Hostinger, a MySQL database can be created easily in the section Database> MySQL database.accessing hpanel to know how to connect with php mysql

Once inside the MySQL Database section, enter the order details and press on Create .  user details and database password for how to connect with php mysql

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

Step 2 – Connecting PHP with MySQL writing PHP code

In the example we use, we will connect to a database with the name u266072517_name . That our database has an associated user named u266072517_user . You must also have a password for the database. In our example, the password is buystuffpwd .

Another important thing to mention is that you must have a servername and hostname correct. In Hostinger, the hostname of the MySQL can be found in the section MySQL database . In our example, it is called mysql.hostinger.com .

IMPORTANT: In many cases, when you are connecting to a database locally (the script that is connecting to a database is sent 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 (eg, your computer), you must use the IP address of your server. For details, contact your hosting provider so that it provides you with the correct information about what hostname to use.

There are two methods to connect to a SQL database with PHP – they are the MySQLi and PDO . One of the most important changes between them is that they supported the “separate statements”, which removes the SQL attacks capacity when you make changes in the database. The original functions of “mysql_” are obsolete and should not be used because they are insecure and are not maintained or updated by developers.

MySQLi means MySQL Improved and adds new features to the MySQL interface. Since the PDO means PHP Data Object . The main difference between them is that the PDO has support for multiple types of databases (MySQL, MS SQL, PostgreSQL DB) in the same script. However, you can only write functions related to data once. The PDO is “object – oriented”, in which the connection to the database is created by creating a variable object. Example of creating a new object:

  1. $ my_object = new OBJECT ( ) ;
$ My_object = new Object ();

 

MySQLi

Here is an example of basic PHP code that can be used to connect to a MySQL database using MySQLi.

  1. <? Php
  2. $ servername = “mysql.hostinger.com” ;
  3. $ database = “u266072517_name” ;
  4. $ username = “u266072517_user” ;
  5. $ password = “buystuffpwd” ;
  6. // Create connection
  7. $ conn = mysqli_connect ( $ ServerName, $ username, password $, $ database ) ;
  8. // Check connection
  9. if ( ! $ conn ) {
  10. die ( “Connection failed” . mysqli_connect_error ( ) ) ;
  11. }
  12. echo “Connected successfully” ;
  13. mysqli_close ( $ conn ) ;
  14. ?>
<? Php
$ Servername = "mysql.hostinger.com";
$ Database = "u266072517_name";
$ Username = "u266072517_user";
$ Password = "buystuffpwd";

// Create connection

Mysqli_connect $ conn = (ServerName $, $ username, password $, $ database);

// Check connection

if (! $ conn) {

    die ( "Connection failed". mysqli_connect_error ());

}
echo "Connected successfully";
mysqli_close ($ conn);
?>

This main method used in the above script is mysqli_connect () . This is an internal function of the PHP. And you can read more about her here .

At the very beginning of our code, we can see some variable declarations and values assigned to these variables. Usually we need four variables to establish a connection: $ servername , $ database , $ username and $ password .

In the code, we define the exact details of the database as values for these variables and then the function mysqli_connect () use these variables to pass to the function.

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

  1. if ( $ conn-> connect_error ) {
  2. die ( “Connection failed:” . $ conn-> connect_error ) ;
  3. }
if ($ conn-> connect_error) {
    die ( "Connection failed:". $ conn-> connect_error);
}

A function die () is executed here, which basically kills our script and gives us the message that we had set before. So, by default, it will inform Connection failed: and an exact error message is displayed to help us determine the problem.

If the connection is successful, the next part of the code will be executed:

  1. echo “Connected successfully” ;
echo "Connected successfully";

This will simply display a success message instead of the previous message, since the statement IF is triggered based on a fault condition.

The last part of the code is:

  1. mysqli_close ( $ conn ) ;
mysqli_close ($ conn);

This code will simply close the connection to the database manually. If not specified, the connection will close alone when the script ends. You can use this same line to close the connection before the end of the script.

PDO

A connection to PDO database requires you to create a new “PDO object” with a DSN (Data Source Name) , as well as a user name and password. The DSN defines the type of database, the database name and any other required information.

The DSN can be a simple variable which is then used as a parameter to create a PDO object, as shown in the code below.

The PDO has support for multiple types of databases and DSN is where you define alternative connections, replacing the line “mysql” with the syntax for the other database. In an actual script, you allow the user to choose which connection is used and write code that sets the variable required DSN. For this tutorial, we are only using MySQL.  

When creating the PDO object that represents the connection to the database, you can include it in the code “try … catch …”. This means that the script will try to connect the supplied code, if there is a problem, the code in the “catch” section will be executed.

You can use the “catch” block to display error messages or perform alternative codes if the attempt fails. In this example, a simple error message is displayed to tell you which piece of information was incorrect.

  1. <? Php
  2. $ servername = “mysql.hostinger.com” ;
  3. $ username = “u266072517_user” ;
  4. $ database = “u266072517_name” ;
  5. $ password = “buystuffpwd” ;
  6. // Create a new connection to the MySQL database using PDO
  7. $ conn = new mysqli ( $ ServerName, $ username, password $ ) ;
  8. // Check connection
  9. if ( $ conn-> connect_error ) {
  10. die ( “Connection failed:” . $ conn-> connect_error ) ;
  11. }
  12. echo “Connected successfully” ;
  13. ?>
<? Php
$ Servername = "mysql.hostinger.com";
$ Username = "u266072517_user";
$ Database = "u266072517_name";
$ Password = "buystuffpwd";

// Create a new connection to the MySQL database using PDO
$ Conn = new mysqli (ServerName $, $ username, password $);

// Check connection
if ($ conn-> connect_error) {
    die ( "Connection failed:". $ conn-> connect_error);
} 
echo "Connected successfully";
?>

Step 3 – Checking connectivity and correcting a common mistake

If PHP connection code is executed successfully and the connection is established smoothly, you will see this message below when visiting the script on your website:

Troubleshooting connection errors PHP MySQL

Now, if the connection is not successful, you will certainly see something different. Errors are one little different for MySQLi and PDO.

incorrect password error

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

Error with MySQLi:

Error with PDO:

So if you see this message, the first thing to do is to check the details of the database. Another reason for this error may be happen that you do not have a user assigned to a database. In the case of Hostinger control panel, your database in the section MySQL database should look like the image below:database list and users in the process of connecting with php mysql

Or in the case of cPanel, you should see your database section like this:current database in the process of connecting with php mysql

You can not connect to MySQL Server

With MySQLi:

The error Can not connect to MySQL serves on ‘server’ (110) means that the script is not receiving a server response. This was because we define server instead of localhost as a servername , and this name is not recognized.

With PDO:

  • [HY000] means general error .
  • [2002] means Can not 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 rule of thumb when you are troubleshooting errors: check your error log.

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

Inside (the said folder), you will be able to see all the mistakes that the script possibly generated and that will greatly help you to solve the problem.

Drago watching tutorials on the blog after teaching how to connect with php mysql

Conclusion

In this tutorial, we provide you with a good basic knowledge about databases and how to connect PHP with MySQL. Now you know you can do this using the MySQLi and PHP Data Objects (PDO).

You can use the stuff you just learned in more advanced options settings scripts. Connect to databases is the first and most important step when working with them. 

LEARN MORE

  • Understand the difference between SQL and MySQL
  • What is PHP? Basic Guide to PHP programming