Datatables Add Edit Delete with Ajax, PHP & MySQL

Datatables Editable Add Delete with Ajax, PHP & MySQL-My programming school

by

Datatables Editable Add Delete with Ajax, PHP & MySQL. HTML tables are extensively used in internet functions to show information in rows and columns. The tables are simply HTML components and can solely be used to show information. But if you need HTML tables which can be interactive and have options like sorting, looking, pagination, then you’re at the best place right here.

Datatables Editable Row and Colum

In this tutorial you’ll study in regards to the jQuery datatables plugin for creating interactive HTML and wealthy HTML tables with dynamic information. We will create stay Datatables Editable, add and delete information with PHP and MySQL. We will refresh the datatables upon updating any information or deleting them from the desk.

We will cowl this tutorial step by making a stay instance of datatables editable, including and deleting information utilizing PHP and MySQL.

Datatables Editable Add Delete

Sonow we’ll begin to implementing Live Datatables editable, add and delete information with PHP and MySQL. Before we start, have a look on information construction for this instance.

  • index.php
  • information.js
  • motion.php
  • Employee.php: A class to carry worker technique.

How to Create MySQL Database Table

As we’ll add, edit and delete information, so first we’ll create MySQL database desk worker to carry out worker operations.

CREATE TABLE `worker` (
  `id` int(11) NOT NULL,
  `identify` varchar(255) NOT NULL,
  `abilities` varchar(255) NOT NULL,
  `handle` varchar(255) NOT NULL,
  `designation` varchar(255) NOT NULL,
  `age` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

How to Include Bootstrap, jQuery and Datatables information

In index.php file, we’ll embrace Bootstrap, jQuery and jQuery Datatables plugin information.

<hyperlink rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<hyperlink 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>		
<hyperlink rel="stylesheet" href="css/dataTables.bootstrap.min.css" />
<script src="js/data.js"></script>

How to Create Simple HTML Table

In index.php file, we’ll create HTML desk to listing worker information. The HTML Table will likely be transformed to Datatables with worker information listings.

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

How to Load Datatables with Employee Records

In the Data.js JavaScript file, we’ll initialize the datatables and load worker information by placing the AtAxt Employee into the Action List to return the JSON information to the Employee List technique Employee (Employee).

var workerData = $('#workerList').DataTable(
	"lengthChange": false,
	"processing":true,
	"serverSide":true,
	"order":[],
	"ajax":
		url:"action.php",
		sort:"POST",
		information:motion:'listingEmployee',
		dataType:"json"
	,
	"columnDefs":[
		
			"targets":[0, 6, 7],
			"orderable":false,
		,
	],
	"pageLength": 10
);

How to Get Employee List from MySQL Database Tables

In class Employee.php, we’ll create the strategy EmployeeList () to retrieve the Employee List as JSON information in keeping with the Datatables information request.

public function workerList()		
	$sqlQuery = "SELECT * FROM ".$this->empTable." ";
	if(!empty($_POST["search"]["value"]))
		$sqlQuery .= 'the place(id LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR identify LIKE "%'.$_POST["search"]["value"].'%" ';			
		$sqlQuery .= ' OR designation LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR handle LIKE "%'.$_POST["search"]["value"].'%" ';
		$sqlQuery .= ' OR abilities 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'];
		
	$outcome = mysqli_query($this->dbConnect, $sqlQuery);

        // Updated for pagination	
        $sqlQuery1 = "SELECT * FROM ".$this->empTable." ";
	$result1 = mysqli_query($this->dbConnect, $sqlQuery1);
	$numRows = mysqli_num_rows($result1);       
        
	$workerData = array();	
	while( $worker = mysqli_fetch_assoc($outcome) ) 		
		$empRows = array();			
		$empRows[] = $worker['id'];
		$empRows[] = ucfirst($worker['name']);
		$empRows[] = $worker['age'];		
		$empRows[] = $worker['skills'];	
		$empRows[] = $worker['address'];
		$empRows[] = $worker['designation'];					
		$empRows[] = '<button sort="button" identify="update" id="'.$employee["id"].'" class="btn btn-warning btn-xs update">Update</button>';
		$empRows[] = '<button sort="button" identify="delete" id="'.$employee["id"].'" class="btn btn-danger btn-xs delete" >Delete</button>';
		$workerData[] = $empRows;
	
	$output = array(
		"draw"				=>	intval($_POST["draw"]),
		"recordsTotal"  	=>  $numRows,
		"recordsFiltered" 	=> 	$numRows,
		"data"    			=> 	$workerData
	);
	echo json_encode($output);

How to Create HTML Form Modal to Add Update Datatables Records

In index.php file, we’ll create Bootstrap HTML mannequin so as to add and replace new information.

