What is PDO in PHP ?

In this article, we will discuss PDO in PHP in detail.

There are three main options for 

<?php
    try {
        $dbhost = 'localhost';
        $dbname='gfg';
        $dbuser = 'root';
        $dbpass = '';
        $connect = new PDO(
"mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
    }
   catch (PDOException $e) {
        echo "Error : " . $e->getMessage() . "<br/>";
        die();
    }
    $count = $connect->exec("DELETE FROM employee WHERE emp_id = 123 ");
    echo("Number of rows to be deleted from the employee table is: ". $count);
?>

ting to a MySQL database server.

• MySQLi procedural
• MySQLi object-oriented
• PDO (PHP Data Objects )

MySQLi procedural and MySQLi object-oriented only support MySQL database but PDO is an advanced method along with MySQL which supports Postgres, SQLite, Oracle, and MS SQL Server.

PDO is more secure than the first two options and it is also faster in comparison with MySQLi procedural and MySQLi object-oriented.

PDO is a database access layer that provides a fast and consistent interface for accessing and managing databases in PHP applications. Every DBMS has a specific PDO driver that must be installed when you are using PDO in PHP applications.

It simplifies the database operations including:

• Creating database connection
• Executing queries
• Handling errors
• Closing the database connections

When we want to connect PHP and MySQL then we need to follow 3 steps:

• Connection with the database
• Run SQL Query
• Closing the database connection

Steps for connection using PDO.

Connection:

In this step, we connect with the database

$conn = new PDO($db_name,$username,$password);

When we want the connection between PHP and MySQL using PDO then we need to create an object of the PDO class.

$db_name = "mysql:host=localhost;dbname:gfg";

Note: $db_name contains the following information.

Run SQL query: To run SQL query we can use two methods:

• query():

$sql = $conn->query("select * from students");

• prepare():

 $sql = $conn->prepare("select * from students");

Note: The prepare() method is more secure than the query() method.

Close the connection:

For closing the database connection using PDO.

$conn = null;

Parameters: It contains the following parameters.

• dsn: It contains the information regarding connection to the database.
• user: It contains the user name.
• password: It contains the password of the database.

Return Value:

On success, it will return the PDO object. And on failure, it will return the PDOException object.

Handling error during connection:

The PDOException object will be thrown in case of any error. We can catch the exception to handle the error. 

<?php
    try {
          $dsn = "mysql:host=localhost;dbname=gfg";
        $user = "root";
        $passwd = "";
        $pdo = new PDO($dsn, $user, $passwd);
    }
    catch (PDOException $e) {
         echo "Error!: " . $e->getMessage() . "<br/>";
         die();
     }
?>

Output:

Error : SQLSTATE[28000] [1045] 
Access denied for user 'roott'@'localhost' 
(using password: NO)

Example 1: Suppose we have a database "gfg" with a table "students". We want to fetch the details like "id" and name of all the students present in the table "students".

<?php
    $dsn = "mysql:host=localhost;dbname=gfg";
    $user = "root";
    $passwd = "";
    $pdo = new PDO($dsn, $user, $passwd);
    $stm = $pdo->query("SELECT * FROM students");
    $rows = $stm->fetchAll(PDO::FETCH_ASSOC);
    foreach($rows as $row) {
        printf("{$row['id']} {$row['name']}\n");
    }
?>

Output:

1  Student1
2  Student2
3  Student3
4  Student4

Example 2: In this case, suppose we have the "employee" table in "gfg" database. We want to delete the employee from the "employee" table whose "id = 123". 

<?php
    try {
        $dbhost = 'localhost';
        $dbname='gfg';
        $dbuser = 'root';
        $dbpass = '';
        $connect = new PDO(
"mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
    }
   catch (PDOException $e) {
        echo "Error : " . $e->getMessage() . "<br/>";
        die();
    }
    $count = $connect->exec("DELETE FROM employee WHERE emp_id = 123 ");
    echo("Number of rows to be deleted from the employee table is: ". $count);
?>

Output:

The number of rows to be deleted from the employee table is: 1

Benefits of PDO:

• Usability: PDO contains helper functions to operate automatic routine operations.
• Reusability: We can access multiple databases because it offers a unified API.
• Security: It provides protection from SQL injection because it uses a prepared statement. A prepared statement separates the instruction of the SQL statement from the data.
• Error handling: It uses exceptions for error handling. There are three types of modes:
  • Silent
  • Warning
  • Exception
• Multiple database support: It is used to access any database which is written for the PDP driver. We need to find a suitable driver and add them when we use them. There are several PDO drivers available like Microsoft SQL Server, Sybase, PostgreSQL, and many more.

Supported Database: PDO supports 12 different databases.

• MySQL
• PostgreSQL
• Oracle
• Firebird
• MS SQL Server
• Sybase
• Informix
• IBM
• FreeTDS
• SQLite
• Cubrid
• 4D