Datatables CRUD with Ajax, PHP & MySQL Example Tutorial

1 Comment on Datatables CRUD with Ajax, PHP & MySQL Example Tutorial

To datatables crud operations using php, mysql, jQuery, ajax; Through this tutorial, you will learn how to implement crud operation project from MySQL database with modal form using jQuery dataTable + ajax in PHP MySQL. As well as learned to Insert update delete and view data without refreshing pages using PHP MySQL DataTables and ajax.

Through this tutorial, You can use jQuery DataTables to list records with a view add, edit, and delete records without page refresh from MySQL database using PHP + MySQL + ajax + DataTables js. And also you can download the source code of PHP MySQL dataTable crud source code.

jQuery Datatables Crud Operations using PHP, Ajax and Mysql Example Tutorial

Use the following steps, you can create simple php ajax crud with jQuery datatables and bootstrap modals:

  • Step 1 – Create Database And Table
  • Step 2 – Create List HTML page
  • Step 3 – Include Datatable Libraries in List Page
  • Step 4 – Create Add Edit Delete Record Ajax Function
  • Step 5 – Fetch data from Mysql DB and Display in Datatable List Page
  • Step 6 – Create Add Edit Delete Record From MySQL Database Function

Step 1 – Create Database And Table

First of all, open your PHPMyAdmin and execute the following MySQL query to create a database and table; as shown below:

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `created_at` datetime NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Recommended:-Insert Update Delete in PHP on Same Page

Step 2 – Create List HTML page

Create index.php file, which is used to display data from MySQL database using datatable js; so add the following code into your index.php file; as shown below:

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<title>Live Datatables CRUD with Ajax, PHP & MySQL - Tutsmake.com</title>

<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css"/>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>

<!-- DataTables JS library -->
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>

    <style type="text/css">
        .bs-example{
            margin: 20px;
        }
    </style>
</head>
<body>
    <div class="bs-example">
        <div class="container">
            <div class="row">
                <div class="col-md-12">
                    <div class="page-header clearfix">
                        <h2 class="float-left">Users List</h2>
                        <a href="javascript:void(0)" class="btn btn-primary float-right add-model"> Add User </a>
                    </div>

                   <table id="usersListTable" class="display" style="width:100%">
                        <thead>
                            <tr>
                                <th>Name</th>
                                <th>Email</th>
                                <th>Created</th>
                                <th>Action</th>
                            </tr>
                        </thead>
                        <tfoot>
                            <tr>
                                <th>Name</th>
                                <th>Email</th>
                                <th>Created</th>
                                <th>Action</th>

                            </tr>
                        </tfoot>
                  </table>
                </div>
            </div>
        </div>
    </div>
</body>

<div class="modal fade" id="edit-modal" aria-hidden="true">
  <div class="modal-dialog">
      <div class="modal-content">
          <div class="modal-header">
              <h4 class="modal-title" id="userCrudModal"></h4>
          </div>
          <div class="modal-body">
              <form id="update-form" name="update-form" class="form-horizontal">
                 <input type="hidden" name="id" id="id">
                  <input type="hidden" class="form-control" id="mode" name="mode" value="update">
                  <div class="form-group">
                      <label for="name" class="col-sm-2 control-label">Name</label>
                      <div class="col-sm-12">
                          <input type="text" class="form-control" id="name" name="name" placeholder="Enter Name" value="" maxlength="50" required="">
                      </div>
                  </div>

                  <div class="form-group">
                      <label class="col-sm-2 control-label">Email</label>
                      <div class="col-sm-12">
                          <input type="email" class="form-control" id="email" name="email" placeholder="Enter Email" value="" required="">
                      </div>
                  </div>
                  <div class="col-sm-offset-2 col-sm-10">
                   <button type="submit" class="btn btn-primary" id="btn-save" value="create">Save changes
                   </button>
                  </div>
              </form>
          </div>
          <div class="modal-footer">

          </div>
      </div>
  </div>
