PHP Connect to MySQL Server

We gave you a little introduction about SQL and SQL and PHP in the previous tutorial. Now its time to learn about how to connect the MySQL database with a PHP application. To get started you first need to install a MySQL server on the system. For this, you can install the XAMPP server as advised earlier. XAMPP provides PHP and MySQL servers which you have to start in order to use PHP and MySQL in your PC. After starting the servers your PC will work as a local server to host PHP and MySQL. The local server is called the 'localhost'. To access the database in the system you have to type 'localhost/phpmyadmin' in the address bar of the browser. The 'phpmyadmin' is the admin panel of the MySQL database.

After all these settings, create a database in the 'phpmyadmin' in which you want to store data of your website. In the database, you can create many tables according to the data.

As we have studied earlier that there are more than one way to connect with the MySQL database, we will implement different ways to do that. We can use MySQL(Legacy Version), MySQLi(Improved Version) or PDO(PHP Data Object) extensions.

If we talk about MySQL extension, it was used earlier in the older version of PHP but later in PHP 5 the MySQLi version got introduced which is the improved version and more secure because of its dual procedural and object oriented approach. You can still use the MySQL extension but it is not recommended. Other than these two PDO can also be used which is more portable and supports more than twelve different databases as compared to the former extensions which only supported MySQL database.

However, MySQL has an easy way to connect and execute queries.

Note: The MySQLi extension has more speed and features as compared to the PDO extension, 
  so it is a better choice for projects specific to MySQL.

Different ways to connect to MySQL DBMS

We will first look at the MySQli and PDO approach and later on, we will discuss about the MySQl extension. Let's start:

1: MySQLi, Procedural Method

The MySQLi extension uses the MySQLi class, it is an improved version of the legacy MySQL functions. The 'mysqli_connect()' function is used to make a connection with a MySQL DBMS.

It returns resource if a connection is established or null. Let's see the steps:

Step 1: The syntax of 'mysqli_connect' is given below. You can replace the 'hostname' with 'localhost', 'username' with 'root' or whatever you have specified, replace 'password' with your DBMS password or leave it blank if there isn't any and then replace 'database' with your database name or you can also leave it blank if you only want to connect to the MySql DBMS and don't have any Database right now. The '$link' will store the return value of the 'mysqli_connect()' function. We will use this variable to ensure whether we are really connected to the DBMS or not.The syntax is:

$link = mysqli_connect("hostname", "username", "password", "database");

Step 2: After succesfully connecting to the database you can perform various operation on the database. You can write an SQL query directly within your website code and it will be implemented on the database by using 'mysqli_query()' funtion. The syntax is given below you can have a look at it, we will discuss this later in the tutorial.

<?php
    $result = mysqli_query($link,"SELECT lastname FROM employees");
?>

PHP mysql connection

Given below is an example (php mysql connection code) of establishing a connection with the database using the procedural approach and then verifying whether the connection is established or not.

2: MySQLi, Object Oriented way

In the object oriented way you can observe that all the functions are now replaced with objects. Firstly the most important '$mysqli' object is created by using the 'new' keyword to establish the connection, and then this object is used everywhere to access any other features of the MySQLi class. The difference you can easily observe and you can use whatever approach you are comfortable with.

The syntax of the Object Oriented approach is:

<?php
    $link = new mysqli("hostname", "username", "password", "database");
?>

Have a look at the example below:

3: PHP Data Objects (PDO) Method

The PDO method can be used where you want your web application to also be compatible with databases other than MySQL. Suppose for any reason you have to change your database then if you are using PDO you can easily migrate to the new DBMS with minor changes in the code. But if you were using MySQli extension then you would have to remove the older connection's code and rewrite the new one.

Let's see the steps to use PDO approach:

Step 1: Here in the first step we will create and object using PDO and then we will access that object to use features of PDO. To connect using PDO follow the given syntax:

$conn = new PDO("mysql:host=hostname;dbname=database", "username", "password");

Step 2: To run any query after establishing the connection successfully, follow the syntax given below. This statement will get the data from the specified table and will store it in the '$result' variable to use later.

<?php
    $result = $conn->query("SELECT lastname FROM employees");
?>

CONNECT TO MYSQL USING LEGACY PHP FUNCTIONS

Now we will see how to connect MySQl DBMS with a PHP website using the 'mysql()' function. These functions are deprecated in PHP 5.5 and maybe in the upcoming future these will be completely removed. You can still use these functions but it is always recommended to use MySQli or PDO. You should only use these functions for backward compatibility if it is absolutely necessary.

Step 1: The frst step is to create a connection using the username, hostname, password and database name. It is almost similar to the MySQli extension but here the database name is given in a special function 'mysql_select_db()' and is not included within the 'mysql_connect()' function.

Look at the syntax below to observe the changes between the two:

<?php
    mysql_connect('localhost','username','password');
    mysql_select_db('dbname');
?>

Step 2: To run a query you can use the 'mysql_query()'. The syntax is:

<?php
    $result = mysql_query('SELECT lastname FROM employees');
?>

The example below shows an entire code to connect MySQL DBMS using MySQL extension. Have a look at it and compare it with the MySQli extension.

Closing the MySQL Database Server Connection

Now its time to close the connection. It is very important to close the established connection. Although the connection gets automatically shut down as soon as any execution of the script ends or the webpage is closed that is why we need the connection file at every page to establish a new connection each time. But what if you want to close the connection earlier?

Let's see how to do it:

MySQLi Procedural Approach

The function is 'mysqli_close()', which will be used to close the connection established by the PHP application. just use the connection variable as the argument in the function and that's it. This connection returns true if the connection is closed successfully otherwise false.

The syntax is:

mysqli_close($link);

MySQli Object Oriented Approach

Syntax :

$mysqli->close();

PHP Data Objects (PDO) Approach

Although making connections through PDO is a bit complex but closing it is very easy, just use the unset() function. The syntax is:

Syntax :

unset($pdo);