Yogesh Chauhan's Blog

Learn to Establish Connection using MySQLi (object-oriented), MySQLi (procedural) and PDO with Example Code

in PHP on December 20, 2019

If you are using PHP 5 or later versions, you can connect to MySQL database using MySQLi and PDO

1. MySQLi = MySQL Improved

2. PDO = PHP Data Objects

Which one should I use MySQLi or PDO?

I prefer to use PDO myself because it supports 12 different database systems so even if I switch from one database to another, I don’t need to worry about my code much. MySQLi makes it difficult to switch from database, I guess that’s their purpose!

Let’s not talk about what should you do and what not and move on to the connection part.

Here are the links for installation in case if you don’t have it installed already.

1. MySQLi Installation : http://php.net/manual/en/mysqli.installation.php

2. PDO Installation: http://php.net/manual/en/pdo.installation.php

Let’s start with MySQLi OO(Object Oriented)

We need the following code no matter what we are using to make a connection.

$servername = "localhost";
$username = "root";
$password = "";

I will explain all the examples using localhost server and in case if you are not aware of it, default localhost username is root and there is no password.

The following code will create a connection in OO MySQLi.

$connect = new mysqli_connect($servername, $username, $password);

if($connect === false){
    die("Could not connect. " . mysqli_connect_error());
echo "Connected successfully";

You need to add those database credentials in the beginning which you can include it using a PHP file include so that you don’t need to type it again and again.

So full code will be:

$servername = "localhost";
$username = "root";
$password = "";

$connect = new mysqli_connect($servername, $username, $password);

if($connect === false){
    die("Could not connect. " . mysqli_connect_error());
echo "Connected successfully";

It’s pretty simple. We TRY to make the connection and check if everything went well using if condition. 

mysqli_connect_error() is will be the error provided by MySQLi in case of no connection.

There are many different types of error but mostly there will be a typo or if you are using database parameter then you might have missed to create the database first!

Let’s move on to MySQLi (procedural)

Both Object Oriented and Procedural have same code except one word. new. The OO approach uses new keyword to establish the connection.

So you can create the connection using the same code above. All you need to do is remove the word new in the connection.

Object Oriented Approach => new mysqli_connect($servername, $username, $password);

Procedural approach => mysqli_connect($servername, $username, $password);

Let’s move on to PDO

Establishing a connection in PDO is a bit… just a bit… tough if you are moving from MySQLi to PDO but once you practice it more, it will be as easy as any other thing for you. I Promise!

As I have mentioned it before, we are going to use the same localhost credentials.

We use try and catch to establish connection using PDO.

For example:

try {
    $connect = new PDO("mysql:host=$servername;dbname=demo_database", $username, $password);
    $connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
catch(PDOException $e)
    echo "Could not connect. Error: " . $e->getMessage();

So the first line try to establish a connection.

Notice that it asks for database name as well which in my case is demo_database. If you don’t specify a database name, you’ll get an error.

The following line is setting the PDO error mode to exception.


All of those approaches follows the same rule. TRY=>If failed(show error)

Let’s see how to close the connection in all of them

MySQLi Object-Oriented => $conn->close();

MySQLi Procedural => mysqli_close($conn);

PDO => $conn = null;

NOTE: All established connections will be closed automatically when the whole PHP script ends. But in some cases it requires to close connection manually before the script ends.

Most Read

#1 How to check if radio button is checked or not using JavaScript? #2 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #3 How to add Read More Read Less Button using JavaScript? #4 How to uninstall Cocoapods from the Mac OS? #5 PHP Login System using PDO Part 1: Create User Registration Page #6 How to Use SQL MAX() Function with Dates?

Recently Posted

#Aug 15 Is PHP still good for back-end programming? #Aug 10 How to create a multisite network in WordPress? #Aug 3 How to create a circle that follows a cursor using JavaScript and CSS? #Aug 3 How to make a curtain slider using jQuery and CSS? #Aug 2 How to progressively load images and add a blurry placeholder? #Aug 1 How to create a placeholder loader (throbber) using CSS?
You might also like these
How to link/add CSS file to HTML Document?CSSThe substr() method in JavaScript and how it’s different from substring()JavaScriptWhat are Web services?MiscellaneousWhat is PostgreSQL? How similar or different it is from SQL?PostgresHow to enable GD library support for PHP on windows server?PHPLearn how to add Scroll Indicator using CSS and JavaScript?CSS