YogeshChauhan.com
Learn to Establish Connection using MySQLi (object-oriented), MySQLi (procedural) and PDO with Example Code
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. 🙂


$connect->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_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.

dreamhost

Leave a Reply

Most Read

#1 How to set opacity or transparency using CSS? #2 Pagination in CSS with multiple examples #3 How to check if radio button is checked or not using JavaScript? #4 How to make HTML form interactive and using CSS? #5 How to uninstall Cocoapods from the Mac OS? #6 How to add Read More Read Less Button using JavaScript?



Recently Posted

Feb 24 How to modify the latest post array using get_posts() in WordPress? Feb 22 WordPress: How to get ACF field values from another post? Feb 22 WordPress: How to print ACF repeater field values? Feb 22 WordPress: How to print ACF array field values? Feb 21 WordPress: How to get field values in Advanced Custom Fields? Feb 21 WordPress: How to add a Search Icon in Menus with toggle effect using jQuery?



You might also like these

transform-origin Property in CSSCSSSELF JOIN in PostgresPostgresSorting Object Arrays in JavaScriptJavaScriptIf statement shorthand examples in JavaScriptJavaScriptCanvas Drawing in HTML5HTML3 Types of Arrays in PHPPHP