Live Datatables CRUD with Ajax, PHP & MySQL

Datatables is a jQuery plguin which is used to display tables data in interactive and user friendly way. The Datatables are very feature rich as it allows searching, sorting and live data update features.

So if you’re thinking about implementing jQuery Datatables with CRUD operation with Ajax and PHP, then you’re here right place. In our previous tutorial you have learned how to create advance contact form with Ajax and PHP.In this tutorial you will learn how to implement live Datatables with CRUD operations with Ajax, PHP and MySQL.

We will cover this tutorial in easy steps to create live example of Datatables CRUD operations with dynamic data.

Also, read:

• jQuery Datatables Server Side Processing with PHP & MySQL

So let’s create implement live Datatables CRUD operations with Ajax, PHP & MySQL. The file structure of this example is following.

• index.php
• ajax.js
• process.php
• Emp.php

Step1: Create MySQL Database Table

As we will perform CRUD operation with Datatables, so we will create MySQL database table crud_emp to store data and perform CRUD actions.

CREATE TABLE `crud_emp` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `skills` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `gender` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Step2: Include Bootstrap, jQuery, Datatables and CSS Files

As we will handle design using Bootstrap framework, so we will need to include Bootstrap, jQuery, jQuery Datatables and CSS files.

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css">
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script>
<script src="js/jquery.dataTables.min.js"></script>
<script src="js/dataTables.bootstrap.min.js"></script>		
<link rel="stylesheet" href="css/dataTables.bootstrap.min.css" />
<script src="js/ajax.js"></script>

Step3: Display Employee List

In index.php, we will create Bootstrap table to display employee data.

<table id="empList" class="table table-bordered table-striped">
	<thead>
		<tr>
			<th>Emp ID</th>
			<th>Name</th>
			<th>Age</th>					
			<th>Skills</th>
			<th>Address</th>
			<th>Designation</th>					
			<th></th>
			<th></th>					
		</tr>
	</thead>
</table>

In ajax.js, we will make ajax request to process.php to action listEmp to load employee data to Datatables.

var empRecords = $('#empList').DataTable({
	"lengthChange": false,
	"processing":true,
	"serverSide":true,
	"order":[],
	"ajax":{
		url:"process.php",
		type:"POST",
		data:{action:'listEmp'},
		dataType:"json"
	},
	"columnDefs":[
		{
			"targets":[0, 6, 7],
			"orderable":false,
		},
	],
	"pageLength": 10
});		

In process.php, we will call employee method empList() to get employee list.

if(!empty($_POST['action']) && $_POST['action'] == 'listEmp') {
	$emp->empList();
}

In class Emp.php, we will define method empList() to get employee list an return as JSON data.

public function empList(){		
	$sqlQuery = "SELECT * FROM ".$this-<empTable." ";
	if(!empty($_POST["search"]["value"])){
		$sqlQuery .= 'where(id LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR name LIKE "%'.$_POST["search"]["value"].'%" ';			
		$sqlQuery .= ' OR designation LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR address LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR skills LIKE "%'.$_POST["search"]["value"].'%") ';			
	}
	if(!empty($_POST["order"])){
		$sqlQuery .= 'ORDER BY '.$_POST['order']['0']['column'].' '.$_POST['order']['0']['dir'].' ';
	} else {
		$sqlQuery .= 'ORDER BY id DESC ';
	}
	if($_POST["length"] != -1){
		$sqlQuery .= 'LIMIT ' . $_POST['start'] . ', ' . $_POST['length'];
	}	
	$result = mysqli_query($this-<dbConnect, $sqlQuery);
	$numRows = mysqli_num_rows($result);

        $sqlQueryTotal = "SELECT * FROM ".$this->empTable;
	$resultTotal = mysqli_query($this->dbConnect, $sqlQueryTotal);
	$numRowsTotal = mysqli_num_rows($resultTotal);

	$employeeData = array();	
	while( $employee = mysqli_fetch_assoc($result) ) {		
		$empRows = array();			
		$empRows[] = $employee['id'];
		$empRows[] = ucfirst($employee['name']);
		$empRows[] = $employee['age'];		
		$empRows[] = $employee['skills'];	
		$empRows[] = $employee['address'];
		$empRows[] = $employee['designation'];					
		$empRows[] = '>button type="button" name="update" id="'.$employee["id"].'" class="btn btn-warning btn-xs update"<Update>/button<';
		$empRows[] = '>button type="button" name="delete" id="'.$employee["id"].'" class="btn btn-danger btn-xs delete" <Delete>/button<';
		$employeeData[] = $empRows;
	}
	$output = array(
		"draw"	=>	intval($_POST["draw"]),			
		"iTotalRecords"	=> 	$numRows,
		"iTotalDisplayRecords"	=>  $numRowsTotal,
		"data"	=> 	$employeeData
	);
	echo json_encode($output);
}

