PHP PDO CRUD Operations Using Bootstrap

I have been getting requested to post crud tutorial with PDO connection, so finally it’s here.

Get Complete Source Code

Tutorial Features:

  • Create, Read, Update and Delete Records
  • Bootstrap – for design and modal popup
  • jQuery – Used to handle ajax request

Tutorial Focus:

At the end of this tutorial you should be able to learn how to use php data object to perform crud operations. also you will learn how to use bootstrap modal popups with ajax request, I also created php library file with Object Oriented Programming, so overall there are many good practiced to learn from this tutorial.

Let’s get started

Step 1: Database Setup

So as always our first step is to setup database, go ahead and create database and add following table that we are going to need to perform our crud operations.

`users` table:

CREATE TABLE  `users` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`first_name` VARCHAR( 40 ) NOT NULL ,
`last_name` VARCHAR( 40 ) NOT NULL ,
`email` VARCHAR( 50 ) NOT NULL
) ENGINE = MYISAM ;
Users Table Structure

Step 2: Database connection file:

Go ahead and create new folder for your crud demo project and add `ajax/db_connection.php` file and use following code.

`ajax/db_connection.php:`

<?php

// DATABASE connection script  

// database Connection variables
define('HOST', 'localhost'); // Database host name ex. localhost
define('USER', 'root'); // Database user. ex. root ( if your on local server)
define('PASSWORD', ''); // Database user password  (if password is not set for user then keep it empty )
define('DATABASE', 'itech_crud'); // Database name
define('CHARSET', 'utf8');

function DB()
{
    static $instance;
    if ($instance === null) {
        $opt = array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES => FALSE,
        );
        $dsn = 'mysql:host=' . HOST . ';dbname=' . DATABASE . ';charset=' . CHARSET;
        $instance = new PDO($dsn, USER, PASSWORD, $opt);
    }
    return $instance;
}

?>

Make sure to change connection variable, in my case I have `itech_crud` database ready to use with no password set to the root user.

So in the above code we have `DB()` function which has global scope in the script, keep in mind if we include this file in other php file we can easily call DB() function to connect with your database.

Step 3: Create Library file with CRUD Functions:

In this tutorial I following little different flow for coding, from backend to front end, okay so library file is the core php file which is going to have class and function to perform operations.

Create new file called – `ajax/lib.php` and add following code:

`ajax/lib.php:`

<?php

require __DIR__ . '/db_connection.php';

class CRUD
{

    protected $db;

    function __construct()
    {
        $this->db = DB();
    }

    function __destruct()
    {
        $this->db = null;
    }

    /*
     * Add new Record
     *
     * @param $first_name
     * @param $last_name
     * @param $email
     * @return $mixed
     * */    public function Create($first_name, $last_name, $email)
    {
        $query = $this->db->prepare("INSERT INTO users(first_name, last_name, email) VALUES (:first_name,:last_name,:email)");
        $query->bindParam("first_name", $first_name, PDO::PARAM_STR);
        $query->bindParam("last_name", $last_name, PDO::PARAM_STR);
        $query->bindParam("email", $email, PDO::PARAM_STR);
        $query->execute();
        return $this->db->lastInsertId();
    }

    /*
     * Read all records
     *
     * @return $mixed
     * */    public function Read()
    {
        $query = $this->db->prepare("SELECT * FROM users");
        $query->execute();
        $data = array();
        while ($row = $query->fetch(PDO::FETCH_ASSOC)) {
            $data[] = $row;
        }
        return $data;
    }

    /*
     * Delete Record
     *
     * @param $user_id
     * */    public function Delete($user_id)
    {
        $query = $this->db->prepare("DELETE FROM users WHERE id = :id");
        $query->bindParam("id", $user_id, PDO::PARAM_STR);
        $query->execute();
    }