</div>

<div class="modal fade" id="add-modal" aria-hidden="true">
  <div class="modal-dialog">
      <div class="modal-content">
          <div class="modal-header">
              <h4 class="modal-title" id="userCrudModal"></h4>
          </div>
          <div class="modal-body">
              <form id="add-form" name="add-form" class="form-horizontal">
                 <input type="hidden" class="form-control" id="mode" name="mode" value="add">
                  <div class="form-group">
                      <label for="name" class="col-sm-2 control-label">Name</label>
                      <div class="col-sm-12">
                          <input type="text" class="form-control" id="name" name="name" placeholder="Enter Name" value="" maxlength="50" required="">
                      </div>
                  </div>

                  <div class="form-group">
                      <label class="col-sm-2 control-label">Email</label>
                      <div class="col-sm-12">
                          <input type="email" class="form-control" id="email" name="email" placeholder="Enter Email" value="" required="">
                      </div>
                  </div>
                  <div class="col-sm-offset-2 col-sm-10">
                   <button type="submit" class="btn btn-primary" id="btn-save" value="create">Save changes
                   </button>
                  </div>
              </form>
          </div>
          <div class="modal-footer">

          </div>
      </div>
  </div>
</div>

<script>
$(document).ready(function(){
    $('#usersListTable').DataTable({
        "processing": true,
        "serverSide": true,
        "order": [],
        "ajax": "fetch.php"
    });
});


  /*  add user model */
$('.add-model').click(function () {
    $('#add-modal').modal('show');
});

// add form submit
$('#add-form').submit(function(e){

    e.preventDefault();

    // ajax
    $.ajax({
        url:"add-edit-delete.php",
        type: "POST",
        data: $(this).serialize(), // get all form field value in serialize form
        success: function(){
            var oTable = $('#usersListTable').dataTable();
            oTable.fnDraw(false);
            $('#add-modal').modal('hide');
            $('#add-form').trigger("reset");
        }
    });
});

/* edit user function */
$('body').on('click', '.btn-edit', function () {
    var id = $(this).data('id');
     $.ajax({
        url:"add-edit-delete.php",
        type: "POST",
        data: {
            id: id,
            mode: 'edit'
        },
        dataType : 'json',
        success: function(result){
          $('#id').val(result.id);
          $('#name').val(result.name);
          $('#email').val(result.email);
          $('#edit-modal').modal('show');
        }
    });
});

// add form submit
$('#update-form').submit(function(e){

    e.preventDefault();

    // ajax
    $.ajax({
        url:"add-edit-delete.php",
        type: "POST",
        data: $(this).serialize(), // get all form field value in serialize form
        success: function(){
            var oTable = $('#usersListTable').dataTable();
            oTable.fnDraw(false);
            $('#edit-modal').modal('hide');
            $('#update-form').trigger("reset");
        }
    });
});

/* DELETE FUNCTION */
$('body').on('click', '.btn-delete', function () {
    var id = $(this).data('id');
    if (confirm("Are You sure want to delete !")) {
     $.ajax({
        url:"add-edit-delete.php",
        type: "POST",
        data: {
            id: id,
            mode: 'delete'
        },
        dataType : 'json',
        success: function(result){
            var oTable = $('#usersListTable').dataTable();
            oTable.fnDraw(false);
        }
     });
    }
    return false;
});

</script>
</html>
Recommended:-How to Fetch Data From Database in PHP using Ajax

Step 3 – Include Datatable Libraries

Include jQuery dataTable js libraries and bootstrap libraries into your index.php file; as shown below:

<!-- DataTables CSS library -->
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css"/>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA+058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonymous">

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/js/bootstrap.min.js" integrity="sha384-JZR6Spejh4U02d8jOt6vLEHfe/JQGiRRSQQxSfFWpi1MquVdAyjUar5+76PVCmYl" crossorigin="anonymous"></script>

