PHP CRUD without Refresh/Reload using Ajax and DataTables Tutorial

Submitted by oretnom23 on Thursday, September 9, 2021 - 15:29.

In this tutorial, you will learn how to create or develop a PHP CRUD Operations w/o Pagre Refresh/Reloading using Ajax and DataTables. Here, we will enlist the data from the database using the server-side processing of DataTables. This tutorial is helpful especially for optimizing the page load of your future PHP Project.

The main goal of the simple web app that we will be creating is to display the data/records from the database into a table using DataTables which means it has a sort, filter, limit, and pagination functions. We will be using Ajax to process the new, updated, and delete data. Lastly, in each process, the table must update its data without leaving the current page/reloading/refreshing.

Getting Started

In this tutorial, I will be using the following

XAMPP as my local web-server to run my PHP scripts.
Bootstrap 5 for the user interface or the design of the web app
jQuery to use Ajax
DataTables andd please include the Button Extention

Compile the plugins/libraries files in a single foleder in your XAMPP's htdocs directory. Please open your XAMPP's Control Panel and start the Apache and MySQL.

Creating the Database

In your web browser, browse the PHPMyAdmin i.e. http://localhost/phpmyadmin and create a new database naming dummy_db. Then navigate to the database SQL Tab and paster the SQL Script below to create our database table and columns.

