Good content takes time and effort to come up with.

Please consider supporting us by just disabling your AD BLOCKER and reloading this page again.







https://stackcoder.in/Why Use PHP PDO With CRUD Examples


Why And How To Use PHP PDO With CRUD Examples


PHP PDO PHP MYSQL
21st May 2020 7 mins read
Share Article     Share On WhatsApp   Buy Me Coffee | StackCoder Buy Coffee


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


WHATS NEXT?


You might be interest to learn more on composer please find my whole article on it


How To Upload Image In PHP?

How To Uploads Files In PHP?

How To Install Packages Parallel For Faster Development In Composer

What Is Composer? How Does It Work? Useful Composer Commands And Usage

composer.json v/s composer.lock

Composer Install v/s Composer Update

Route Model Binding In Laravel & Change Default Column id To Another Column

How To Run Raw Queries Securely In Laravel

Laravel 7.x Multiple Database Connections, Migrations, Relationships & Querying

How To Install Apache Web Server On Ubuntu 20.04 / Linux & Manage It

How To Create / Save / Download PDF From Blade Template In PHP Laravel

How To Add Free SSL Certificate In cPanel With ZeroSSL & Certbot

How To Securely SSH Your Server & Push Files With FileZilla

How To Push Files To CPanel / Remote Server using FTP Software FileZilla

How To Install Linux, Apache, MYSQL, PHP (LAMP Stack) on Ubuntu

How To Cache Static Files With NGINX Server

Redirect www to a non-www website or vice versa

How To Create Free SSL Certificate With Lets Encrypt/Certbot In Linux (Single / Multiple Domains)

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

PHP Built-In Web Server & Testing Your Development Project In Mobile Without Any Software

How To Do Google reCAPTCHA Integration In PHP Laravel Forms


Happy Coding :)








Author Image
AUTHOR

Channaveer Hakari

I am a full-stack developer working at WifiDabba India Pvt Ltd. I started this blog so that I can share my knowledge and enhance my skills with constant learning.

Never stop learning. If you stop learning, you stop growing