How to write crud using PDO?

What is CRUD in php?

CRUD ( Create Read Update Delete ) are basic operations that are needed for web development. If your website uses some kind of database to store website related data then learning a CRUD operation is a basic task for any developer.

When you learn any new web programming language first thing you learn is CRUD operation. If you know how to create crud operation that you will be master at creating dynamic websites using any language.

In this tutorial, we will use php programming language and pdo php extension to create crud operations.

Create singleton class to connect database

Singleton classes are very important to learn. Singleton allow us to use the object without creating a class.

If you are creating php web app and you want to use the same database connection through out your web application rather then writing repeated code.

Let's create a single ton class that creates a database connection and returns pdo instance: 

<?php

class DBConnection {

    private static $instance = null;

    public static function get()
    {
        if(self::$instance === null)
        {
            self::$instance = new PDO('mysql:host=localhost;dbname=test', 'root',  'root');
        }

        return self::$instance;
    }
}


// get new pdo instance
$connection = DBConnection::get();

// check the variable to
// see if it has pdo object
var_dump($connection);​

Create category table

Open your sql editor and run following command to create a test database and category table: 

-- CREATE TEST DATABASE
CREATE DATABASE test;

-- CREATE TEST DATABASE TABLE
CREATE TABLE `test`.`category` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(85) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`));​

In our tutorial, we will use this new category table to perform crud operations using php and pdo.

How to write pdo singleton class

Let's first write a singleton class that creates a pdo connection for us. We want to write singleton class because we want to create our connection for the first time our code runs rather then creating multiple connection.

Singleton class makes sure that you will create connection only one time and re-use the same connection again and again. Let's write following class: 

<?php

class DBConnection {

    private static $instance = null;

    public static function get()
    {
        if(self::$instance === null)
        {
            self::$instance = new PDO('mysql:host=localhost;dbname=test', 'root',  'root');
        }

        return self::$instance;
    }
}

How to write crud using php and pdo?

Let's perform crud operation using php and pdo with our mysql database. We have created a new category table.

Using our singleton connection object we will use the same pdo instance over and over rather then creating diffrent pdo connections.

PDO delete operation 

<?php

include_once("DBConnection.php");

/**
 * Using singleton class
 * we created a db connection
 * without creating a class
 */
$dbh = DBConnection::get();

/**
 * PDO Delete Operation
 * --------------------
 *
 * You can delete all the records
 * from category table using query function
 * provided by pdo library
 */
$dbh->query('DELETE FROM category');
​

PDO Insert 

<?php

include_once("DBConnection.php");

/**
 * Using singleton class
 * we created a db connection
 * without creating a class
 */
$dbh = DBConnection::get();

/**
 * PDO Create Operation
 * --------------------
 *
 * Rather then writing too
 * many insert statements using
 * php arrays you can create data
 * that needs to be inserted in the
 * database and using foreach you can
 * create records in the table one by one
 */
$categories = [
    'PHP' => 'php tutorials',
    'MySQL' => 'mysql tutorials',
    'Laravel' => 'laravel tutorials'
];

foreach ($categories as $title => $description) {

    // prepare pdo statement to prevent sql injections
    $sth = $dbh->prepare('INSERT INTO category(`title`, `description`) VALUES(:title, :description)');

    // bind string parameters
    $sth->bindParam(':title', $title);
    $sth->bindParam(':description', $description);

    $sth->execute();
}

PDO Update 

?php

include_once("DBConnection.php");

/**
 * Using singleton class
 * we created a db connection
 * without creating a class
 */
$dbh = DBConnection::get();

/**
 * PDO: Update Operation
 * ---------------------
 *
 * You can modify existing database
 * record using update operation
 */
$existing_title  = 'PHP';
$new_description = 'Updated PHP tutorial description';

// let's update existing record using
// let's update new description for php
$sth = $dbh->prepare('UPDATE category SET `description` = :description WHERE `title` = :title');

$sth->bindParam(':title', $existing_title);
$sth->bindParam(':description', $new_description);

$sth->execute();

PDO Select 

<?php

include_once("DBConnection.php");

/**
 * Using singleton class
 * we created a db connection
 * without creating a class
 */
$dbh = DBConnection::get();

/**
 * PDO: Read Operation
 * ---------------------
 *
 * Using select query you can
 * fetch all the records from the
 * database and show them on console or web browser
 */
$records = $dbh->query('SELECT * FROM category');

// loop through all the records
// and shown them in console or web browser
foreach ($records as $record) {
    echo "{$record['title']}: {$record['description']}" .PHP_EOL;
}

Take the advantage of single ton class and through out your php application use the same database connection rather then creating database connection hundred times.

I hope this tutorial will help you with your routine crud tasks.

↑