How to connect to MySQL database in PHP using MySQLi and PDO

Any PHP program that wants to do database operations must connect to the database first. So, we need to write code to connect to a database using PHP and then we can do various database operations on it.

This topic will discuss how we can connect to MySQL database using PHP. Also, we will see how you can include MySQL connection scripts in your PHP program so that you can write it once and use it in other programs that need to access a database.

Connect to database in PHP using MySQLi and PDOWatch YouTube Video

Establish Connection

You need to choose a MySQL database before establishing a connection to it. You can use the 'test' database which comes along with XAMPP/WAMP installation. See the below screenshot.

MySQL database connection

To connect to MySQL database, we need below four parameters:

  1. Server name - in our case it is 'localhost'
  2. Userid - let us use 'root' as userid, it exists as default userid
  3. Password - By default, 'root' does not have a password
  4. Database name - in our case it is 'test'

With this, let us write PHP code to connect to MySQL database.

Different ways to connect to the database

We will discuss three types of connections here from PHP. These are:

  1. MySQLi Procedural
  2. MySQLi Object Oriented
  3. PDO (PHP Data Objects)

Let us take a look at each of these types of connections:

MySQLi Procedural Connection to MySQL database from PHP

Using this type of connection we can use MySQLi extension to connect to a database. We can use mysqli_connect() method to connect to the database. See the below code:

dbconnect_pro.php

mysqli_connect() method is used to connect to the database. This method takes four parameters. These are Server, userid, password and database name. The database name is not mandatory in this method. In our case, we have provided these values using four variables to connect to a specific database. If the connection is successful, the method returns true. If it cannot connect to the database because of some issues, then it returns false. In case of a connection error, we display the actual error message by using mysqli_connect_error() function.

Create a directory named "dbconnect" under your xampp/htdocs, and save the above code in a file named "dbconnect_pro.php" in that folder.

Now, open your browser and type localhost/dbconnect/dbconnect.php and enter. Make sure in your XAMPP control panel, services are running. You will see the below result which shows the connection is successful.

MySQL database connection in php mysqli_connect

You can see database connection is successful in this case. If the connection could not be established, it would have displayed an error message.

MySQLi Object Oriented Style connection to MySQL database from PHP

For this also we can use MySQLi extension to connect to the database, but in this case, we will create a mysqli instance by using new mysqli(). See the below code:

dbconnect_oo.php

We are using the same four parameters here - Server, userid, password and database to create the mysqli instance using new mysqli(). Here also database name is not mandatory. In case of error in connection, we display the actual error message by using $conn->connect_error function.

Save the above code in a file named dbconnect_oo.php in the same directory as earlier.

Now, open your browser and type localhost/dbconnect/dbconnect_oo.php and enter. Make sure in your XAMPP control panel, services are running. You will see the below result which shows the connection is successful.

MySQL object oriented database connection in php

You can see database connection is successful in this case. If connection could not be established, it would have displayed an error message.

PDO connection to MySQL database from PHP

For this we will create a PDO instance by using new PDO() to connect to the database. See the below code:

dbconnect_pdo.php

We are using try...catch here and the same four parameters are used - Server, userid, password and database to create the PDO instance using new PDO(). In this case, database name is mandatory. PDO needs a string with server and database name. setAttribute() method is used to set the error mode to exception. In case of an error, we display the actual error message by using PDOException.

Save the above code in a file named dbconnect_pdo.php in the same directory as earlier.

Now, open your browser and type localhost/dbconnect/dbconnect_pdo.php and enter. Make sure in your XAMPP control panel, services are running. You will see below result which shows the connection is successful.

MySQL PDO connection to php

You can see the database connection is successful in this case. If the connection could not be established, it would have displayed an error message.

PHP program to fetch and display data using these connections.

Now, let us write a simple PHP program to connect to the database, fetch data and display in an html table. We will use these three types of connections separately to show how you can use them in a PHP program.

Since we have already written database connection code, we will just include it in our program, advantage of this is that we can reuse it in aother program.

We will create a table named 'category' in the test database. Just run the below create-table script. Copy the entire code and paste it in SQL window and then click on go. Table will be created and data will be inserted in it.

The structure of the table and the rows in it are given below:

Table: category

display data in html table from MySQL database in php

Table data

display data in html table in php and mysql

Use MySQLi Procedural connection

We will create index.php program to display the data in an html table. We will connect to the database using MySQLi procedural connection as explained earlier. So, we will include dbconnect_proc.php in index.php. See below code:

index.php

See lines 16, 17 and 18. We have included connection script in line 16. Next we have our select statment and then executed it using mysqli_query(). Then in line 32, we are using a foreach loop to fetch the data and display in a html table.

Run in the browser using localhost/dbconnect you will see below output:

display data in html table in php and mysql

Here, we do not need to print the connection successful message, you can remove it. We have used it just to show you which connection type we are using.

Use MySQLi Object-Oriented style connection

Here we have to include dbconnect_oo.php, so replace line 16 with include "dbconnect_oo.php"; // MySQLi object-oriented connection and also replace line 18 with $result = $conn->query($sql); to execute the query. Rest of the code will remain same.

Run in the browser using localhost/dbconnect you will see below output:

display data in html table in php and mysql

Use PDO connection

Here we have to include dbconnect_pdo.php, so replace line 16 with include "dbconnect_pdo.php"; // PDO connection and also replace line 18 with $result = $conn->query($sql); to execute the query. Rest of the code will remain same.

Run in the browser using localhost/dbconnect you will see below output:

display data in html table in php and mysql

This way, we can include a database connection program to access the database. Write once and use it multiple times whenever required. So, write a separate program to connect to the database and include it in all other programs that need to access the database. Also, in case you need to change your database connection parameters, you have to change them in one place only. So, this is easier for code maintenance.

Which type of connection is better?

While MySQLi supports only MySQL database, PDO connection supports many other databases. So, in case there is a change to connect to a different database then you do not have to change the code much for PDO connection. But with MySQLi connection a significant amount of code change is required.

Connect to database in PHP using MySQLi and PDOWatch YouTube Video

connect php with mysql in xamppConclusion

Connection to a database is the basic task you need to perform before writing a PHP program with database access. This was a simple example to show you how you can connect to MySQL database using various types of connection in PHP. Hope this will be useful for you.