<!-- DataTables JS library -->
<script type="text/javascript" src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
Recommended:-Get Country, City, latitude, longitude from IP address using PHP

Step 4 – Create Add Edit Delete Record Ajax Function

Create jQuery ajax functions to insert, update, delete data from MySQL database; as shown below: 

$(document).ready(function(){
    $('#usersListTable').DataTable({
        "processing": true,
        "serverSide": true,
        "order": [],
        "ajax": "fetch.php"
    });
});


  /*  add user model */
$('.add-model').click(function () {
    $('#add-modal').modal('show');
});

// add form submit
$('#add-form').submit(function(e){

    e.preventDefault();

    // ajax
    $.ajax({
        url:"add-edit-delete.php",
        type: "POST",
        data: $(this).serialize(), // get all form field value in serialize form
        success: function(){
            var oTable = $('#usersListTable').dataTable();
            oTable.fnDraw(false);
            $('#add-modal').modal('hide');
            $('#add-form').trigger("reset");
        }
    });
});

/* edit user function */
$('body').on('click', '.btn-edit', function () {
    var id = $(this).data('id');
     $.ajax({
        url:"add-edit-delete.php",
        type: "POST",
        data: {
            id: id,
            mode: 'edit'
        },
        dataType : 'json',
        success: function(result){
          $('#id').val(result.id);
          $('#name').val(result.name);
          $('#email').val(result.email);
          $('#edit-modal').modal('show');
        }
    });
});

// add form submit
$('#update-form').submit(function(e){

    e.preventDefault();

    // ajax
    $.ajax({
        url:"add-edit-delete.php",
        type: "POST",
        data: $(this).serialize(), // get all form field value in serialize form
        success: function(){
            var oTable = $('#usersListTable').dataTable();
            oTable.fnDraw(false);
            $('#edit-modal').modal('hide');
            $('#update-form').trigger("reset");
        }
    });
});

/* DELETE FUNCTION */
$('body').on('click', '.btn-delete', function () {
    var id = $(this).data('id');
    if (confirm("Are You sure want to delete !")) {
     $.ajax({
        url:"add-edit-delete.php",
        type: "POST",
        data: {
            id: id,
            mode: 'delete'
        },
        dataType : 'json',
        success: function(result){
            var oTable = $('#usersListTable').dataTable();
            oTable.fnDraw(false);
        }
     });
    }
    return false;
});

Step 5 – Fetch data from Mysql DB and Display in Datatable List Page

Create fetch.php file; which is used to fetch data from MySQL database using ajax and display it into your index.php file; as shown below:

<?php
// Database connection info
$dbDetails = array(
    'host' => 'localhost',
    'user' => 'root',
    'pass' => '',
    'db'   => 'demos'
);

// mysql db table to use
$table = 'users';

// Table's primary key
$primaryKey = 'id';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database.
// The `dt` parameter represents the DataTables column identifier.
$columns = array(
    array( 'db' => 'name', 'dt' => 0 ),
    array( 'db' => 'email',  'dt' => 1 ),
    array(
        'db'        => 'created_at',
        'dt'        => 2,
        'formatter' => function( $d, $row ) {
            return date( 'jS M Y', strtotime($row['created_at']));
        }
    ),
    array(
        'db'        => 'id',
        'dt'        => 3,
        'formatter' => function( $d, $row ) {
            return '<a href="javascript:void(0)" class="btn btn-primary btn-edit" data-id="'.$row['id'].'"> Edit </a> <a href="javascript:void(0)" class="btn btn-danger btn-delete ml-2" data-id="'.$row['id'].'"> Delete </a>';
        }
    )
);

// Include SQL query processing class
require 'ssp.class.php';

// Output data as json format
echo json_encode(
    SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns ));

Note that;- The SSP class handles the database related operations. It contains some helper functions to build SQL queries for DataTables server-side processing. You can download from this url :- https://github.com/tutsmake/Datatables-Server-side-Processing/blob/main/ssp.class.php.