    /*
     * Update Record
     *
     * @param $first_name
     * @param $last_name
     * @param $email
     * @return $mixed
     * */    public function Update($first_name, $last_name, $email, $user_id)
    {
        $query = $this->db->prepare("UPDATE users SET first_name = :first_name, last_name = :last_name, email = :email  WHERE id = :id");
        $query->bindParam("first_name", $first_name, PDO::PARAM_STR);
        $query->bindParam("last_name", $last_name, PDO::PARAM_STR);
        $query->bindParam("email", $email, PDO::PARAM_STR);
        $query->bindParam("id", $user_id, PDO::PARAM_STR);
        $query->execute();
    }

    /*
     * Get Details
     *
     * @param $user_id
     * */    public function Details($user_id)
    {
        $query = $this->db->prepare("SELECT * FROM users WHERE id = :id");
        $query->bindParam("id", $user_id, PDO::PARAM_STR);
        $query->execute();
        return json_encode($query->fetch(PDO::FETCH_ASSOC));
    }
}

?>

Quick description on library file structure:

– `Database` – At the very top of the file we are using our database connection file that we are going to need through the library.

– `CRUD`: Class name which is having our methods collection.

– `__construct()` and `destruct()` method to initiate our database connection variable.

– `Create()`: To add new record in the database, it requires three basic parameters.

– `Read()` : To read all the records from the database

– `Delete()` : To delete record from the database table for particular User ID.

– `Update()` : As name says it used to update the record

– `Details()` : Use to get details for particular User ID.

For each and every method we are going to have call, let’s create request files to include library file and call method as needed.

Step 4: Ajax Request files:

`create.php:`  – Create new Record

<?php
if (isset($_POST['first_name']) && isset($_POST['last_name']) && isset($_POST['email'])) {
    require("lib.php");

    $first_name = $_POST['first_name'];
    $last_name = $_POST['last_name'];
    $email = $_POST['email'];

    $object = new CRUD();

    $object->Create($first_name, $last_name, $email);
}
?>

`read.php:` – Read all Records

<?php

require 'lib.php';

$object = new CRUD();

// Design initial table header
$data = '<table class="table table-bordered table-striped">
      <tr>
       <th>No.</th>
       <th>First Name</th>
       <th>Last Name</th>
       <th>Email Address</th>
       <th>Update</th>
       <th>Delete</th>
      </tr>';


$users = $object->Read();