<div id="employeeModal" class="modal fade">
	<div class="modal-dialog">
		<kind technique="post" id="employeeForm">
			<div class="modal-content">
				<div class="modal-header">
					<button sort="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>
						<enter sort="text" class="form-control" id="empName" identify="empName" placeholder="Name" required>			
					</div>
					<div class="form-group">
						<label for="age" class="control-label">Age</label>							
						<enter sort="number" class="form-control" id="empAge" identify="empAge" placeholder="Age">							
					</div>	   	
					<div class="form-group">
						<label for="lastname" class="control-label">Skills</label>							
						<enter sort="text" class="form-control"  id="empSkills" identify="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" identify="address"></textarea>							
					</div>
					<div class="form-group">
						<label for="lastname" class="control-label">Designation</label>							
						<enter sort="text" class="form-control" id="designation" identify="designation" placeholder="Designation">			
					</div>						
				</div>
				<div class="modal-footer">
					<enter sort="hidden" identify="empId" id="empId" />
					<enter sort="hidden" identify="action" id="action" worth="" />
					<enter sort="submit" identify="save" id="save" class="btn btn-info" worth="Save" />
					<button sort="button" class="btn btn-default" data-dismiss="modal">Close</button>
				</div>
			</div>
		</kind>
	</div>
</div>

How to Handle Add New Employee Record

In information.js, we’ll deal with Bootstrap modal kind undergo submit kind and make Ajax request to submit information to motion addEmployee to name worker class technique addEmployee() to insert new worker information to database.

$("#employeeModal").on('submit','#workerForm', function(occasion)
	occasion.preventDefault();
	$('#save').attr('disabled','disabled');
	var kindData = $(this).serialize();
	$.ajax(
		url:"action.php",
		technique:"POST",
		information:kindData,
		success:function(information)				
			$('#workerForm')[0].reset();
			$('#workerModal').modal('disguise');				
			$('#save').attr('disabled', false);
			workerData.ajax.reload();
		
	)
);

How to Save New Employee Record to Database Table

In class Employee.php, we’ll create technique to insert worker file to desk worker.

public function addEmployee()
	$insertQuery = "INSERT INTO ".$this->empTable." (identify, age, abilities, handle, designation) 
		VALUES ('".$_POST["empName"]."', '".$_POST["empAge"]."', '".$_POST["empSkills"]."', '".$_POST["address"]."', '".$_POST["designation"]."')";
	$isUpdated = mysqli_query($this->dbConnect, $insertQuery);

How to Handle Employee Record Update

In information.js file, we’ll make Ajax request to motion getEmployee to get worker from database to show in worker edit kind modal to edit worker particulars.

$("#employeeList").on('click on', '.replace', function()
	var empId = $(this).attr("id");
	var motion = 'getEmployee';
	$.ajax(
		url:'motion.php',
		technique:"POST",
		information:empId:empId, motion:motion,
		dataType:"json",
		success:function(information)
			$('#workerModal').modal('present');
			$('#empId').val(information.id);
			$('#empName').val(information.identify);
			$('#empAge').val(information.age);
			$('#empSkills').val(information.abilities);				
			$('#handle').val(information.handle);
			$('#designation').val(information.designation);	
			$('.modal-title').html("<i class='fa fa-plus'></i> Edit Employee");
			$('#motion').val('replaceEmployee');
			$('#save').val('Save');
		
	)
);

How to Edit Save Employee Record

In class Employee.php, we’ll create technique replaceEmployee() to replace in database desk.

public function replaceEmployee()
	if($_POST['empId']) 	
		$updateQuery = "UPDATE ".$this->empTable." 
		SET identify = '".$_POST["empName"]."', age = '".$_POST["empAge"]."', abilities = '".$_POST["empSkills"]."', handle = '".$_POST["address"]."' , designation = '".$_POST["designation"]."'
		WHERE id ='".$_POST["empId"]."'";
		$isUpdated = mysqli_query($this->dbConnect, $updateQuery);

How to Handle Delete Employee Record

In the information.js file, we delete worker information by deleting an Ajax request to delete worker information and name deleteEmployee () to delete worker information.

$("#employeeList").on('click on', '.delete', function()
	var empId = $(this).attr("id");		
	var motion = "empDelete";
	if(affirm("Are you sure you want to delete this employee?")) 
		$.ajax(
			url:"action.php",
			technique:"POST",
			information:empId:empId, motion:motion,
			success:function(information) 					
				workerData.ajax.reload();
			
		)
	 else 
		return false;
	
);

How to Delete Employee Record from MySQL Database Table

In class Employee.php, we’ll create technique deleteEmployee() to create worker file from database desk.

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

Download Complete Source Code

Here you may obtain full supply code of Datatables Editable Add Delete with Ajax, PHP & MySQL. You can view the obtain the script from the Download hyperlink beneath.
Download

Pramod Kumar Yadav is from Janakpur Dham, Nepal. He was born on December 23, 1994, and has one elder brother and two elder sisters. He completed his education at various schools and colleges in Nepal and completed a degree in Computer Science Engineering from MITS in Andhra Pradesh, India. Pramod has worked as the owner of RC Educational Foundation Pvt Ltd, a teacher, and an Educational Consultant, and is currently working as an Engineer and Digital Marketer.

...



Best PYthon Course

Get More trending Courses

Leave a Comment