Friday 22 April 2022

Add dynamic rows in html table with Add, Edit, Delete feature in PHP and MySQLi

dynamic-table.php

<!DOCTYPE html>
<html lang="en-US">
<head>
<title> Dynamic Table in PhP</title>
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<link href="https://fonts.googleapis.com/css?family=Lato:400,700,300|Open+Sans:400,300,600,700" rel="stylesheet" />
<link rel="stylesheet" href="//code.jquery.com/ui/1.13.1/themes/base/jquery-ui.css">
<link href="../css/bootstrap.min.css?ver=3.3.5" rel="stylesheet" />
<link href="../css/font-awesome.min.css?ver=4.6.3" rel="stylesheet" />
<link rel="stylesheet" href="css/dataTables.bootstrap.min.css" />
</head>
<body>
<script src="../js/jquery-3.5.1.min.js"></script>
<script src="../js/jquery.dataTables.min.js"></script>
<script src="../js/bootstrap.min.js?ver=3.3.5"></script>
<script src="../js/jquery-ui.js"></script>
<script>
      $(document).ready(function(){
       $("#fromdate").datepicker({
                changeMonth: true,
                changeYear: true,
                dateFormat: 'dd/mm/yy'                          
             
            });
        $("#todate").datepicker({
                changeMonth: true,
                changeYear: true,
                dateFormat: 'dd/mm/yy'                          
             
            });
  var employeeData = $('#employeeList').DataTable({
"processing":true,
"serverSide":true,
"bFilter":false,
"bSort" : false ,
"bPaginate": false,
"bInfo" : false,
"order":[],
"ajax":{
url:"action.php",
type:"POST",
data:{action:'listEmployee'},
dataType:"json"
}
});
$('#addEmployee').click(function(){
$('#employeeModal').modal('show');
$('#employeeForm')[0].reset();
$('.modal-title').html("<i class='fa fa-plus'></i> Add Employment Details");
$('#action').val('addEmployee');
$('#save').val('Add');
});
$("#employeeList").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);
$('#post').val(data.post);
$('#company').val(data.company);
$('#type').val(data.type);
$('#fromdate').val(data.fromdate);
$('#todate').val(data.todate);
$('#scale').val(data.scale);
$('#gross').val(data.gross);
$('.modal-title').html("<i class='fa fa-plus'></i> Edit Employee");
$('#action').val('updateEmployee');
$('#save').val('Save');
}
})
});
$("#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();
}
})
});
$("#employeeList").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;
}
});
});
</script>
<div id="content" class="site-content">
<div class="container">
<div class="row">
<div class="col-md-12 padding-bottom-20">
<div class="row row-margin">
<div class="col-md-12">
<div class="container">
<div id="payment" style="display: block;">
<div style="overflow-x: auto;">
<div class="col-lg-10 col-md-10 col-sm-9 col-xs-12">   
<div class="panel-heading">
<div class="row">
<div class="col-md-10">
<h3 class="panel-title"></h3>
</div>
<div class="col-md-2" align="right">
<button type="button" name="add" id="addEmployee" class="btn btn-success btn-xs" style="font-size: 15px;padding: 10%;">Add New Row</button>
</div>
</div>
</div>
<table id="employeeList" class="table table-bordered table-striped" style="width:100%;">
        <thead>
<tr>
<th>Post held</th>
<th>Department/ Institute/ Company</th>
<th>Permanent/ Temporary/ Contract</th>
<th>From Date</th>
<th>To Date</th>
<th>Scale of pay</th>
<th>Gross Amount</th>
<th></th>
<th></th>
</tr>
</table>
</div>
<div id="employeeModal" class="modal fade">
    <div class="modal-dialog">
    <form method="post" id="employeeForm">
    <div class="modal-content">
    <div class="modal-header">
    <button type="button" class="close" data-dismiss="modal">&times;</button>
<h4 class="modal-title"><i class="fa fa-plus"></i> Edit Employment Details</h4>
    </div>
    <div class="modal-body">
<div class="form-group"
<label for="post" class="control-label">Post held</label>
<input type="text" class="form-control" id="post" name="post" placeholder="Post held" required>
</div>
<div class="form-group">
<label for="company" class="control-label">Department/ Institute/ Company</label>
<input type="text" class="form-control" id="company" name="company" placeholder="Department/ Institute/ Company">
</div>    
<div class="form-group">
<label for="type" class="control-label">Permanent/ Temporary/ Contract</label>
<input type="text" class="form-control"  id="type" name="type" placeholder="Permanent/ Temporary/ Contract" required="required">
</div>  
<div class="form-group">
<label for="fromdate" class="control-label">From Date [dd/mm/yyyy]</label>
<input type="text" class="form-control"  id="fromdate" name="fromdate" placeholder="From Date" autocomplete="off" readonly="readonly" required="required" ></textarea>
</div>
<div class="form-group">
<label for="todate" class="control-label">To Date [dd/mm/yyyy]</label>
<input type="text" class="form-control" id="todate" name="todate" placeholder="To Date" autocomplete="off" readonly="readonly" required="required" >
</div>
<div class="form-group">
<label for="scale" class="control-label">Scale of pay</label>
<input type="text" class="form-control" id="scale" name="scale" placeholder="Scale of pay" required="required" >
</div>
<div class="form-group">
<label for="gross" class="control-label">Gross Amount</label>
<input type="text" class="form-control" id="gross" name="gross" placeholder="Gross Amount" required="required" >
</div>
    </div>
    <div class="modal-footer">
    <input type="hidden" name="empId" id="empId" />
    <input type="hidden" name="userid" id="userid" value="12"/>   
    <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>
</div>
</div>
</div>
</div>
</div>
</div>
</div>
<!-- .col-md-9 --></div>
<!-- .row --></div>
<!-- .container --></div>

action.php

<?php
include('Employee.php');
$emp = new Employee();
if(!empty($_POST['action']) && $_POST['action'] == 'listEmployee') {
$emp->employeeList();
}
if(!empty($_POST['action']) && $_POST['action'] == 'addEmployee') {
$emp->addEmployee();
}
if(!empty($_POST['action']) && $_POST['action'] == 'getEmployee') {
$emp->getEmployee();
}
if(!empty($_POST['action']) && $_POST['action'] == 'updateEmployee') {
$emp->updateEmployee();
}
if(!empty($_POST['action']) && $_POST['action'] == 'empDelete') {
$emp->deleteEmployee();
}
?>

Config.php

<?php
class dbConfig {
    protected $serverName;
    protected $userName;
    protected $password;
    protected $dbName;
    function dbConfig() {
        $this -> serverName = 'localhost';
        $this -> userName = 'root';
        $this -> password = "";
        $this -> dbName = "employee";
    }
}
?>

Employee.php

<?php require('config.php'); class Employee extends Dbconfig { protected $hostName; protected $userName; protected $password; protected $dbName; private $empTable = 'employment'; 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); if($conn->connect_error){ die("Error failed to connect to MySQL: " . $conn->connect_error); } else{ $this->dbConnect = $conn; } } } public function employeeList(){ $sqlQuery = "SELECT * FROM ".$this->empTable." "; $result = mysqli_query($this->dbConnect, $sqlQuery); $employeeData = array(); while( $employee = mysqli_fetch_assoc($result) ) { $empRows = array(); $empRows[] = $employee['post']; $empRows[] = $employee['company']; $empRows[] = $employee['type']; $empRows[] = $employee['fromdate']; $empRows[] = $employee['todate']; $empRows[] = $employee['scale']; $empRows[] = $employee['gross']; $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"]), "data" => $employeeData ); echo json_encode($output); } public function getEmployee(){ if($_POST["empId"]) { $sqlQuery = " SELECT * FROM ".$this->empTable." WHERE id = '".$_POST["empId"]."'"; $result = mysqli_query($this->dbConnect, $sqlQuery); $row = mysqli_fetch_array($result, MYSQLI_ASSOC); echo json_encode($row); } } public function updateEmployee(){ if($_POST['empId']) { $updateQuery = "UPDATE ".$this->empTable." SET userid = '".$_POST["userid"]."', post = '".$_POST["post"]."', company = '".$_POST["company"]."', type = '".$_POST["type"]."', fromdate = '".$_POST["fromdate"]."' , todate = '".$_POST["todate"]."' , scale = '".$_POST["scale"]."', gross = '".$_POST["gross"]."' WHERE id ='".$_POST["empId"]."'"; $isUpdated = mysqli_query($this->dbConnect, $updateQuery); } } public function addEmployee(){ $insertQuery = "INSERT INTO ".$this->empTable." (userid, post, company, type, fromdate, todate,scale, gross) VALUES ('".$_POST["userid"]."','".$_POST["post"]."', '".$_POST["company"]."', '".$_POST["type"]."', '".$_POST["fromdate"]."', '".$_POST["todate"]."', '".$_POST["scale"]."', '".$_POST["gross"]."')"; $isUpdated = mysqli_query($this->dbConnect, $insertQuery); } public function deleteEmployee(){ if($_POST["empId"]) { $sqlDelete = " DELETE FROM ".$this->empTable." WHERE id = '".$_POST["empId"]."'"; mysqli_query($this->dbConnect, $sqlDelete); } } } ?>

employee.sql

CREATE TABLE `employment` (
  `id` int(10) NOT NULL,
  `userid` int(10) DEFAULT NULL,
  `post` varchar(100) DEFAULT NULL,
  `company` varchar(100) DEFAULT NULL,
  `type` varchar(25) DEFAULT NULL,
  `fromdate` varchar(25) DEFAULT NULL,
  `todate` varchar(25) DEFAULT NULL,
  `scale` varchar(50) DEFAULT NULL,
  `gross` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


Posted by at
Labels: ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)