CRUD Operations in PHP with PDO using Bootstrap

19:42 Php

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

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 ;

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 ;

IMAGE : Users Table Structure — 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;
}

?>

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

?>

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

<?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 onclick="GetUserDetails(' . $user['id'] . ')" class="btn btn-warning">Update</button>
    </td>
    <td>
     <button onclick="DeleteUser(' . $user['id'] . ')" class="btn btn-danger">Delete</button>
    </td>
      </tr>
';
        $number++;
    }
} else {
    // records not found
    $data .= '
<tr><td colspan="6">Records not found!</td></tr>
';
}

$data .= '</table>
';

echo $data;

?>

<?php

require 'lib.php';

$object = new CRUD();

// Design initial table header

$data = '<table class="table table-bordered table-striped">

<tr>

<th>First 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['email'] . '</td>

<td>

<button onclick="GetUserDetails(' . $user['id'] . ')" class="btn btn-warning">Update</button>

</td>

<td>

<button onclick="DeleteUser(' . $user['id'] . ')" class="btn btn-danger">Delete</button>

</td>

</tr>';

$number++;

}

} else {

// records not found

$data .= '<tr><td colspan="6">Records not found!</td></tr>';

}

$data .= '</table>';

echo $data;

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

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

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

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

<!DOCTYPE html>

<head>

<title>PHP CRUD Operations Using PDO Connection</title>

</head>

<body>

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

<h1>PHP CRUD Operations Using PDO Connection</h1>

</div>

<button class="btn btn-success" data-toggle="modal" data-target="#add_new_record_modal">Add New Record</button>

</div>

<h3>Records:</h3>

</div>

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">×</span></button>
                <h4 class="modal-title" id="myModalLabel">
Add New Record</h4>
</div>
<div class="modal-body">

                <div class="form-group">
                    <label for="first_name">First Name</label>
                    <input type="text" id="first_name" placeholder="First Name" class="form-control"/>
                </div>
<div class="form-group">
                    <label for="last_name">Last Name</label>
                    <input type="text" id="last_name" placeholder="Last Name" class="form-control"/>
                </div>
<div class="form-group">
                    <label for="email">Email Address</label>
                    <input type="text" id="email" placeholder="Email Address" class="form-control"/>
                </div>
</div>
<div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Cancel</button>
                <button type="button" class="btn btn-primary" onclick="addRecord()">Add Record</button>
            </div>
</div>
</div>
</div>
<!-- // Modal -->

<h4 class="modal-title" id="myModalLabel">Add New Record</h4>

</div>

<label for="first_name">First Name</label>

</div>

</div>

<label for="email">Email Address</label>

</div>

<button type="button" class="btn btn-default" data-dismiss="modal">Cancel</button>

<button type="button" class="btn btn-primary" onclick="addRecord()">Add Record</button>

</div>

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">×</span></button>
                <h4 class="modal-title" id="myModalLabel">
Update</h4>
</div>
<div class="modal-body">

                <div class="form-group">
                    <label for="update_first_name">First Name</label>
                    <input type="text" id="update_first_name" placeholder="First Name" class="form-control"/>
                </div>
<div class="form-group">
                    <label for="update_last_name">Last Name</label>
                    <input type="text" id="update_last_name" placeholder="Last Name" class="form-control"/>
                </div>
<div class="form-group">
                    <label for="update_email">Email Address</label>
                    <input type="text" id="update_email" placeholder="Email Address" class="form-control"/>
                </div>
</div>
<div class="modal-footer">
                <button type="button" class="btn btn-default" data-dismiss="modal">Cancel</button>
                <button type="button" class="btn btn-primary" onclick="UpdateUserDetails()" >Save Changes</button>
                <input type="hidden" id="hidden_user_id">
            </div>
</div>
</div>
</div>
<!-- // Modal -->

<h4 class="modal-title" id="myModalLabel">Update</h4>

</div>

<label for="update_first_name">First Name</label>

</div>

</div>

<label for="update_email">Email Address</label>

</div>

<button type="button" class="btn btn-default" data-dismiss="modal">Cancel</button>

<button type="button" class="btn btn-primary" onclick="UpdateUserDetails()" >Save Changes</button>

</div>

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("");
        });
    }
}

// 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);
    });
}

// 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");
}

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();
            }
        );
    }
}

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();
            }
        );
    }
}

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

$(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:
Records List

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

Tutorial Lancer

Popular Posts

Labels

CRUD Operations in PHP with PDO using Bootstrap

Tutorial Features:

Tutorial Focus:

Let’s get started

Step 1: Database Setup

Step 2: Database connection file:

Step 3: Create Library file with CRUD Functions:

Step 4: Ajax Request files:

Step 5: Bootstrap Design:

Step 6: jQuery Script: