Knowledgebase

Introduction

PHP Data Object (PDO) is a database abstraction layer that unifies access to different databases in your website or application. The library provides you with a low-level programming interface that makes your code clean and highly maintainable.

With the PDO library, you can seamlessly switch between different databases (for example, MySQL to SQLite) by tweaking your connection string. This makes you more productive because you don't have to re-write your SQL statements every time you migrate to a new database.

PDO also supports prepared statements that implement parameterized query placeholders instead of unsafe user-defined values. This comes in handy to protect your code against SQL injection attacks.

While the PDO Library's use sounds great, it is always recommended to create a centralized PHP class file that you can re-use in your entire project.

In this tutorial, you'll walk through the process of creating a centralized database access object class with PDO on Ubuntu 20.04.

Prerequisites

To follow along with this tutorial, ensure you have got the following:

• An Ubuntu 20.04 server.
• A sudo user.
• A Lamp Stack.

Create a Sample Database

SSH to your server and log in to MySQL as a root user.

$ sudo mysql -u root -p

Enter the root password of your MySQL server (don't confuse this with the root password of your server) and hit Enter to continue. Then, create a sample hotel database.

mysql> CREATE DATABASE hotel;

Next, create a non-root MySQL user to be used in your PHP scripts for the hotel database.

mysql> CREATE USER 'hotel_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'EXAMPLE_PASSWORD';
mysql> GRANT ALL PRIVILEGES ON hotel.* TO 'hotel_user'@'localhost';
mysql> FLUSH PRIVILEGES;

If you prefer to use MariaDB, use the command below:

MariaDB> GRANT ALL PRIVILEGES on hotel.* TO 'hotel_user'@'localhost' identified by 'EXAMPLE_PASSWORD';

Switch to the hotel database.

mysql> USE hotel;

Create a Table

For the basis of this tutorial, you'll create a single table that you can use to execute queries on your class. Run the code below to create a customers table.

mysql> CREATE TABLE customers (
       customer_id INT AUTO_INCREMENT PRIMARY KEY,
       first_name VARCHAR(50),
       last_name VARCHAR(50),
       phone VARCHAR(15)
       );

Next, insert some records into the customers table for testing purposes.

mysql> INSERT INTO customers (first_name, last_name, phone) VALUES ('JOHN', 'DOE', '11111');
mysql> INSERT INTO customers (first_name, last_name, phone) VALUES ('ROE', 'MARY', '22222');
mysql> INSERT INTO customers (first_name, last_name, phone) VALUES ('JANE', 'SMITH', '33333');

Ensure that the records were inserted into the customers table.

mysql> SELECT
       customer_id,
       first_name,
       last_name,
       phone
       FROM customers;

You should now see a list of all customers as shown below:

+-------------+------------+-----------+-------+
| customer_id | first_name | last_name | phone |
+-------------+------------+-----------+-------+
|           1 | JOHN       | DOE       | 11111 |
|           2 | ROE        | MARY      | 22222 |
|           3 | JANE       | SMITH     | 33333 |
+-------------+------------+-----------+-------+
3 rows in set (0.00 sec)

Up to this point, you've only created 3 records in the table. Later in this guide, you will use a PDO class you're about to create to query and insert new records to the table.

Exit from the MySQL command-line interface

mysql> exit;

Create a PDO Class

When coding in PHP, always use re-usable code to avoid expending all your efforts whenever there is a simple change in your application logic.

You'll create a single PDO database class in this guide that you can reference in multiple files across your entire project. If you change the database in the future, you'll need to change a single file, and the whole project will run as expected.

To create the class, use nano to open a blank DatabaseGateway.php file in your website's root directory.

$ sudo nano /var/www/html/DatabaseGateway.php

Next, name your class as DatabaseGateway You can use whatever name you want, but it is recommended to use a descriptive class name in UpperCamelCase. That is, if your class name has several words joined together, capitalize the first letter of each word.

Enter the information below to the /var/www/html/DatabaseGateway.php file.

<?php

class DatabaseGateway {
    public  $error    = '';
}

The public $error variable tracks any errors that might be encountered when interacting with your database.

Create a Database Connection Function

After you've defined the class, the next step is creating functions. You'll start by setting up a database connection function dbConnect inside the class DatabaseGateway {...} block. You may change the name of this function if you wish, but this time around, use camelCase for the name. This means capitalizing the first letter of each word except the first one.

    ...
    private function dbConnect()
    {
        try {

            $db_name     = 'hotel';
            $db_user     = 'hotel_user';
            $db_password = 'EXAMPLE_PASSWORD';
            $db_host     = 'localhost';

            $pdo = new PDO("mysql:host=" . $db_host  . ";dbname=" . $db_name, $db_user, $db_password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

            return $pdo;

        } catch(PDOException $e) {
            $this->error = $e->getMessage();
        }
    }
...

In the above code, you've defined the database variables, including the database name, database user, database password, and host. Then, you've initialized the PDO library using the new PDO(...) statement and created a $pdo object. Remember to enter the correct value for the EXAMPLE_PASSWORD

The $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); instructs PDO to throw an exception if any error is encountered in a database call.

You've also turned emulation off with the command $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); to return the correct data type for the columns when executing SELECT queries against the MySQL database.

The dbConnect function returns the $pdo object as indicated by the return $pdo; statement. You've also wrapped the entire code in a try {...}catch{...} block to handle errors and assign them as a string to the public $error variable.

Create a PDO Query Function

Still, in the class {...} block, enter a new query function.

    public function query($sql, $data = '')
    {
        try {

            $pdo  = $this->dbConnect();

            if ($this->error != '') {
                return $this->error;
            }

            $stmt = $pdo->prepare($sql);

            if (!empty($data)) {
                foreach ($data as $key => &$val) {
                   $stmt->bindParam($key, $val);
                }
            }

            $stmt->execute();
            $response = [];

            while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
                $response[] = $row;
            }

            $pdo = null;

            return $response;

        } catch(PDOException $e) {
        $this->error = $e->getMessage();
        }
    }

