YogeshChauhan . com

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. Don't think much about it now. 🙂


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 uninstall Cocoapods from the Mac OS? #4 How to add Read More Read Less Button using JavaScript? #5 How to Use SQL MAX() Function with Dates? #6 PHP Login System using PDO Part 1: Create User Registration Page

Recently Posted

May 7 How to disable right click and drag and drop of images using jQuery? May 7 How to render Lists in React? May 7 What’s the difference between variables in CSS and SCSS (Sass)? May 7 How to define variables in SCSS (Sass)? May 7 How to show and hide an element on click in React? May 5 Use inline if to make a shorter conditional syntax in React

You might also like these

Modules and its Core features in JavaScriptJavaScriptSelector Lists and Combinators in SCSS (Sass)SCSSUse inline if to make a shorter conditional syntax in ReactReactLearn to Make a Simple Contact Us Form using PHP and PDO-MySQLPHPsubstring() Method in JavaScriptJavaScriptSorting Arrays in JavaScriptJavaScript