Ajax Datatable CRUD Operation Using PHP and MySQL

in this tutorial, We’ll learn about how to add add, edit, delete functionality using Bootstrap 5, PHP and MySQL.I am extending previous tutorial Ajax Pagination with Search and Sort.

We have already added functionality to listing, searching, and sorting into datatable, So Let’s add functionality to CRUD operation without page refresh.

We have already added the CRUD button into the previous tutorial, we just add into the functionality.

YouTube Video:

We will cover the following features in this Datatable Tutorial:

• Add Record into the Datatable
• Edit record into the Datatable
• Delete record from the datatable

jQuery Datatable CRUD Operation

You will learn how to implement Live Add, Edit, and Delete DataTables Records with Ajax PHP and MySQL.

There are following files will participate in this tutorial:

• index.php: The main entry file of the project.
• action.php: This file is used to define all actions.
• Employee.php: This file will contain all action methods.
• partials/container.php: This is the container file of the CRUD operation.
• partials/header.php: This file is the header file of the project.
• partials/footer.php: This file contains footer details of the project.

I am assuming, You have already created structure and added some code:

Create Actions

We’ll define all actions with method details and added below code into the action.php file:

include('Employee.php');
$emp = new Employee();
$action = isset($_POST['action']) && $_POST['action'] != '' ? $_POST['action'] : '';
switch ($action) {
	case "listEmployee":
		$emp->employeeList();
	  break;
	case "addEmployee":
		$emp->addEmployee();
	  break;
	case "getEmployee":
		$emp->getEmployee();
	  break;
	case "updateEmployee":
		$emp->updateEmployee();
	  break;
	case "empDelete":
		$emp->deleteEmployee();
	  break;
	default:
	  echo "Action found!";
  }

Ajax Add a Record into Datatable

Let’s add functionality to create a new record into MySQL and show it into the datatable listing.

Add A Record Into the Datatable

We’ll create an HTML view into the index.php file, I am using a modal box to add/edit a record. The below modal box HTML view, It’ll use for both types of operations: add and edit a record.

<div id="employeeModal" class="modal fade">
    <div class="modal-dialog">
        <form method="post" id="employeeForm">
            <div class="modal-content">
                <div class="modal-header">
                    <h4 class="modal-title"><i class="fa fa-plus"></i> Add/Edit User</h4>

                    <button type="button" class="close" data-bs-dismiss="modal">×</button>
                </div>

                <div class="modal-body">
                    <div class="form-group" <label="" for="name">
                        Name
                        <input type="text" class="form-control" id="empName" name="empName" placeholder="Name" required="" />
                    </div>

                    <div class="form-group">
                        <label for="age" class="control-label">Age</label>
                        <input type="number" class="form-control" id="empAge" name="empAge" placeholder="Age" />
                    </div>

                    <div class="form-group">
                        <label for="salary" class="control-label">Salary</label>
                        <input type="number" class="form-control" id="empSalary" name="empSalary" placeholder="Salary" />
                    </div>
                </div>

                <div class="modal-footer">
                    <input type="hidden" name="empId" id="empId" />
                    <input type="hidden" name="action" id="action" value="" />
                    <input type="submit" name="save" id="save" class="btn btn-info" value="Save" />
                    <button type="button" class="btn btn-danger" data-bs-dismiss="modal">Close</button>
                </div>
            </div>
        </form>
    </div>
</div>

Open Add Record Modal

Let’s open add a record modal box, when the user clicked on ‘add a record’ button.

$('#addEmployee').click(function(){
		$('#employeeModal').modal('show');
		$('#employeeForm')[0].reset();
		$('.modal-title').html("<i class="fa fa-plus"></i> Add Employee");
		$('#action').val('addEmployee');
		$('#save').val('Add');
	});

Submit Form Data Using Ajax

We’ll create an ajax request into the common.js file.

$("#employeeModal").on('submit','#employeeForm', function(event){
	event.preventDefault();
	$('#save').attr('disabled','disabled');
	var formData = $(this).serialize();
	$.ajax({
		url:"action.php",
		method:"POST",
		data:formData,
		success:function(data){				
			$('#employeeForm')[0].reset();
			$('#employeeModal').modal('hide');				
			$('#save').attr('disabled', false);
			employeeData.ajax.reload();
		}
	})
});

Created a Action Method

Now, I’ll add the action method into the employee.php file.

public function addEmployee(){
	$insertQuery = "INSERT INTO ".$this->empTable." (employee_name, employee_age, employee_salary) 
		VALUES ('".$_POST["empName"]."', '".$_POST["empAge"]."', '".$_POST["empSalary"]."')";
	$isUpdated = mysqli_query($this->dbConnect, $insertQuery);		
}

Ajax Update a Record into the Datatable

Let’s add functionality to update an existing record into MySQL and show it into the datatable listing.

HTML View for Update a Record Using AJAX

I am using the same add a modal box to edit a record into the datatable.

Submit Form Data Using Ajax

We’ll create an ajax request into the common.js file.

$("#dt-employee").on('click', '.update', function(){
	var empId = $(this).attr("id");
	var action = 'getEmployee';
	$.ajax({
		url:'action.php',
		method:"POST",
		data:{empId:empId, action:action},
		dataType:"json",
		success:function(data){
			$('#employeeModal').modal('show');
			$('#empId').val(data.id);
			$('#empName').val(data.employee_name);
			$('#empAge').val(data.employee_age);
			$('#empSalary').val(data.employee_salary);
			$('.modal-title').html("<i class="fa fa-plus"></i> Edit Employee");
			$('#action').val('updateEmployee');
			$('#save').val('Save');
		}
	})
});

Created a Action Method To Update Record

Now, I’ll update the action method into the employee.php file. Let’s create a MySQL query using the posted payloads data, after that run the query into the MySQL database.

public function updateEmployee(){
	if($_POST['empId']) {	
		$updateQuery = "UPDATE ".$this->empTable." 
		SET employee_name = '".$_POST["empName"]."', employee_age = '".$_POST["empAge"]."', employee_salary = '".$_POST["empSalary"]."'
		WHERE id ='".$_POST["empId"]."'";
		$isUpdated = mysqli_query($this->dbConnect, $updateQuery);		
	}	
}

Delete a Record from the Datatable

Let’s add functionality to delete an existing record from MySQL and update the datatable listing.

Submit Delete Request Using Ajax

We’ll create an ajax request into the common.js file, to delete a record from the database, the target id(emp_id) is passed as a parameter.

$("#dt-employee").on('click', '.delete', function(){
	var empId = $(this).attr("id");		
	var action = "empDelete";
	if(confirm("Are you sure you want to delete this employee?")) {
		$.ajax({
			url:"action.php",
			method:"POST",
			data:{empId:empId, action:action},
			success:function(data) {					
				employeeData.ajax.reload();
			}
		})
	} else {
		return false;
	}
});

Created a Action Method To Update Record

Add a delete action method into the employee.php file. This method will call from the above ajax request.

public function deleteEmployee(){
	if($_POST["empId"]) {
		$sqlDelete = "
			DELETE FROM ".$this->empTable."
			WHERE id = '".$_POST["empId"]."'";		
		mysqli_query($this->dbConnect, $sqlDelete);		
	}
}

Conclusion:

We have learned about datatable AJAX live add, edit and delete functionality. You can implement it in your project, it’s very simple and easy. We have used the latest version of datatable and bootstrap5 to implement datatable CRUD operation.