• PDO is an acronym for PHP Data Objects.
• It is a library of PHP. PDO is a lean, consistent way to access databases.
• This means developers can write portable code much easier.
• PDO has a much nicer interface, you will end up being more productive, and write safer and cleaner code.
• PDO is enabled by default in PHP installations.

Benefits of PDO:

• It prevents from SQL injections by using prepared statements.
• No need to change query's, if we are switching to databases.
• The main advantage of PDO over MySQLi is its database driver support. PDO supports almost 12 different drivers, opposed to MySQLi, which supports only MySQL.

To get list of all the drivers that PDO currently supports, use below code :

                            <?php
                            
                            var_dump(PDO::getAvailableDrivers());
                            
                            ?>
                            

How to make database connection with PDO and MySQLi ?

• It's a cinch(extremely easy task) to connect to a database with PDO and MySQLi:

<?php
/*
PDO, by using PDO class.
Here we are creating $pdo object of PDO class and passing arguments
to constructor method.
*/
$pdo = new PDO("mysql:host=localhost;dbname=db", 'username', 'password');

// mysqli, procedural way
$mysqli = mysqli_connect('localhost','username','password','db');

// mysqli, object oriented way
$mysqli = new mysqli('localhost','username','password','db');
?>

Named Parameters vs Unnamed Parameters

• The difference between named and unamed parameters is that with unnamed parameters you'll have to take care about the order in which they will be bound to the query.
• The question mark parameter binding might seem shorter, but it isn't as flexible as named parameters due to the fact that the developer must always keep track of the parameter order.
• MySQLi doesn't support named parameters.

How to insert rows in table using PDO ?

1. By using named placeholders :

<?php

$db = new PDO('mysql:host=localhost;dbname=database', 'root', '');

// insertion with pdo using prepared statement start

// storing user form input data in an array named as data 
$data = [ 
            'first' => 'Rahul',
            'last' => 'Yadav'
 ];

// named placeholders
$statement = $db->prepare("insert into users (firstname, lastname)
             values (:first, :last)");

if ($statement->execute($data)) {

  echo "Records inserted successfully";

} else {

echo "Unsuccessfull";

}

// insertion with pdo using prepared statement ends

?>

2. By using unnamed placeholders :

<?php

$db = new PDO('mysql:host=localhost;dbname=database', 'root', '');

// insertion with pdo using prepared statement start

// storing user form input data in an array named as data 
$data = [ 
             'Rahul',
             'Yadav'
 ];

// unnamed placeholders
$statement = $db->prepare("insert into users (firstname, lastname)
             values (?, ?)");

if ($statement->execute($data)) {

  echo "Records inserted successfully";

} else {

echo "Unsuccessfull";

}

// insertion with pdo using prepared statement ends

?>

How to update rows in table using PDO ?

<?php

$db = new PDO('mysql:host=localhost;dbname=database', 'root', '');

// update with pdo using prepared statement start

// storing user form input in an array named as params 
$params = [
            'first' => 'R',
            'last' => 'Y',
            'user_id' => '1'
 ];

$statement = $db->prepare("update users set firstname = :first,
             lastname = :last where id = :user_id ");

if ($statement->execute($params)) {

  echo "Records updated successfully";

} else {

echo "Unsuccessfull";

}

// update with pdo using prepared statement ends

?>

How to delete rows in table using PDO ?

<?php

$db = new PDO('mysql:host=localhost;dbname=database', 'root', '');

// deletion with pdo using prepared statement start

// data we want to delete in an array named as params 
$params = [
            'first' => 'R',
            'last' => 'Y'
 ];

$statement = $db->prepare("delete from users where firstname = :first
             and lastname = :last");

if ($statement->execute($params)) {

  echo "Records deleted successfully";

} else {

echo "Unsuccessfull";

}

// deletion with pdo using prepared statement ends

?>