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 Solution to the error “Visual Studio Code can’t be opened because Apple cannot check it for malicious software” #2 How to add Read More Read Less Button using JavaScript? #3 How to check if radio button is checked or not using JavaScript? #4 Solution to “TypeError: ‘x’ is not iterable” in Angular 9 #5 How to uninstall Cocoapods from the Mac OS? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

#Apr 8 JSON.stringify() in JavaScript #Apr 7 Middleware in NextJS #Jan 17 4 advanced ways to search Colleague #Jan 16 Colleague UI Basics: The Search Area #Jan 16 Colleague UI Basics: The Context Area #Jan 16 Colleague UI Basics: Accessing the user interface
You might also like these
WordPress: How to find all posts from a custom post type with multiple custom fields values?WordPressObject destructuring in JavaScript: Unpacking fields from objects passed as function parameterJavaScriptSolution for “Yarn build: Failed because of a stylelint error”WordPressColumn and Table Alias in PostgresPostgresHow to create a dynamic countdown using HTML and JavaScript?HTMLRevisiting variable scope in JavaScriptJavaScript