Why And How To Use PHP PDO With CRUD Examples

In this article you will learn how to perform CRUD operations in PHP PDO.

We will cover

1. Ditch mysqli Functions And Go For PDO?
2. MYSQL Database Connection (Remains Standard For Other DB Supported Drivers)
3. MYSQL Database Setup
4. Understanding Position (?) & Named (:) Placeholders In PDO
5. Create Operation
6. Read Operation
7. Update Operation
8. Delete Operation

The code is available in GitHub repository PHP PDO CRUD Operation

---

Prerequisites

Basic knowledge of PHP.

---

1) Ditch mysqli Functions And Go For PDO?

First of all congrats if your using mysqli functions over mysql functions for your plain PHP project. Thats a good start.

PDO supports various of databases and you can switch as per you needs with very little efforts.

Now days everything is evolving and so does the working with multiple databases like MySQL, Postgres, SQLite, MongoDB, Cassandra to name few.

WHY PDO?

PDO provides a data-access abstraction layer, which means regardless of which database you're using, you use the same functions to issue queries and fetch data. 

PDO does not provide a database abstraction, it doesn't rewrite SQL or emulate missing features. You should use a full-blown abstraction layer if you need that facility.

NOTE: No where PDO replaces the the ORM framework like Doctrine, Propel

Protection Against SQL Injection

Other than the above thing it will also protects you from SQL Injection, but you have to use positional or named placeholders explained in 4th Point

---

2) MYSQL Database Connection

First check which of database drivers are installed in your computer or server with the following command

<?php
$pdoDrivers = PDO::getAvailableDrivers();
echo '<pre>';
print_r($pdoDrivers);

It will list all the databases installed in your computer and supported drivers for you like the following

Array
(
    [0] => mysql
    [1] => sqlite
    [2] => pgsql
)

The following is the setup for MYSQL database. In case if you have Postgres, SQLite or any other database then just change the $dsn& credentials. It should work fine for you.

db.php

<?php

$host           = 'localhost';
$db_name        = 'pdo_training';
$db_username    = 'root';
$db_password    = 'root';
$dsn            = 'mysql:host='. $host .';dbname='. $db_name;

$options        = [
                    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
                    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                    PDO::ATTR_EMULATE_PREPARES   => false,
                ];

try {
    $pdo = new PDO($dsn, $db_username, $db_password);
} catch (PDOException $e) {
    exit($e->getMessage());
}

---

3) MYSQL Database Setup

For the sake of demonstration I will be using MYSQL following is the database structure

NOTE: I will omit many fields as this is just for the sake of demonstration

In the below MYSQL Database design I have created pdo_training database & users table to perform CRUD operations.

 

/** Creating database with name invoice */
CREATE DATABASE `pdo_training`;

/** Once we create we want to switch to invoice so that we can add products table */
USE `pdo_training`;

/** Products table to hold the products data */
 CREATE TABLE `users` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `first_name` VARCHAR(30) NOT NULL,
    `last_name` VARCHAR(30),
    `email` VARCHAR(150) NOT NULL,
    `password` VARCHAR(250) NOT NULL,
    PRIMARY KEY (`id`)
 );

---

4) Understanding Position (?) & Named (:) Placeholders In PDO

Before proceeding with CRUD operations you need to understand the different between position & named placeholders in PDO

PHP PDO supports positional (?) and named (:email) placeholders, the latter (ie named) always begins from a colon and can be written using letters, digits and underscores only. Also note that no quotes have to be ever used around placeholders.

Eg:

The following becomes

$sql = "SELECT * FROM users WHERE email = '$email' AND status='$status'";

/** To */

$sql = 'SELECT * FROM users WHERE email = ? AND status=?';

/** OR */

$sql = 'SELECT * FROM users WHERE email = :email AND status=:status';

With placeholders, you have to prepare it, using the PDO::prepare() method

To get the query executed, you must run execute() method of this object, passing variables in it, in the form of array

Positional Parameters

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = ? AND status=?');
$stmt->execute([$email, $status]);
$user = $stmt->fetch();

Named Parameters

$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email AND status=:status');
$stmt->execute(['email' => $email, 'status' => $status]);
$user = $stmt->fetch();

Now you have some basic understanding of PDO lets begin with CRUD operations

---

NOTE: Thought the course I will be using named parameters.

5) Create Operation ie Writing To Database Table

First lets start inserting some records into the database users table.

In GitHub repository you will find the code in create.php

create.php

<?php
require_once './db.php';
try {
    /** Using prepare() function to prepare by query for execution */
    $userQuery = $pdo->prepare("
        INSERT INTO `users`
            (`first_name`, `last_name`, `email`, `password`)
        VALUES
            (:first_name, :last_name, :email, :password)
    ");

    /** Execute the prepared query with parameters values */
    $user = $userQuery->execute([
        ':first_name'   => 'Channaveer',
        ':last_name'    => 'Hakari',
        ':email'        => 'somemail@gmail.com',
        /** For the sake of demonstration I am using MD5, using hash for more secure */
        ':password'     => md5('password')
    ]);

    /** If any issues in inserting the data then it will throw exception */
    if (!$user) {
        throw new Exception('Error in adding user details');
    }

    $lastInsertedIs = $pdo->lastInsertId();
} catch (Exception $e) {
    /** Handle all your errors here */
    exit($e->getMessage());
}

---

6) Read Operation ie Get the Details From Database Table