CREATE TABLE `authors` (
  `id` int(11) NOT NULL,
  `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  `birthdate` date NOT NULL,
  `added` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
ALTER TABLE `authors`
  ADD PRIMARY KEY (`id`);
 
ALTER TABLE `authors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=102;

Creating our Database Connection

In you source code folder, create a new PHP file naming connect.php. Copy/Paste the script below. The script will create a connection to our database.

<?php
$host = "localhost";
$username = "root";
$password = "";
$database = "dummy_db";
 
$conn = new mysqli($host, $username, $password, $database);
if(!$conn){
    echo "Database connection failed. Error:".$conn->error;
    exit;
}
?>

Creating the Interface

Create a new PHP file naming index.php. Paste the following script, to create our web app's interface. Please make sure that plugins/libraries path are correct in your end for both CSS Links and External JavaScript.

<!DOCTYPE html>
<html lang="en">
 
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>PHP CRUD using Ajax and DataTables</title>
    <link rel="stylesheet" href="css/bootstrap.css">
    <link rel="stylesheet" href="DataTables/datatables.min.css">
    <link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.0.0/css/buttons.dataTables.min.css">
    <script src="js/jquery-3.6.0.min.js"></script>
    <script src="js/bootstrap.js"></script>
    <script src="DataTables/datatables.min.js"></script>
    <script src="https://cdn.datatables.net/buttons/2.0.0/js/dataTables.buttons.min.js"></script>
    <script src="js/script.js"></script>
    <style>
        button.dt-button.btn-primary{
            background:var(--bs-primary)!important;
            color:white;
        }
    </style>
</head>
 
<body class="">
<nav class="navbar navbar-expand-lg navbar-light bg-dark bg-gradient">
  <div class="container">
    <a class="navbar-brand text-light text-shadow" href="//www.sourcecodester" target="_blank">SourceCodester</a>
  </div>
</nav>
 
    <div class="container py-5 h-100">
        <div class="row">
            <div class="col-lg-12">
                <h3 class="text-center"><b>PHP CRUD w/o Refresh using Ajax and DataTable</b></h3>
            </div>
        </div>
        <hr>
        <div class="row">
            <div class="col-md-12" id="msg"></div>
        </div>
        <div class="row">
            <div class="col-lg-12">
                <table class="table table-hover table-bordered table-striped" id="authors-tbl">
                    <thead>
                        <tr class="bg-dark text-light bg-gradient bg-opacity-150">
                            <th class="px-1 py-1 text-center">#</th>
                            <th class="px-1 py-1 text-center">First Name</th>
                            <th class="px-1 py-1 text-center">Last Name</th>
                            <th class="px-1 py-1 text-center">Email</th>
                            <th class="px-1 py-1 text-center">Birth Date</th>
                            <th class="px-1 py-1 text-center">Action</th>
                        </tr>
                    </thead>
                    <tfoot>
                        <tr class="bg-dark text-light bg-gradient bg-opacity-150">
                            <th class="px-1 py-1 text-center">#</th>
                            <th class="px-1 py-1 text-center">First Name</th>
                            <th class="px-1 py-1 text-center">Last Name</th>
                            <th class="px-1 py-1 text-center">Email</th>
                            <th class="px-1 py-1 text-center">Birth Date</th>
                            <th class="px-1 py-1 text-center">Action</th>
                        </tr>
                    </tfoot>
                </table>
            </div>
        </div>
    </div>
    <!-- Add Modal -->
    <div class="modal fade" id="add_modal" data-bs-backdrop="static">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Add Author</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                    <div class="container-fluid">
                        <form action="" id="new-author-frm">
                            <div class="form-group">
                                <label for="first_name" class="control-label">First Name</label>
                                <input type="text" class="form-control rounded-0" id="first_name" name="first_name" required>
                            </div>
                            <div class="form-group">
                                <label for="last_name" class="control-label">Last Name</label>
                                <input type="text" class="form-control rounded-0" id="last_name" name="last_name" required>
                            </div>
                            <div class="form-group">
                                <label for="email" class="control-label">Email</label>
                                <input type="text" class="form-control rounded-0" id="email" name="email" required>
                            </div>
                            <div class="form-group">
                                <label for="birthdate" class="control-label">Date of Birth</label>
                                <input type="date" class="form-control rounded-0" id="birthdate" name="birthdate" required>
                            </div>
                        </form>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="submit" class="btn btn-primary" form="new-author-frm">Save</button>
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                </div>
            </div>
        </div>
    </div>
    <!-- /Add Modal -->
    <!-- Edit Modal -->
    <div class="modal fade" id="edit_modal" data-bs-backdrop="static">
        <div class="modal-dialog">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Edit Author's Details</h5>
                    <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
                </div>
                <div class="modal-body">
                    <div class="container-fluid">
                        <form action="" id="edit-author-frm">
                            <input type="hidden" name="id">
                            <div class="form-group">
                                <label for="first_name" class="control-label">First Name</label>
                                <input type="text" class="form-control rounded-0" id="first_name" name="first_name" required>
                            </div>
                            <div class="form-group">
                                <label for="last_name" class="control-label">Last Name</label>
                                <input type="text" class="form-control rounded-0" id="last_name" name="last_name" required>
                            </div>
                            <div class="form-group">
                                <label for="email" class="control-label">Email</label>
                                <input type="text" class="form-control rounded-0" id="email" name="email" required>
                            </div>
                            <div class="form-group">
                                <label for="birthdate" class="control-label">Date of Birth</label>
                                <input type="date" class="form-control rounded-0" id="birthdate" name="birthdate" required>
                            </div>
                        </form>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="submit" class="btn btn-primary" form="edit-author-frm">Save</button>
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
                </div>
            </div>
        </div>
    </div>
    <!-- /Edit Modal -->
    <!-- Delete Modal -->
    <div class="modal fade" id="delete_modal" data-bs-backdrop="static">
        <div class="modal-dialog modal-dialog-centered">
            <div class="modal-content">
                <div class="modal-header">
                    <h5 class="modal-title">Confirm</h5>
                </div>
                <div class="modal-body">
                    <div class="container-fluid">
                        <form action="" id="delete-author-frm">
                            <input type="hidden" name="id">
                            <p>Are you sure to delete <b><span id="name"></span></b> from the list?</p>
                        </form>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="submit" class="btn btn-danger" form="delete-author-frm">Yes</button>
                    <button type="button" class="btn btn-primary" data-bs-dismiss="modal">No</button>
                </div>
            </div>
        </div>
    </div>
    <!-- /Delete Modal -->
</body>
</html>

Creating our JavaScript Functions

The script below contains all of the scripts and functions that initiating the processes of the CRUD Operations. It also includes the DataTables initiation. Save the file as script.js. In my case, the file is located inside the js directory inside my source code folder.

var authorsTbl = '';
$(function() {
    // draw function [called if the database updates]
    function draw_data() {
        if ($.fn.dataTable.isDataTable('#authors-tbl') && authorsTbl != '') {
            authorsTbl.draw(true)
        } else {
            load_data();
        }
    }
 
    function load_data() {
        authorsTbl = $('#authors-tbl').DataTable({
            dom: '<"row"B>flr<"py-2 my-2"t>ip',
            "processing": true,
            "serverSide": true,
            "ajax": {
                url: "./get_authors.php",
                method: 'POST'
            },
            columns: [{
                    data: 'id',
                    className: 'py-0 px-1'
                },
                {
                    data: 'first_name',
                    className: 'py-0 px-1'
                },
                {
                    data: 'last_name',
                    className: 'py-0 px-1'
                },
                {
                    data: 'email',
                    className: 'py-0 px-1'
                },
                {
                    data: 'birthdate',
                    className: 'py-0 px-1'
                },
                {
                    data: null,
                    orderable: false,
                    className: 'text-center py-0 px-1',
                    render: function(data, type, row, meta) {
                        console.log()
                        return '<a class="me-2 btn btn-sm rounded-0 py-0 edit_data btn-primary" href="javascript:void(0)" data-id="' + (row.id) + '">Edit</a><a class="btn btn-sm rounded-0 py-0 delete_data btn-danger" href="javascript:void(0)" data-id="' + (row.id) + '">Delete</a>';
                    }
                }
            ],
            drawCallback: function(settings) {
                $('.edit_data').click(function() {
                    $.ajax({
                        url: 'get_single.php',
                        data: { id: $(this).attr('data-id') },
                        method: 'POST',
                        dataType: 'json',
                        error: err => {
                            alert("An error occured while fetching single data")
                        },
                        success: function(resp) {
                            if (!!resp.status) {
                                Object.keys(resp.data).map(k => {
                                    if ($('#edit_modal').find('input[name="' + k + '"]').length > 0)
                                        $('#edit_modal').find('input[name="' + k + '"]').val(resp.data[k])
                                })
                                $('#edit_modal').modal('show')
                            } else {
                                alert("An error occured while fetching single data")
                            }
                        }
                    })
                })
                $('.delete_data').click(function() {
                    $.ajax({
                        url: 'get_single.php',
                        data: { id: $(this).attr('data-id') },
                        method: 'POST',
                        dataType: 'json',
                        error: err => {
                            alert("An error occured while fetching single data")
                        },
                        success: function(resp) {
                            if (!!resp.status) {
                                $('#delete_modal').find('input[name="id"]').val(resp.data['id'])
                                $('#delete_modal').modal('show')
                            } else {
                                alert("An error occured while fetching single data")
                            }
                        }
                    })
                })
            },
            buttons: [{
                text: "Add New",
                className: "btn btn-primary py-0",
                action: function(e, dt, node, config) {
                    $('#add_modal').modal('show')
                }
            }],
            "order": [
                [1, "asc"]
            ],
            initComplete: function(settings) {
                $('.paginate_button').addClass('p-1')
            }
        });
    }
    //Load Data
    load_data()
        //Saving new Data
    $('#new-author-frm').submit(function(e) {
            e.preventDefault()
            $('#add_modal button').attr('disabled', true)
            $('#add_modal button[form="new-author-frm"]').text("saving ...")
            $.ajax({
                url: 'save_data.php',
                data: $(this).serialize(),
                method: 'POST',
                dataType: "json",
                error: err => {
                    alert("An error occured. Please chech the source code and try again")
                },
                success: function(resp) {
                    if (!!resp.status) {
                        if (resp.status == 'success') {
                            var _el = $('<div>')
                            _el.hide()
                            _el.addClass('alert alert-primary alert_msg')
                            _el.text("Data successfulle saved");
                            $('#new-author-frm').get(0).reset()
                            $('.modal').modal('hide')
                            $('#msg').append(_el)
                            _el.show('slow')
                            draw_data();
                            setTimeout(() => {
                                _el.hide('slow')
                                    .remove()
                            }, 2500)
                        } else if (resp.status == 'success' && !!resp.msg) {
                            var _el = $('<div>')
                            _el.hide()
                            _el.addClass('alert alert-danger alert_msg form-group')
                            _el.text(resp.msg);
                            $('#new-author-frm').append(_el)
                            _el.show('slow')
                        } else {
                            alert("An error occured. Please chech the source code and try again")
                        }
                    } else {
                        alert("An error occurred. Please check the source code and try again")
                    }
 
                    $('#add_modal button').attr('disabled', false)
                    $('#add_modal button[form="new-author-frm"]').text("Save")
                }
            })
        })
        // Update Data
    $('#edit-author-frm').submit(function(e) {
            e.preventDefault()
            $('#edit_modal button').attr('disabled', true)
            $('#edit_modal button[form="edit-author-frm"]').text("saving ...")
            $.ajax({
                url: 'update_data.php',
                data: $(this).serialize(),
                method: 'POST',
                dataType: "json",
                error: err => {
                    alert("An error occured. Please chech the source code and try again")
                },
                success: function(resp) {
                    if (!!resp.status) {
                        if (resp.status == 'success') {
                            var _el = $('<div>')
                            _el.hide()
                            _el.addClass('alert alert-primary alert_msg')
                            _el.text("Data successfulle updated");
                            $('#edit-author-frm').get(0).reset()
                            $('.modal').modal('hide')
                            $('#msg').append(_el)
                            _el.show('slow')
                            draw_data();
                            setTimeout(() => {
                                _el.hide('slow')
                                    .remove()
                            }, 2500)
                        } else if (resp.status == 'success' && !!resp.msg) {
                            var _el = $('<div>')
                            _el.hide()
                            _el.addClass('alert alert-danger alert_msg form-group')
                            _el.text(resp.msg);
                            $('#edit-author-frm').append(_el)
                            _el.show('slow')
                        } else {
                            alert("An error occured. Please chech the source code and try again")
                        }
                    } else {
                        alert("An error occurred. Please check the source code and try again")
                    }
 
                    $('#edit_modal button').attr('disabled', false)
                    $('#edit_modal button[form="edit-author-frm"]').text("Save")
                }
            })
        })
        // DELETE Data
    $('#delete-author-frm').submit(function(e) {
        e.preventDefault()
        $('#delete_modal button').attr('disabled', true)
        $('#delete_modal button[form="delete-author-frm"]').text("deleting data ...")
        $.ajax({
            url: 'delete_data.php',
            data: $(this).serialize(),
            method: 'POST',
            dataType: "json",
            error: err => {
                alert("An error occured. Please chech the source code and try again")
            },
            success: function(resp) {
                if (!!resp.status) {
                    if (resp.status == 'success') {
                        var _el = $('<div>')
                        _el.hide()
                        _el.addClass('alert alert-primary alert_msg')
                        _el.text("Data successfulle updated");
                        $('#delete-author-frm').get(0).reset()
                        $('.modal').modal('hide')
                        $('#msg').append(_el)
                        _el.show('slow')
                        draw_data();
                        setTimeout(() => {
                            _el.hide('slow')
                                .remove()
                        }, 2500)
                    } else if (resp.status == 'success' && !!resp.msg) {
                        var _el = $('<div>')
                        _el.hide()
                        _el.addClass('alert alert-danger alert_msg form-group')
                        _el.text(resp.msg);
                        $('#delete-author-frm').append(_el)
                        _el.show('slow')
                    } else {
                        alert("An error occured. Please chech the source code and try again")
                    }
                } else {
                    alert("An error occurred. Please check the source code and try again")
                }
 
                $('#delete_modal button').attr('disabled', false)
                $('#delete_modal button[form="delete-author-frm"]').text("YEs")
            }
        })
    })
});

Creating Our PHP API's

The following PHP files are the codes that queries in our database. These are for fetching, adding, and deleting data in oue database. Save the files as the filename above each script.

get_authors.php

<?php 
require_once("./connect.php");
extract($_POST);
 
$totalCount = $conn->query("SELECT * FROM `authors` ")->num_rows;
$search_where = "";
if(!empty($search)){
    $search_where = " where ";
    $search_where .= " first_name LIKE '%{$search['value']}%' ";
    $search_where .= " OR last_name LIKE '%{$search['value']}%' ";
    $search_where .= " OR email LIKE '%{$search['value']}%' ";
    $search_where .= " OR date_format(birthdate,'%M %d, %Y') LIKE '%{$search['value']}%' ";
}
$columns_arr = array("id",
                     "first_name",
                     "last_name",
                     "email",
                     "unix_timestamp(birthdate)");
$query = $conn->query("SELECT * FROM `authors` {$search_where} ORDER BY {$columns_arr[$order[0]['column']]} {$order[0]['dir']} limit {$length} offset {$start} ");
$recordsFilterCount = $conn->query("SELECT * FROM `authors` {$search_where} ")->num_rows;
 
$recordsTotal= $totalCount;
$recordsFiltered= $recordsFilterCount;
$data = array();
$i= 1 + $start;
while($row = $query->fetch_assoc()){
    $row['no'] = $i++;
    $row['birthdate'] = date("F d, Y",strtotime($row['birthdate']));
    $data[] = $row;
}
echo json_encode(array('draw'=>$draw,
                       'recordsTotal'=>$recordsTotal,
                       'recordsFiltered'=>$recordsFiltered,
                       'data'=>$data
                       )
);

save_data.php

<?php 
require_once('connect.php');
extract($_POST);
 
$query = $conn->query("INSERT INTO `authors` (`first_name`,`last_name`,`email`,`birthdate`) VALUE ('{$first_name}','{$last_name}','{$email}','{$birthdate}')");
if($query){
    $resp['status'] = 'success';
}else{
    $resp['status'] = 'failed';
    $resp['msg'] = 'An error occured while saving the data. Error: '.$conn->error;
}
 
echo json_encode($resp);

get_single.php

<?php
require_once('connect.php');
extract($_POST);
$query = $conn->query("SELECT * FROM `authors` where id = '{$id}'");
if($query){
    $resp['status'] = 'success';
    $resp['data'] = $query->fetch_array();
}else{
    $resp['status'] = 'success';
    $resp['error'] = 'An error occured while fetching the data. Error: '.$conn->error;
}
echo json_encode($resp);

update_data.php

<?php 
require_once('connect.php');
extract($_POST);
 
$update = $conn->query("UPDATE `authors` set `first_name` = '{$first_name}', `last_name` = '{$last_name}', `email` = '{$email}',`birthdate` = '{$birthdate}' where id = '{$id}'");
if($update){
    $resp['status'] = 'success';
}else{
    $resp['status'] = 'failed';
    $resp['msg'] = 'An error occured while saving the data. Error: '.$conn->error;
}
 
echo json_encode($resp);

delete_data.php

<?php 
require_once('connect.php');
extract($_POST);
 
$delete = $conn->query("DELETE FROM `authors` where id = '{$id}'");
if($delete){
    $resp['status'] = 'success';
}else{
    $resp['status'] = 'failed';
    $resp['msg'] = 'An error occured while saving the data. Error: '.$conn->error;
}
 
echo json_encode($resp);

There you go. You can now test the web application that we've created on your end. If there's an error occurred on your end. Please review the scripts above. You can also download the working source code I created. The download button is located below.

DEMO

That's the end of this tutorial. Leave a comment below for any questions. Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding :)

Comments

View Label or Badge

Hello,
Congratulations on your "Crud" code
can you help me?
I have a field called "scadenza" "Date" format
I would like to view a label in case the date expired

example:
2021-08-31
View 2021-08-31 Expired
I tried to change script.js but I can't

ps: I sent a message copy of this in case you don't read

Hi, thank you for…

Hi, thank you for appreciating my work.
If my understanding is right, this could work:

<?php
// example query
$query = $conn->query("SELECT `scadenza` from `table_name`");
while($row = $query->fetch_assoc()){
    $expiry_date = strtotime($row['scandeza']);
    $current_date = strtotime(date("Y-m-d"));
    // Check if current date less than expiry date 'scadenza'
    if($expiry_date < $current_date){
        // Expired
        $is_expired = "Expired";
    }else{
        // Not Expired
        $is_expired = "";
    }
 
}
?>

I hope this will help. Thanks

Error

Hello... I apreciate your source code very much... I was trying your source code to input data. But whenever I try to input text containt single quotes (') in form, The form became error. Example word "it's me; your's" etc Can you please help explain why?

Hi Abdi Dalem, you can…

Hi Abdi Dalem, you can escape the strings you want to insert or update in the database.

For Example:

$query = $conn->query("INSERT INTO `authors` (`first_name`,`last_name`,`email`,`birthdate`) VALUE ('{$conn->real_escape_string($first_name)}','{$conn->real_escape_string($last_name)}','{$conn->real_escape_string($email)}','{$birthdate}')");

I hope this will help you. Thanks

Thank you very much for your…

Thank you very much for your answer. I am still learning :)

can not find table name to changes to my table's name

Hi, Thank you so much for the code. It is really useful for me to learn how to code in this style of CRUD. I have some question if you don't mind. I have an exist table and trying to load up data from my table. 1. Is there any place other than table name "authors" to change the name? 2. I am stuck on loading the data from the table. I think I didn't change the table name in the right places but I can not find anywhere else. 3. I am guessing when the page load up data, it is script.js does the job? Thank you very much.

Changes required for your table

Name of table 'authors' is in respective queries in seperate PHP files for add, update, delete etc - like "UPDATE 'authors' .. Change it to name of your table and your fields

Datatable cannot be initialised

Hi, thank you for your code. I am now referring your source code. I encounter this error: DataTables warning: table id=authors-tbl - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1 What can be done? I have my own table and new column. Thank you.

Same here

Log in or register to post comments
19379 views