The function above takes two parameters. The $sql variable initializes a prepared SQL statement that uses placeholders, while the $data takes an array of data values to be executed along with the SQL command. The $data variable is optional; that's why you've set it to empty using the statement $data = '' if a query using the class function doesn't supply the value.

The line $pdo = $this->dbConnect(); retrieves a PDO object from the dbConnect() function that you've created earlier. if an error is encountered when retrieving the $pdo object and an error is raised, this error will be returned to the calling script.

To prepare a PDO statement, you've used the $stmt = $pdo->prepare($sql); command.

Next, you've used the PHP ...if () {...}... logical statement to check if there are valid parameters supplied into the function's $data variable when executing SELECT queries. Then, you've issued the command $stmt->bindParam($key, $val) statement to bind parameters to the already prepared PDO statement $stmt.

The function above only handles SELECT queries or the Read part in a CRUD(Create, Read, Update, Delete) application. Because you are incorporating all the database operations in the centralized PDO class, you will create a function to handle INSERT, UPDATE, and DELETE commands in the next step.

Create a PDO Execute Transaction Function

Next, create a PDO Execute function. This function will handle most database table operations. With the function, you'll be able to insert new records, update records with the right value,s and even delete entries that are no longer needed in your table.

Enter the information shown below inside the class DatabaseGateway{...} block.

    public function executeTransaction($sql, $data)
    {
        try {
            $pdo = $this->dbConnect();

            if ($this->error != '') {
                return $this->error;
            }

            try {
                $stmt = $pdo->prepare($sql);
                $stmt->execute($data);
            } catch(PDOException $e) {
                $this->error = $e->getMessage();
            }
        } catch(PDOException $e) {
           $this->error =  $e->getMessage();
        }
    }

The above executeTransaction function takes 2 parameters. The $sql variable is a parameterized query string with the command to execute, while the $data variable takes an array of values to be bound to the query. As indicated earlier in the guide, this is useful when safeguarding your application against SQL injections.

Next, the function above tries to execute dbConnect function to connect to the database, and if an error occurs, the above function returns the error to the calling script.

If no error is encountered, you've used the $pdo->prepare($sql); statement to prepare a statement and then executed it alongside the parameterized query data retrieved from the $data array.

When you've put all the variables and functions in place, your /var/www/html/DatabaseGateway.php file should now be similar to the content shown below.

<?php

class DatabaseGateway {
    public  $error    = '';

    private function dbConnect()
    {
        try {

            $db_name     = 'hotel';
            $db_user     = 'hotel_user';
            $db_password = 'EXAMPLE_PASSWORD';
            $db_host     = 'localhost';

            $pdo = new PDO("mysql:host=" . $db_host  . ";dbname=" . $db_name, $db_user, $db_password);
            $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

            return $pdo;

        } catch(PDOException $e) {
            $this->error = $e->getMessage();
        }
    }