Here we will see how to get specific row and all the rows from database table

Single Record Or By Condition

<?php
require_once './db.php';

/** Usually you do this */
// $userId = filter_input(INPUT_GET, 'user_id', FILTER_VALIDATE_INT);
/** For the sake of demo I am hard coding */
$userId = 1;
try {
    $userQuery = $pdo->prepare("
        SELECT 
            `id`, `first_name`, `last_name`, `email`, `password`
        FROM
            `users`
        WHERE
            `id` = :user_id
    ");
    $userQuery->execute([
        ':user_id'   => $userId,
    ]);

    $user = $userQuery->fetchObject();

    if (!$user) {
        throw new Exception('User details not found');
    }

    echo '<pre>';
    print_r($user);
} catch (Exception $e) {
    /** Handle all your errors here */
    exit($e->getMessage());
}

Observer the following line. It will only fetch the first row that it finds and in Standard Object

$user = $userQuery->fetch(PDO::FETCH_OBJ);

If you want to fetch in associative array then use the following

$user = $userQuery->fetch(PDO::FETCH_ASSOC);

You can use the one which your comfortable to use.

---

All Records From Table

$userQuery = $pdo->prepare("
    SELECT 
        `id`, `first_name`, `last_name`, `email`, `password`
    FROM
        `users`
");
$userQuery->execute();


$users = $userQuery->fetchAll(PDO::FETCH_OBJ);


if (!$users) {
    throw new Exception('User details not found');
}

Observe the following line. fetchAll pull all the records in Object

$users = $userQuery->fetchAll(PDO::FETCH_OBJ);

To pull all the records in Associative array then use the following

$users = $userQuery->fetchAll(PDO::FETCH_ASSOC);

---

7) Update Operation

Now lets update the record we have already having in our database with PDO

<?php
require_once './db.php';

/** Usually you do this */
// $userId = filter_input(INPUT_GET, 'user_id', FILTER_VALIDATE_INT);
/** For the sake of demo I am hard coding */
$userId = 1;
$firstName = 'Balaji';
$lastName = 'Vishwanath';
try {
    $userQuery = $pdo->prepare("
        UPDATE 
            `users`
        SET
            `first_name`    = :first_name,
            `last_name`     = :last_name
        WHERE
            `id` = :user_id
    ");
    $userQuery->execute([
        ':first_name'   => $firstName,
        ':last_name'    => $lastName,
        ':user_id'      => $userId
    ]);

    /** If no records were updated then it will throw exception */
    if ($userQuery->rowCount() < 1) {
        throw new Exception('No records updated.');
    }

    echo 'Updated successfully.';
} catch (Exception $e) {
    /** Handle all your errors here */
    exit($e->getMessage());
}

Observe the below code. Here we will get the update rows counts based on any updates.

if($userQuery->rowCount() < 1)

---

8) Delete Operation

Let's delete the record from the database table.

<?php
require_once './db.php';

/** Usually you do this */
// $userId = filter_input(INPUT_GET, 'user_id', FILTER_VALIDATE_INT);
/** For the sake of demo I am hard coding */
$userId = 2;
try {
    $userQuery = $pdo->prepare("
        DELETE FROM
            `users`
        WHERE
            `id` = :user_id
    ");
    $userQuery->execute([
        ':user_id'      => $userId
    ]);

    /** If no records were deleted then it will throw exception */
    if ($userQuery->rowCount() < 1) {
        throw new Exception('No records deleted.');
    }

    echo 'Deleted successfully.';
} catch (Exception $e) {
    /** Handle all your errors here */
    exit($e->getMessage());
}

Observe the below line, if any records deleted then it returns the rowCount.

if ($userQuery->rowCount() < 1) {

---

Conclusion

Hope you found the article very useful.

I have added code in GitHub PHP PDO CRUD Operation

Articles For You

What Is Laravel Resourceful Controllers?

SQLite Doesn't Support Dropping Foreign Keys in Laravel

Stripe Payment Integration With Laravel

Move Uploaded Files From Local Computer Or Server To Amazon S3 Bucket In PHP

Create A Composer Package? Test It Locally And Add To Packagist Repository

Sass or SCSS @function vs @mixin

NGINX Security Best Practices & Optimization

What Is HTTP? Different HTTP Methods And Status Codes Explained With Examples

Install Letsencrypt SSL Certificate for RabbitMQ Server and RabbitMQ Management Tool

Create Zip File On The Fly With Streaming Download In PHP Laravel

Install Linux, NGINX, MYSQL, PHP (LEMP Stack) on Ubuntu

Debugging Laravel Queue Email Issues

Laravel Named Routes

Setup MAMP Virtual Hosts For Local PHP Development

Send Email In PHP With PHPMailer