if (count($users) > 0) {
    $number = 1;
    foreach ($users as $user) {
        $data .= '<tr>
    <td>' . $number . '</td>
    <td>' . $user['first_name'] . '</td>
    <td>' . $user['last_name'] . '</td>
    <td>' . $user['email'] . '</td>
    <td>
     <button >

`update.php:` – Update Reocrd

<?php
if (isset($_POST)) {
    require 'lib.php';

    $id = $_POST['id'];
    $first_name = $_POST['first_name'];
    $last_name = $_POST['last_name'];
    $email = $_POST['email'];

    $object = new CRUD();

    $object->Update($first_name, $last_name, $email, $id);
}

`delete.php:` – Delete Record

<?php
if (isset($_POST['id']) && isset($_POST['id']) != "") {
    require 'lib.php';
    $user_id = $_POST['id'];

    $object = new CRUD();
    $object->Delete($user_id);
}
?>

`details.php:` – Record Details

<?php
if (isset($_POST['id']) && isset($_POST['id']) != "") {
    require 'lib.php';
    $user_id = $_POST['id'];

    $object = new CRUD();

    echo $object->Details($user_id);
}
?>

If you have a look on above files you will see, we have really good way to call methods from lib file, we are easily including lib.php file, then we are creating Object of CRUD class to call methods.

Real easy right ?

So overall our backend work is done, now we just needs create front part. front part is going to have Bootstrap Design and jQuery Script to for ajax requests.

Step 5: Bootstrap Design:

Create `index.php` and add following code.

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>PHP CRUD Operations Using PDO Connection</title>

    <!-- Bootstrap CSS File  -->
    <link rel="stylesheet" type="text/css" href="bootstrap-3.3.5-dist/css/bootstrap.css"/>
</head>
<body>


<!-- Jquery JS file -->
<script type="text/javascript" src="js/jquery-1.11.3.min.js"></script>

<!-- Bootstrap JS file -->
<script type="text/javascript" src="bootstrap-3.3.5-dist/js/bootstrap.min.js"></script>

<!-- Custom JS file -->
<script type="text/javascript" src="js/script.js"></script>

</body>
</html>

– Download latest bootstrap file and add to the project from getbootstrap.com

– Download later jQuery file

– Create  `js/script.js` file

Make sure wit the paths for your newly added files in `src` as well as `href`.

Content Section:

Add following content section html code in the `index.php` file after `<body>` tag.

<!-- Content Section -->
<div class="container">
    <div class="row">
        <div class="col-md-12">
            <h1>PHP CRUD Operations Using PDO Connection</h1>
        </div>
    </div>
    <div class="row">
        <div class="col-md-12">
            <div class="pull-right">
                <button class="btn btn-success" data-toggle="modal" data-target="#add_new_record_modal">Add New Record</button>
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-md-12">
            <h3>Records:</h3>

            <div class="records_content"></div>
        </div>
    </div>
</div>
<!-- /Content Section -->

Add New Record Bootstrap modal popup:

<!-- Bootstrap Modals -->
<!-- Modal - Add New Record/User -->
<div class="modal fade" id="add_new_record_modal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                <h4 class="modal-title" id="myModalLabel">Add New Record</h4>
            </div>
            <div class="modal-body">

                <div class="form-group">
                    <label >


Update Details bootstrap modal popup:
<!-- Modal - Update User details -->
<div class="modal fade" id="update_user_modal" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
    <div class="modal-dialog" role="document">
        <div class="modal-content">
            <div class="modal-header">
                <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
                <h4 class="modal-title" id="myModalLabel">Update</h4>
            </div>
            <div class="modal-body">

                <div class="form-group">
                    <label >

Step 6: jQuery Script:

Please note: script needs to be there in `js/script.js` file. Add record function:
// Add Record
function addRecord() {
    // get values
    var first_name = $("#first_name").val();
    first_name = first_name.trim();
    var last_name = $("#last_name").val();
    last_name = last_name.trim();
    var email = $("#email").val();
    email = email.trim();

    if (first_name == "") {
        alert("First name field is required!");
    }
    else if (last_name == "") {
        alert("Last name field is required!");
    }
    else if (email == "") {
        alert("Email field is required!");
    }
    else {
        // Add record
        $.post("ajax/create.php", {
            first_name: first_name,
            last_name: last_name,
            email: email
        }, function (data, status) {
            // close the popup
            $("#add_new_record_modal").modal("hide");

            // read records again
            readRecords();

            // clear fields from the popup
            $("#first_name").val("");
            $("#last_name").val("");
            $("#email").val("");
        });
    }
}

Read Records:

// READ records
function readRecords() {
    $.get("ajax/read.php", {}, function (data, status) {
        $(".records_content").html(data);
    });
}

Get details:

function GetUserDetails(id) {
    // Add User ID to the hidden field
    $("#hidden_user_id").val(id);
    $.post("ajax/details.php", {
            id: id
        },
        function (data, status) {
            // PARSE json data
            var user = JSON.parse(data);
            // Assign existing values to the modal popup fields
            $("#update_first_name").val(user.first_name);
            $("#update_last_name").val(user.last_name);
            $("#update_email").val(user.email);
        }
    );
    // Open modal popup
    $("#update_user_modal").modal("show");
}

Update Record:

function UpdateUserDetails() {
    // get values
    var first_name = $("#update_first_name").val();
    first_name = first_name.trim();
    var last_name = $("#update_last_name").val();
    last_name = last_name.trim();
    var email = $("#update_email").val();
    email = email.trim();

    if (first_name == "") {
        alert("First name field is required!");
    }
    else if (last_name == "") {
        alert("Last name field is required!");
    }
    else if (email == "") {
        alert("Email field is required!");
    }
    else {
        // get hidden field value
        var id = $("#hidden_user_id").val();

        // Update the details by requesting to the server using ajax
        $.post("ajax/update.php", {
                id: id,
                first_name: first_name,
                last_name: last_name,
                email: email
            },
            function (data, status) {
                // hide modal popup
                $("#update_user_modal").modal("hide");
                // reload Users by using readRecords();
                readRecords();
            }
        );
    }
}

Delete Record:

function DeleteUser(id) {
    var conf = confirm("Are you sure, do you really want to delete User?");
    if (conf == true) {
        $.post("ajax/delete.php", {
                id: id
            },
            function (data, status) {
                // reload Users by using readRecords();
                readRecords();
            }
        );
    }
}

Read records on page load:

$(document).ready(function () {
    // READ records on page load
    readRecords(); // calling function
});

Finally if you run your code, you should be able perform crud operations.

Final Design:

Get Complete Source Code

If you have any issues using tutorial, do let me know in the comment section below.

Yogesh Koli

Software engineer & Blogger lives in India, has 6+ years of experience working with the front-end and back-end web app development.

View Comments

  • Uncaught ReferenceError: $ is not defined

    $(document).ready(function () {
    // READ records on page load
    readRecords(); // calling function
    });

    • You have not loaded jQuery Correctly, check and make sure jQuery library file is loaded in a right way.

  • @iyogeshkoli:disqus Hi, i followed all but altered all variables to match with my database. And when i try updating or creating the modal wont hide and I cant update / create data. What seems to be the problem.. Btw, this tutorial is awesome..

    • Hey @disqus_hfAhcM6i5S:disqus, Thanks for following up iTech Empires. You must be missing something into your JS code kindly checkout the console or checkout the PHP backend script to make sure it is correct according to the given instructions.

  • Great work, i have been trying to call PDO functions using Jqeury but it has been a hustle for me. thanks man

  • Yogesh Koli Sir i am migrating from procedural to oops and after going through many a PDO CRUD tutorials on net i stumbled upon this jewel of a s tutorial .....this has set me on right track and ajax facility is just pixie dust to top all the good stuff ......thank you very much ...lots of love and respect from Valley of Kashmir

  • I'm getting the following message on Firebug when clicking create a new record or editing one: TypeError: ativa is undefined.
    Does someone could help me on this?

  • This code working but you need to correct the directory of delete.php, details.php, read.php and update.php. The first time i read this article i don't understand they need to stay in ajax directory.
    Good joob :)

  • I am using your demo and it works (Product Master list and Category List) now my problem is I want to add a dropdown list-databound based in category List and also to add a checkbox to determine whether this product(item) is active or inactive.

Recent Posts

Complete guide of using Laravel 6 Eloquent Subquery Enhancements

Learn How to use laravel frameworks new improved feature called Eloquent Subquery and get example of using Eloquent Subqueries and…

4 weeks ago

3 Useful examples of using Array Map function in PHP – Best Practices

Learn how to use php array map function with easy and essential tutorial to modify arrays in php here I…

4 weeks ago

Working with PHP Array Filter Function – Best Practices

Learn how to use php array filter function with easy and essential tutorial to filter array in php here I…

4 weeks ago

How to add Access Modifiers with Constructor Parameters in TypeScript

Want to know how to refactor your Typescript class, Learn here utilising Typescript of the best and essential feature that…

4 weeks ago

What is Access Modifiers and how to use Access Modifiers in TypeScript ?

What is Access Modifiers in typescript, how to use Access Modifiers, when to use them, what are the advantage of…

1 month ago

Top 10 Super Useful Packages to Improve Laravel applications in 2019

This tutorial provide ultimate list of package those are top 10 on packagist and super useful to optimize your laravel…

1 month ago