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 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.

<?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;
    }
}

/**
 * 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 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 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: 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.