    public function query($sql, $data = '')
    {
        try {

            $pdo  = $this->dbConnect();

            if ($this->error != '') {
                return $this->error;
            }

            $stmt = $pdo->prepare($sql);

            if (!empty($data)) {
                foreach ($data as $key => &$val) {
                   $stmt->bindParam($key, $val);
                }
            }

            $stmt->execute();
            $response = [];

            while (($row = $stmt->fetch(PDO::FETCH_ASSOC)) !== false) {
                $response[] = $row;
            }

            $pdo = null;

            return $response;

        } catch(PDOException $e) {
        $this->error = $e->getMessage();
        }
    }

    public function executeTransaction($sql, $data)
    {
        try {
            $pdo = $this->dbConnect();

            if ($this->error != '') {
                return $this->error;
            }

            try {
                $stmt = $pdo->prepare($sql);
                $stmt->execute($data);
            } catch(PDOException $e) {
                $this->error = $e->getMessage();
            }
        } catch(PDOException $e) {
           $this->error =  $e->getMessage();
        }
    }

}

Save and close the file, you'll now use that class in the next steps to execute commands in your database.

Use the PDO Class in your Project

With the DatabaseGateway class in place, you will use it to execute commands in the database in your PHP project.

Insert a Record Using the PDO Class

Create a new /var/www/html/insert_customers.php file. You'll use this file to create new records in the customers database.

$ sudo nano /var/www/html/insert_customers.php

Enter the information below into the file.

<?php

    require_once 'DatabaseGateway.php';

    $database_gateway = new DatabaseGateway();

    $sql = "insert into customers
           (
           first_name,
           last_name,
           phone
           )
           values
           (
           :first_name,
           :last_name,
           :phone
           )";

    $data = [
            'first_name' => 'BABY',
            'last_name'  => 'JACK',
            'phone'      => '44444'
            ];

    $database_gateway->executeTransaction($sql, $data);

    if ($database_gateway->error == '') {
        echo 'Record inserted succesfully';
    } else {
        echo 'Error encountered ' . $database_gateway->error;
    }

Save and close the file.

In the above file, you've used the statement require_once 'DatabaseGateway.php'; to load the DatabaseGateway class file. Next, you've initialized a new instance of the class using the $database_gateway = new DatabaseGateway(); statement.

You've also used an $sql variable to create an SQL command for inserting data to the customers table. You've implemented the named parameters(:first_name, :last_name, and :phone) in the query instead of the raw data you are inserting into the table. The $data array holds the actual values to be executed by your database class.

You've called the $database_gateway->executeTransaction($sql, $data); command to fire the executeTransaction function inside the DatabaseGateway class. Finally, you've examined the global variable $database_gateway->error from the DatabaseGateway class to check if there is an error; otherwise, you're echoing a success message.

Execute the /var/www/html/insert_customers.php file using curl.

$ curl localhost/insert_customers.php

You'll see a message that the record was inserted successfully into the database.

Record inserted successfully

Retrieve Multiple Records Using the PDO Class

Retrieve records from the customers' table by creating a new /var/www/html/query_customers.php using nano.

$ sudo nano /var/www/html/query_customers.php

Then, enter the content shown below into the file.

<?php

    header('Content-Type: application/json');

    require_once 'DatabaseGateway.php';

    $database_gateway = new DatabaseGateway();

    $sql = "select
            customer_id,
            first_name,
            last_name,
            phone
            from customers
           ";

    $response = $database_gateway->query($sql);

    if ($database_gateway->error == '') {
       echo json_encode($response, JSON_PRETTY_PRINT);
    } else {
        echo 'Error encountered ' . $database_gateway->error;
    }

Save and close the file when you're through with editing.

The header('Content-Type: application/json'); in the above code sets the content type. In this case, you've encoded the values returned to JSON.

You've then initialized a new DatabaseGateway class and created a new SQL statement to retrieve records from the database. Please note, in this example, you're calling the function query inside the DatabaseGateway class. Although the function takes two parameters($sql and $data), you defined the $data parameter as optional. In this case, you don't have to supply a value for the $data variable to retrieve all records from the customers table.

Run the /var/www/html/query_customers.php.

$ curl localhost/query_customers.php

You should get the output shown below.

