AngularJS PHP Mysqli CRUD (Create, Read, Update and Delete) with Pagination, Search and Sort Bootstrap 5.1 Version
https://getbootstrap.com/docs/5.1/getting-started/introduction/
https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css
angularjs CDN
https://angularjs.org/ Version 1.8.2 https://ajax.googleapis.com/ajax/libs/angularjs/1.8.2/angular.min.js
dirPagination - AngularJS module for paginating
https://github.com/michaelbromley/angularUtils/tree/master/src/directives/pagination
https://github.com/michaelbromley/angularUtils/blob/master/src/directives/pagination/dirPagination.js
CREATE TABLE `employee` (
`id` int(11) NOT NULL,
`name` varchar(100) NOT NULL,
`position` varchar(100) NOT NULL,
`office` varchar(100) NOT NULL,
`age` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`created_at` date NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `employee` (`id`, `name`, `position`, `office`, `age`, `salary`, `created_at`) VALUES
(1, 'Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '2020-01-09'),
(2, 'Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '2020-02-15'),
(3, 'Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '2020-03-01'),
(4, 'cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '2020-01-24'),
(5, 'Rhona Davidson', 'Software Engineer', 'San Francisco', 26, 5465, '2020-01-11'),
(6, 'Quinn Flynn', 'Integration Specialist', 'New York', 53, 56465, '2020-02-23'),
(8, 'Tiger Nixon', 'Software Engineer', 'London', 45, 456, '2020-03-04'),
(9, 'Airi Satou updated', 'Pre-Sales Support updated', 'New York', 25, 4568, '2020-04-28'),
(10, 'Angelica Ramos updated', 'Sales Assistant updated', 'New York', 45, 456, '2020-01-12'),
(11, 'Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '2020-02-06'),
(12, 'Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '2020-03-21'),
(13, 'Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '2020-04-14'),
(14, 'Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '2020-01-29'),
(15, 'Bruno Nash', 'Customer Support', 'New York', 36, 65465, '2020-02-22'),
(16, 'cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '2020-03-10'),
(17, 'Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '2020-04-26'),
(18, 'Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '2020-01-17'),
(19, 'Sakura Yamamoto', 'Support Engineer', 'Tokyo', 48, 5468, '2020-02-01'),
(20, 'Serge Baldwin', 'Data Coordinator', 'Singapore', 85, 5646, '2020-03-19'),
(21, 'Shad Decker', 'Regional Director', 'Tokyo', 45, 4545, '2020-04-21');
ALTER TABLE `employee`
ADD PRIMARY KEY (`id`);
ALTER TABLE `employee`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=22;
index.php
//index.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>AngularJS PHP Mysqli CRUD with Pagination Search and Sort </title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-1BmE4kWBq78iYhFldvKuhfTAU6auU8tT94WrHftjDbrCEXSU1oBoqyl2QvZ6jIW3" crossorigin="anonymous">
<link href="https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css" rel="stylesheet">
<script src="https://ajax.googleapis.com/ajax/libs/angularjs/1.8.2/angular.min.js"></script>
<link rel="stylesheet" type="text/css" href="style.css">
</head>
<body ng-app="employeeapp" ng-controller="employeeCTR" ng-init="fetch()">
<div class="container">
<h1 class="page-header text-center">AngularJS PHP Mysqli CRUD (Create, Read, Update and Delete) with Pagination, Search and Sort</h1>
<div class="row">
<div class="col-md-12">
<div class="alert alert-success text-center" ng-show="success">
<button type="button" class="close" ng-click="clearMessage()"><span aria-hidden="true">×</span></button>
<i class="fa fa-check"></i> {{ successMessage }}
</div>
<div class="alert alert-danger text-center" ng-show="error">
<button type="button" class="close" ng-lick="clearMessage()"><span aria-hidden="true">×</span></button>
<i class="fa fa-warning"></i> {{ errorMessage }}
</div>
<div class="row">
<div class="col-md-12">
<button href="" class="btn btn-primary" ng-click="showAdd()"><i class="fa fa-plus"></i> Add New New Employee</button>
<span class="pull-right">
<input type="text" ng-model="search" class="form-control" placeholder="Search">
</span>
</div>
</div>
<table class="table table-bordered table-striped" style="margin-top:10px;">
<thead>
<tr>
<th ng-click="sort('name')" class="text-center">Name
<span class="pull-right">
<i class="fa fa-sort gray" ng-show="sortKey!='name'"></i>
<i class="fa fa-sort" ng-show="sortKey=='name'" ng-class="{'fa fa-sort-asc':reverse,'fa fa-sort-desc':!reverse}"></i>
</span>
</th>
<th ng-click="sort('position')" class="text-center">Position
<span class="pull-right">
<i class="fa fa-sort gray" ng-show="sortKey!='position'"></i>
<i class="fa fa-sort" ng-show="sortKey=='position'" ng-class="{'fa fa-sort-asc':reverse,'fa fa-sort-desc':!reverse}"></i>
</span>
</th>
<th ng-click="sort('office')" class="text-center">Office
<span class="pull-right">
<i class="fa fa-sort gray" ng-show="sortKey!='office'"></i>
<i class="fa fa-sort" ng-show="sortKey=='office'" ng-class="{'fa fa-sort-asc':reverse,'fa fa-sort-desc':!reverse}"></i>
</span>
</th>
<th class="text-center">Age</th>
<th class="text-center">Salary</th>
<th class="text-center">Action</th>
</tr>
</thead>
<tbody>
<tr dir-paginate="rs in employee|orderBy:sortKey:reverse|filter:search|itemsPerPage:5">
<td>{{ rs.name }}</td>
<td>{{ rs.position }}</td>
<td>{{ rs.office }}</td>
<td>{{ rs.age }}</td>
<td>$ {{ rs.salary }}</td>
<td>
<button type="button" class="btn btn-success" ng-click="showEdit(); selectEmployee(rs);"><i class="fa fa-edit"></i> Edit</button>
<button type="button" class="btn btn-danger" ng-click="showDelete(); selectEmployee(rs);"> <i class="fa fa-trash"></i> Delete</button>
</td>
</tr>
</tbody>
</table>
<div class="pull-right" style="margin-top:-30px;">
<dir-pagination-controls
max-size="5"
direction-links="true"
boundary-links="true" >
</dir-pagination-controls>
</div>
</div>
</div>
<?php include('modal.php'); ?>
</div>
<script src="dirPaginate.js"></script>
<script src="angular.js"></script>
<style>
.pagination {
display: inline-block;
padding-left: 0;
margin: 20px 0;
border-radius: 4px;
}
.pagination>li {
display: inline;
}
.pagination>li:first-child>a, .pagination>li:first-child>span {
margin-left: 0;
border-top-left-radius: 4px;
border-bottom-left-radius: 4px;
}
.pagination>.disabled>a, .pagination>.disabled>a:focus, .pagination>.disabled>a:hover, .pagination>.disabled>span, .pagination>.disabled>span:focus, .pagination>.disabled>span:hover {
color: #777;
cursor: not-allowed;
background-color: #fff;
border-color: #ddd;
}
.pagination>li>a, .pagination>li>span {
position: relative;
float: left;
padding: 6px 12px;
margin-left: -1px;
line-height: 1.42857143;
color: #337ab7;
text-decoration: none;
background-color: #fff;
border: 1px solid #ddd;
}
.pagination>.active>a, .pagination>.active>a:focus, .pagination>.active>a:hover, .pagination>.active>span, .pagination>.active>span:focus, .pagination>.active>span:hover {
z-index: 3;
color: #fff;
cursor: default;
background-color: #337ab7;
border-color: #337ab7;
}
</style>
</body>
</html>
angular.js
//angular.js
var app = angular.module('employeeapp', ['angularUtils.directives.dirPagination']);
app.controller('employeeCTR',function($scope, $http, $window){
$scope.AddModal = false;
$scope.EditModal = false;
$scope.DeleteModal = false;
$scope.errorname = false;
$scope.showAdd = function(){
$scope.name = null;
$scope.position = null;
$scope.office = null;
$scope.errorname = false;
$scope.errorposition = false;
$scope.erroroffice = false;
$scope.AddModal = true;
}
$scope.fetch = function(){
$http({
method: 'GET',
url: 'fetch.php',
}).then(function (data){
console.log(data)
$scope.employee = data.data;
},function (error){
console.log(error, 'can not get data.');
});
}
$scope.sort = function(keyname){
$scope.sortKey = keyname;
$scope.reverse = !$scope.reverse;
}
$scope.clearMessage = function(){
$scope.success = false;
$scope.error = false;
}
$scope.addnew = function(){
$http({
method: 'POST',
url: 'add.php',
data:{name:$scope.name, position:$scope.position, office:$scope.office}
}).then(function (data){
console.log(data)
if(data.data.name){
$scope.errorname = true;
$scope.errorposition = false;
$scope.erroroffice = false;
$scope.errorMessage = data.data.message;
$window.document.getElementById('name').focus();
}
else if(data.data.position){
$scope.errorname = false;
$scope.errorposition = true;
$scope.erroroffice = false;
$scope.errorMessage = data.data.message;
$window.document.getElementById('position').focus();
}
else if(data.data.office){
$scope.errorname = false;
$scope.errorposition = false;
$scope.erroroffice = true;
$scope.errorMessage = data.data.message;
$window.document.getElementById('office').focus();
}
else if(data.data.error){
$scope.errorname = false;
$scope.errorposition = false;
$scope.erroroffice = false;
$scope.error = true;
$scope.errorMessage = data.data.message;
}
else{
$scope.AddModal = false;
$scope.success = true;
$scope.successMessage = data.data.message;
$scope.fetch();
}
},function (error){
console.log(error, 'can not get data.');
});
}
$scope.selectEmployee = function(employee){
$scope.clickEmployee = employee;
}
$scope.showEdit = function(){
$scope.EditModal = true;
}
$scope.updateEmployee = function(){
$http({
method: 'POST',
url: 'edit.php',
data: $scope.clickEmployee
}).then(function (data){
console.log(data)
if(data.error){
$scope.error = true;
$scope.errorMessage = data.data.message;
$scope.fetch();
}
else{
$scope.success = true;
$scope.successMessage = data.data.message;
}
},function (error){
console.log(error, 'can not get data.');
});
}
$scope.showDelete = function(){
$scope.DeleteModal = true;
}
$scope.deleteEmployee = function(){
$http({
method: 'POST',
url: 'delete.php',
data: $scope.clickEmployee
}).then(function (data){
console.log(data)
if(data.data.error){
$scope.error = true;
$scope.errorMessage = data.data.message;
}
else{
$scope.success = true;
$scope.successMessage = data.data.message;
$scope.fetch();
}
},function (error){
console.log(error, 'can not get data.');
});
}
});
conn.php
//conn.php
<?php
$conn = new mysqli('localhost', 'root', '', 'testingdb');
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
fetch.php
//fetch.php
<?php
include('conn.php');
$output = array();
$sql = "SELECT * FROM employee";
$query=$conn->query($sql);
while($row=$query->fetch_array()){
$output[] = $row;
}
echo json_encode($output);
?>
modal.php
//modal.php
<!-- Add Modal -->
<div class="myModal" ng-show="AddModal">
<div class="modalContainer">
<div class="modalHeader">
<span class="headerTitle">Add New Employee</span>
<button class="closeBtn pull-right" ng-click="AddModal = false">×</button>
</div>
<div class="modalBody">
<div class="form-group">
<label>Name:</label>
<input type="text" class="form-control" ng-model="name" id="name">
<span class="pull-right input-error" ng-show="errorFirstname">{{ errorMessage }}</span>
</div>
<div class="form-group">
<label>Position:</label>
<input type="text" class="form-control" ng-model="position" id="position">
<span class="pull-right input-error" ng-show="errorposition">{{ errorMessage }}</span>
</div>
<div class="form-group">
<label>Office:</label>
<input type="text" class="form-control" ng-model="office" id="office">
<span class="pull-right input-error" ng-show="erroroffice">{{ errorMessage }}</span>
</div>
</div>
<hr>
<div class="modalFooter">
<div class="footerBtn pull-right">
<button class="btn btn-default" ng-click="AddModal = false"><span class="glyphicon glyphicon-remove"></span> Cancel</button> <button class="btn btn-primary" ng-click="addnew()"><span class="glyphicon glyphicon-floppy-disk"></span> Save</button>
</div>
</div>
</div>
</div>
<!-- Edit Modal -->
<div class="myModal" ng-show="EditModal">
<div class="modalContainer">
<div class="editHeader">
<span class="headerTitle">Edit Employee</span>
<button class="closeEditBtn pull-right" ng-click="EditModal = false">×</button>
</div>
<div class="modalBody">
<div class="form-group">
<label>Name:</label>
<input type="text" class="form-control" ng-model="clickEmployee.name">
</div>
<div class="form-group">
<label>Position:</label>
<input type="text" class="form-control" ng-model="clickEmployee.position">
</div>
<div class="form-group">
<label>Office:</label>
<input type="text" class="form-control" ng-model="clickEmployee.office">
</div>
</div>
<hr>
<div class="modalFooter">
<div class="footerBtn pull-right">
<button class="btn btn-default" ng-click="EditModal = false"><span class="glyphicon glyphicon-remove"></span> Cancel</button> <button class="btn btn-success" ng-click="EditModal = false; updateEmployee();"><span class="glyphicon glyphicon-check"></span> Save</button>
</div>
</div>
</div>
</div>
<!-- Delete Modal -->
<div class="myModal" ng-show="DeleteModal">
<div class="modalContainer">
<div class="deleteHeader">
<span class="headerTitle">Delete Employee</span>
<button class="closeDelBtn pull-right" ng-click="DeleteModal = false">×</button>
</div>
<div class="modalBody">
<h5 class="text-center">Are you sure you want to delete</h5>
<h2 class="text-center">{{clickEmployee.name}}</h2>
</div>
<hr>
<div class="modalFooter">
<div class="footerBtn pull-right">
<button class="btn btn-default" ng-click="DeleteModal = false"><span class="glyphicon glyphicon-remove"></span> Cancel</button> <button class="btn btn-danger" ng-click="DeleteModal = false; deleteEmployee(); "><span class="glyphicon glyphicon-trash"></span> Yes</button>
</div>
</div>
</div>
</div>
edit.php
//edit.php
<?php
include('conn.php');
$data = json_decode(file_get_contents("php://input"));
$out = array('error' => false);
$name = $data->name;
$position = $data->position;
$office = $data->office;
$id = $data->id;
$sql = "UPDATE employee SET name = '$name', position = '$position', office = '$office' WHERE id = '$id'";
$query = $conn->query($sql);
if($query){
$out['message'] = 'Employee updated Successfully';
}
else{
$out['error'] = true;
$out['message'] = 'Cannot update';
}
echo json_encode($out);
?>
delete.php
//delete.php
<?php
include('conn.php');
$data = json_decode(file_get_contents("php://input"));
$out = array('error' => false);
$id = $data->id;
$sql = "DELETE FROM employee WHERE id = '$id'";
$query = $conn->query($sql);
if($query){
$out['message'] = 'Employee deleted Successfully';
}
else{
$out['error'] = true;
$out['message'] = 'Cannot delete';
}
echo json_encode($out);
?>
add.php
//add.php
<?php
include('conn.php');
$data = json_decode(file_get_contents("php://input"));
$out = array('error' => false, 'name' => false, 'position' => false, 'office' => false);
$name = $data->name;
$position = $data->position;
$office = $data->office;
if(empty($name)){
$out['name'] = true;
$out['message'] = 'name is required';
}
elseif(empty($position)){
$out['position'] = true;
$out['message'] = 'position is required';
}
elseif(empty($office)){
$out['office'] = true;
$out['message'] = 'office is required';
}
else{
$sql = "INSERT INTO employee (name, position, office, age, salary) VALUES ('$name', '$position', '$office', '25', '99')";
$query = $conn->query($sql);
if($query){
$out['message'] = 'New Employee Added Successfully';
}
else{
$out['error'] = true;
$out['message'] = 'Cannot Add';
}
}
echo json_encode($out);
?>
style.css
//style.css
.gray{
color:gray;
}
.input-error{
font-size:12px;
color:#f44336;
}
.myModal{
position:fixed;
top:0;
left:0;
right:0;
bottom:0;
background: rgba(0, 0, 0, 0.4);
z-index:100;
}
.modalContainer{
width: 555px;
background: #FFFFFF;
margin:auto;
margin-top:10px;
}
.modalHeader{
padding:10px;
background: #008CBA;
color: #FFFFFF;
height:50px;
font-size:20px;
padding-left:15px;
}
.editHeader{
padding:10px;
background: #4CAF50;
color: #FFFFFF;
height:50px;
font-size:20px;
padding-left:15px;
}
.deleteHeader{
padding:10px;
background: #f44336;
color: #FFFFFF;
height:50px;
font-size:20px;
padding-left:15px;
}
.modalBody{
padding:30px;
}
.modalFooter{
height:36px;
}
.footerBtn{
margin-right:10px;
margin-top:-9px;
}
.closeBtn{
background: #008CBA;
color: #FFFFFF;
border:none;
}
.closeEditBtn{
background: #4CAF50;
color: #FFFFFF;
border:none;
}
.closeDelBtn{
background: #f44336;
color: #FFFFFF;
border:none;
}
VIDEO