Step4: Add Employee Record

We will design Bootstrap modal to add and edit employee record.

<div id="empModal" class="modal fade">
	<div class="modal-dialog">
		<form method="post" id="empForm">
			<div class="modal-content">
				<div class="modal-header">
					<button type="button" class="close" data-dismiss="modal">×</button>
					<h4 class="modal-title"><i class="fa fa-plus"></i> Edit User</h4>
				</div>
				<div class="modal-body">
					<div class="form-group"
						<label for="name" class="control-label">Name</label>
						<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="lastname" class="control-label">Skills</label>							
						<input type="text" class="form-control"  id="empSkills" name="empSkills" placeholder="Skills" required>							
					</div>	 
					<div class="form-group">
						<label for="address" class="control-label">Address</label>							
						<textarea class="form-control" rows="5" id="address" name="address"></textarea>							
					</div>
					<div class="form-group">
						<label for="lastname" class="control-label">Designation</label>							
						<input type="text" class="form-control" id="designation" name="designation" placeholder="Designation">			
					</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-default" data-dismiss="modal">Close</button>
				</div>
			</div>
		</form>
	</div>
</div>

We will handle modal form submit and ajax request to process.php with action addEmp to add new employee records.

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

In process.php, we will call method addEmp() on action addEmp to add new employee records.

if(!empty($_POST['action']) && $_POST['action'] == 'addEmp') {
	$emp->addEmp();
}

In class Emp.php, we will define method addEmp() to insert new employee record.

public function addEmp(){
	$insertQuery = "INSERT INTO ".$this->empTable." (name, age, skills, address, designation) 
		VALUES ('".$_POST["empName"]."', '".$_POST["empAge"]."', '".$_POST["empSkills"]."', '".$_POST["address"]."', '".$_POST["designation"]."')";
	$isUpdated = mysqli_query($this->dbConnect, $insertQuery);		
}

Step5: Update Employee Records

We will make ajax request with action getEmp to load employee to edit form.

$("#empList").on('click', '.update', function(){
	var empId = $(this).attr("id");
	var action = 'getEmp';
	$.ajax({
		url:'process.php',
		method:"POST",
		data:{empId:empId, action:action},
		dataType:"json",
		success:function(data){
			$('#empModal').modal('show');
			$('#empId').val(data.id);
			$('#empName').val(data.name);
			$('#empAge').val(data.age);
			$('#empSkills').val(data.skills);				
			$('#address').val(data.address);
			$('#designation').val(data.designation);	
			$('.modal-title').html("<i class='fa fa-plus'></i> Edit Employee");
			$('#action').val('updateEmp');
			$('#save').val('Save');
		}
	})
});

In process.php, we will call method $emp->getEmp() to load emp data.

public function getEmp(){
	if($_POST["empId"]) {
		$sqlQuery = "
			SELECT * FROM ".$this->empTable." 
			WHERE id = '".$_POST["empId"]."'";
		$result = mysqli_query($this->dbConnect, $sqlQuery);	
		$row = mysqli_fetch_array($result, MYSQL_ASSOC);
		echo json_encode($row);
	}
}

Then we will call method updateEmp() on edit form submit to update employee record.

public function updateEmp(){
	if($_POST['empId']) {	
		$updateQuery = "UPDATE ".$this->empTable." 
		SET name = '".$_POST["empName"]."', age = '".$_POST["empAge"]."', skills = '".$_POST["empSkills"]."', address = '".$_POST["address"]."' , designation = '".$_POST["designation"]."'
		WHERE id ='".$_POST["empId"]."'";
		$isUpdated = mysqli_query($this->dbConnect, $updateQuery);		
	}	
}

Step6: Delete Employee Record

We will handle employee record delete by making ajax request with action deleteEmp.

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

We will call method deleteEmp() on action deleteEmp to delete employee record.

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

You may also like:

• Follow and Unfollow System with PHP & MySQL
• GST Billing System with PHP & MySQL
• Restaurant Management System with PHP & MySQL
• Visitor Management System with PHP & MySQL
• Student Attendance System with PHP & MySQL
• Like and Dislike System with Ajax, PHP & MySQL
• Build Star Rating System with jQuery, PHP & MySQL
• User Registration and Login System with PHP & MySQL
• Build Contact Form with PHP and Ajax
• To-do List Application with Ajax, PHP & MySQL
• Build Event Calendar with jQuery , PHP & MySQL
• Create Dynamic Bar Chart with JavaScript, PHP & MySQL

You can view the live demo from the Demo link and can download the source from the Download link below.
Demo Download