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
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.
We will first look at the MySQli and PDO approach and later on, we will discuss about the MySQl extension. Let's start:
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
'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"); ?>
Given below is an example of establishing a connection with the database using the procedural approach and then verifying whether the connection is established or not.
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.
<?php $link = new mysqli("hostname", "username", "password", "database"); ?>
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.
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"); ?>
Now we will see how to connect MySQl DBMS with a PHP website using the
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
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
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.
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?
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:
Although making connections through PDO is a bit complex but closing it is very easy, just use the
unset() function. The syntax is: