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.
We will cover the following features in this Datatable Tutorial:
You will learn how to implement Live Add, Edit, and Delete DataTables Records with Ajax PHP and MySQL.
I am assuming, You have already created structure and added some code:
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!";
} Let’s add functionality to create a new record into MySQL and show it into the datatable listing.
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>
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');
}); 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();
}
})
}); 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);
} Let’s add functionality to update an existing record into MySQL and show it into the datatable listing.
I am using the same add a modal box to edit a record into the datatable.
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');
}
})
}); 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);
}
} Let’s add functionality to delete an existing record from MySQL and update the datatable listing.
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;
}
}); 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);
}
} 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.
This tutorial helps integrate a PHP SDK with Laravel. We'll install aws-php-sdk into laravel application and access all aws services… Read More
in this quick PHP tutorial, We'll discuss php_eol with examples. PHP_EOL is a predefined constant in PHP and represents an… Read More
This Laravel tutorial helps to understand table Relationships using Elequonte ORM. We'll explore laravel table Relationships usage and best practices… Read More
We'll explore different join methods of Laravel eloquent with examples. The join helps to fetch the data from multiple database… Read More
in this Laravel tutorial, We'll explore valet, which is a development environment for macOS minimalists. It's a lightweight Laravel development… Read More
I'll go through how to use soft delete in Laravel 10 in this post. The soft deletes are a method… Read More
View Comments
Hi
Thank your very much. The script is very good.
I had to make a correction. The variables were not visible.
Which address can I use for a donation with paypal?
have a nice day
Sapienti
:
class Employee extends Dbconfig {
protected $hostName;
protected $userName;
protected $password;
protected $dbName;
private $empTable = 'employee';
private $dbConnect = false;
public function __construct(){
if(!$this->dbConnect){
$database = new dbConfig();
$this -> hostName = $database -> serverName;
$this -> userName = $database -> userName;
$this -> password = $database ->password;
$this -> dbName = $database -> dbName;
// ->>>> $conn = new mysqli($this->hostName, $this->userName, $this->password, $this->dbName);
// does not work
$conn = new mysqli("localhost","root","","test"); //better!
if($conn->connect_error){
die("-". $this->hostName ."Error failed to connect to MySQL: " . $conn->connect_error);
} else{
$this->dbConnect = $conn;
}
}
}