[
    {
        "customer_id": 1,
        "first_name": "JOHN",
        "last_name": "DOE",
        "phone": "11111"
    },
    {
        "customer_id": 2,
        "first_name": "ROE",
        "last_name": "MARY",
        "phone": "22222"
    },
    {
        "customer_id": 3,
        "first_name": "JANE",
        "last_name": "SMITH",
        "phone": "33333"
    },
    {
        "customer_id": 4,
        "first_name": "BABY",
        "last_name": "JACK",
        "phone": "44444"
    }
]

Retrieve a Single Record Using the PDO Class

You can create a new file to query a single record from the customers table.

$ sudo nano /var/www/html/query_single_customer.php

This time, you'll need to supply a value for the $data variable with the customer_id as a parameter as shown in the below file.

<?php

    header('Content-Type: application/json');

    require_once 'DatabaseGateway.php';

    $database_gateway = new DatabaseGateway();

    $sql = "select
            customer_id,
            first_name,
            last_name,
            phone
            from customers
            where customer_id = :customer_id
           ";


    $data = [
             'customer_id' => 1
            ];

    $response = $database_gateway->query($sql, $data);

    if ($database_gateway->error == '') {
       echo json_encode($response, JSON_PRETTY_PRINT);
    } else {
        echo 'Error encountered ' . $database_gateway->error;
    }

Save and close the file. Then, execute it.

$ curl localhost/query_single_customer.php

You should now get a single record, as shown below.

[
    {
        "customer_id": 1,
        "first_name": "JOHN",
        "last_name": "DOE",
        "phone": "11111"
    }
]

Update Records Using the PDO Class

To update a record in the table, create an update file.

$ sudo nano /var/www/html/update_customer.php

Then enter the information below.

<?php

    header('Content-Type: application/json');

    require_once 'DatabaseGateway.php';

    $database_gateway = new DatabaseGateway();

    $sql = "update customers set
            first_name = :first_name
            where customer_id = :customer_id
           ";


    $data = [
             'customer_id' => 1,
             'first_name'  => 'JIMMIE'
            ];

    $response = $database_gateway->executeTransaction($sql, $data);

    if ($database_gateway->error == '') {
       echo "Record updated successfully";
    } else {
        echo 'Error encountered ' . $database_gateway->error;
    }

Save and close the file. Then execute it.

$ curl localhost/update_customer.php

Output.

Record updated successfully

The customer's name should now be changed from JOHN DOE TO JIMMIE DOE. You can confirm this by executing the /var/www/html/query_single_customer.php file again.

$ curl localhost/query_single_customer.php

As you can see below, the name has been updated.

 [
     {
        "customer_id": 1,
        "first_name": "JIMMIE",
        "last_name": "DOE",
        "phone": "11111"
    }
]

Delete a Record Using the PDO Class

To delete a record, create a new /var/www/html/delete_customer.php file

$ sudo nano /var/www/html/delete_customer.php

Enter the code below to the file

<?php

    header('Content-Type: application/json');

    require_once 'DatabaseGateway.php';

    $database_gateway = new DatabaseGateway();

    $sql = "delete from customers
            where customer_id = :customer_id
           ";


    $data = [
             'customer_id' => 2
            ];

    $response = $database_gateway->executeTransaction($sql, $data);

    if ($database_gateway->error == '') {
       echo "Record deleted successfully";
    } else {
        echo 'Error encountered ' . $database_gateway->error;
    }

Save and close the file. Then, execute the file,

$ curl localhost/delete_customer.php

After you run the file, you should see a success message showing that the record was deleted.

Record deleted successfully

When you execute the file, the code above deletes the record with customer_id number 2. You may confirm if the DELETE operation was executed successfully by running a SELECT statement against the customers table.

Log back to the MySQL database.

$ sudo mysql -u root -p

Enter your password to proceed. After the mysql> prompt appears, switch to the hotel database

mysql> USE hotel;

Then, select all records from the customers table.

mysql> SELECT
       customer_id,
       first_name,
       last_name,
       phone
       FROM customers;

The output below confirms that the record was deleted successfully because the customer_id 2 is missing from the list.

+-------------+------------+-----------+-------+
| customer_id | first_name | last_name | phone |
+-------------+------------+-----------+-------+
|           1 | JIMMIE     | DOE       | 11111 |
|           3 | JANE       | SMITH     | 33333 |
|           4 | BABY       | JACK      | 44444 |
+-------------+------------+-----------+-------+
3 rows in set (0.00 sec)

Conclusion

This tutorial is a complete walkthrough using PHP Data Objects and a centralized class for manipulating data in MySQL. You can extend the code snippets in this guide to further suit your programming needs.