PHP PDO : PHP Data Object

What is PDO

It is an extension of the PHP language for database access. Fully object-oriented, it has several important features, in addition to supporting several database mechanisms.

Main Features:

Parameterized queries
Different types of return
Different types of exception handling
Transaction handling

PDO supported databases

PDO has support for the main relational databases on the market. This is possible because of the Driver concept it uses. Basically a driver is an extension that we install in PHP and that indicates how PDO will communicate with that specific database.

Main Available Drivers:

MySQL
SQL Server
PostgreSQL
Oracle
SQLite
FireBird

Compatibility between different databases

An advantage of PDO in relation to other database extensions for PHP is the possibility of executing queries in different DBMSs using the same classes and methods.
Theoretically, it would be possible to take any application that uses PDO and change the DBMS without problems, since the SQL language has standards that should be followed.
However, many databases implement specific features for their platform and as PDO does not abstract the SQL language, when we try to change we will have to deal with the differences.

How to enable PDO in PHP

The PDO main extension is already activated by default in PHP. What we actually need is to activate the DBMS driver extension that we will use.
When you open the php.ini file in the extensions section, you will probably see the following list of extensions with the prefix pdo_:

;extension=pdo_firebird
;extension=pdo_mysql
;extension=pdo_oci
;extension=pdo_odbc
;extension=pdo_pgsql
;extension=pdo_sqlite

If on Windows just uncomment the line, removing the ; from the front and restart the service. In Linux you need to check the distribution you are using how to do it, in Ubuntu for example, you could use apt-get to install and activate the database driver you are going to use:

sudo apt install php-sqlite3

for SQLite

sudo apt install php-pgsql

for PostgreSQL

sudo apt install php-mysql

for MySQL

Connecting

Create a Connection

To MySQL:

// open connection
$conn = new PDO("mysql:host={$host}; dbname={$name}; port={$port}", "{$user}", "{$pass}");
// set exception control
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

To PosgreSQL:

// open connection
$conn = new PDO("pgsql:dbname={$name}; user={$user}; password={$pass}; host={$host}; port={$port}");
// set exception control
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

To SQLite:

// open connection
$conn = new PDO("sqlite:{$file_name}");
// set exception control
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

PDO CRUD

Inserting a row into a table example

<?php

    $conn = new PDO("sqlite:database/pdo.db");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    try
    {
        $name  = 'Zero';
        $age = 23;

        $sql = "INSERT INTO person(name,age) VALUES(:name,:age)";

        $result = $conn->prepare($sql);

        $result->execute([
            ':name' => $name,
            ':age'  => $age,
        ]); 

        print "Person was inserted";

    }
    catch(Exception $e)
    {
        print $e->getMessage();
    }

How it works.

First, create a connect to the person database.
Next, construct an SQL INSERT statement. The :name and :age are the placeholder that will be replaced by a publisher name.
Then, prepare the INSERT statement for execution by calling the prepare() method of the $conn instance. The prepare() method returns an instance of the PDOStatement class.
After that, execute the prepared statement by passing the values to the execute() method.

Inserting multiple rows into a table example

<?php

    $conn = new PDO("sqlite:database/pdo.db");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    try
    {

        $person = [

            'name' => [
                'Zero',
                'Ace',
            ],

            'age' => [
                23,
                27,
            ],

        ];

        $sql = "INSERT INTO person(name,age) VALUES(:name,:age)";

        $result = $conn->prepare($sql);

        for ($i=0; $i < count($person) ; $i++) 
        { 
            $result->execute([
                ':name' => $person['name'][$i],
                ':age'  => $person['age'][$i],
            ]);
        }

    }
    catch(Exception $e)
    {
        print $e->getMessage();
    }

Updating data from PHP using PDO

<?php

    $conn = new PDO("sqlite:database/pdo.db");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    try
    {

        $person = [
            'id'   => 1,
            'name' => 'Zero',
            'age'  => 25,
        ];

        $sql = "UPDATE person SET age = :age WHERE id = :id";

        // prepare statement
        $result = $conn->prepare($sql);

        // bind params
        //PDO::PARAM_INT (int) Represents the SQL INTEGER data type.
        $result->bindParam(':id', $person['id'], PDO::PARAM_INT);
        $result->bindParam(':age', $person['age']);

        // execute the UPDATE statment
        if( $result->execute() )
        {
            echo 'The person age has been updated successfully!';
        }

    }
    catch(Exception $e)
    {
        print $e->getMessage();
    }

PDO Querying Data

<?php

    $conn = new PDO("sqlite:database/pdo.db");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    try
    {

        $sql = "SELECT * FROM person";

        $result = $conn->prepare($sql);
        $result->execute();
        $people = $result->fetchALL(PDO::FETCH_ASSOC);

        if( $people )
        {
            foreach( $people as $person )
            {
                print $person['name'] . ' ' . $person['age'] . '<br>';
            }
        }

    }
    catch(Exception $e)
    {
        print $e->getMessage();
    }

PDO Querying One Data

<?php

    $conn = new PDO("sqlite:database/pdo.db");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    try
    {

            $id = 2;

        $sql = "SELECT * FROM person WHERE id = :id";

        $result = $conn->prepare($sql);
        $result->bindParam(':id', $id, PDO::PARAM_INT);
        $result->execute();
        $person = $result->fetch(PDO::FETCH_ASSOC);

        if( $person )
        {
          print $person['name'] . ' ' . $person['age'] . '<br>';
        }

    }
    catch(Exception $e)
    {
        print $e->getMessage();
    }

Delete one row from a table

<?php

    $conn = new PDO("sqlite:database/pdo.db");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    try
    {

        $id = 2;

        // construct the delete statement
        $sql = "DELETE FROM person WHERE id = :id";

        // prepare the statement for execution
        $result = $conn->prepare($sql);
        $result->bindParam(':id', $id, PDO::PARAM_INT);

        // execute the statement
        if( $result->execute() )
        {
            print 'person id ' . $id . ' was deleted successfully.';
        }

    }
    catch(Exception $e)
    {
        print $e->getMessage();;
    }

Delete multiple rows from a table

<?php

    $conn = new PDO("sqlite:database/pdo.db");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    try
    {

            $id = 2;

        $sql = "DELETE FROM person WHERE id >= :id";

        $result = $conn->prepare($sql);
        $result->bindParam(':id', $id, PDO::PARAM_INT);

        if( $result->execute() )
        {
          print $result->rowCount() . ' row(s) was deleted successfully.' ;
        }

    }
    catch(Exception $e)
    {
        print $e->getMessage();
    }

Final considerations

PDO is extremely important in the PHP ecosystem, many applications use it directly to access relational databases and most applications use it without knowing it, through components such as Laravel's Eloquent and Doctrine's ORM.

Blog