Step 6 – Create Add Edit Delete Record From MySQL Database Function

Create a new file name add-edit-delete.php and inside this file create PHP functions with MySQL query to insert, update, delete data from MySQL database; as shown below:

<?php

// Database connection info
  $host='localhost';
  $username='root';
  $password='';
  $dbname = "demos";
  $conn=mysqli_connect($host,$username,$password,"$dbname");


if ($_POST['mode'] === 'add') {

     $name = $_POST['name'];
     $email = $_POST['email'];

     mysqli_query($conn, "INSERT INTO users (name,email)
     VALUES ('$name','$email')");

     echo json_encode(true);
}

if ($_POST['mode'] === 'edit') {

    $result = mysqli_query($conn,"SELECT * FROM users WHERE id='" . $_POST['id'] . "'");
    $row= mysqli_fetch_array($result);

     echo json_encode($row);
}

if ($_POST['mode'] === 'update') {

    mysqli_query($conn,"UPDATE users set  name='" . $_POST['name'] . "', email='" . $_POST['email'] . "' WHERE id='" . $_POST['id'] . "'");
    echo json_encode(true);
}

if ($_POST['mode'] === 'delete') {

     mysqli_query($conn, "DELETE FROM users WHERE id='" . $_POST["id"] . "'");
     echo json_encode(true);
}

?>

Conclusion

Datatables + php + jquery + ajax + bootstrap + mysql; Through this tutorial, you will learned how to create simple bootstrap crud (create, read, update and delete) datatable for database with modal form using jQuery + ajax in PHP MySQL.

Recommended PHP Tutorials

Recommended:-Ajax Image Upload Using PHP and jQuery Without Refreshing Page
Recommended:-Send Reset Password Link Email PHP
Crop and Save Image Using jQuery Croppie and PHP
Recommended:-PHP Google Places Autocomplete Example
Recommended:-PHP Dropzone File Upload Tutorial Example
Recommended:-PHP Signature Pad using Jquery Ajax Example
Recommended:-PHP 8 MySQL Ajax Live Search Autocomplete Example
Recommended:-PHP Google Firebase CRUD Example Tutorial
Recommended:-PHP Find Nearest Location using Latitude and Longitude Example
Recommended:-PHP Send Push Notification To Android & IOS using Google FCM
Recommended:-Replace First and Last Character From String PHP
Recommended:-How to Install PHP 8 on Ubuntu 20.04 using Terminal
Recommended:-PHP 8 Create PDF File From HTML Form Using Fpdf Example
Recommended:-Login and Registration Form in PHP + MySQL using XAMPP
Recommended:-PHP Sending Email (text/html)|PHP Mail() Function
Recommended:-Import CSV File into MySQL using PHP
Recommended:-Upload File to AWS S3 Bucket In PHP
Recommended:-Login with Facebook using PHP
Recommended:-PHP MySQL jQuery Ajax Pagination
Recommended:-Import CSV File Data into MySQL Database using PHP Ajax

If you have any questions or thoughts to share, use the comment form below to reach us.

Filed Under: Ajax, Bootstrap, Jquery, MySQL, PHP Tagged With:

AuthorDevendra Dode

Greetings, I'm Devendra Dode, a full-stack developer, entrepreneur, and the proud owner of Tutsmake.com. My passion lies in crafting informative tutorials and offering valuable tips to assist fellow developers on their coding journey. Within my content, I cover a spectrum of technologies, including PHP, Python, JavaScript, jQuery, Laravel, Livewire, CodeIgniter, Node.js, Express.js, Vue.js, Angular.js, React.js, MySQL, MongoDB, REST APIs, Windows, XAMPP, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL, and Bootstrap. Whether you're starting out or looking for advanced examples, I provide step-by-step guides and practical demonstrations to make your learning experience seamless. Let's explore the diverse realms of coding together.

One reply to Datatables CRUD with Ajax, PHP & MySQL Example Tutorial

  1. Taankyou

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *