PHP Class Mysql PDO CRUD(Create, Read, Update and Delete) with Search Functionality and Ajax Pagination
Bootstrap https://getbootstrap.com/docs/4.5/getting-started/introduction/
https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css
Modal
https://getbootstrap.com/docs/4.5/components/modal/
https://getbootstrap.com/docs/4.5/components/spinners/#size
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,
`photo` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `employee` (`id`, `name`, `position`, `office`, `age`, `salary`, `photo`) VALUES
(1, 'Tiger Wood', 'Accountant', 'Tokyo', 36, 5689, '15739efb98977a7540932d5b49dbf930.jpg'),
(2, 'Mark Oto Ednalan', 'Chief Executive Officer (CEO)', 'London', 56, 5648, '1.jpg'),
(3, 'Jacob thompson', 'Junior Technical Author', 'San Francisco', 23, 5689, '1.jpg'),
(4, 'cylde Ednalan', 'Software Engineer', 'Olongapo', 23, 54654, '1.jpg'),
(5, 'Rhona Davidson', 'Software Engineer', 'San Francisco', 26, 5465, '1.jpg'),
(6, 'Quinn Flynn', 'Integration Specialist', 'New York', 53, 56465, '1.jpg'),
(8, 'Tiger Nixon', 'Software Engineer', 'London', 45, 456, '1.jpg'),
(9, 'Airi Satou updated', 'Pre-Sales Support updated', 'New York', 25, 4568, '1.jpg'),
(10, 'Angelica Ramos updated', 'Sales Assistant updated', 'New York', 45, 456, '1.jpg'),
(11, 'Ashton updated', 'Senior Javascript Developer', 'Olongapo', 45, 54565, '1.jpg'),
(12, 'Bradley Greer', 'Regional Director', 'San Francisco', 27, 5485, '5.jpg'),
(13, 'Brenden Wagner', 'Javascript Developer', 'San Francisco', 38, 65468, '4.jpg'),
(14, 'Brielle Williamson', 'Personnel Lead', 'Olongapo', 56, 354685, '3.jpg'),
(15, 'Bruno Nash', 'Customer Support', 'New York', 36, 65465, '2.jpg'),
(16, 'cairocoders', 'Sales Assistant', 'Sydney', 45, 56465, '1.jpg'),
(17, 'Zorita Serrano', 'Support Engineer', 'San Francisco', 38, 6548, '5.jpg'),
(18, 'Zenaida Frank', 'Chief Operating Officer (COO)', 'San Francisco', 39, 545, '4.jpg'),
(19, 'Sakura Yamamoto', 'Support Engineer', 'Tokyo', 48, 5468, '3.jpg'),
(20, 'Serge Baldwin', 'Data Coordinator', 'Singapore', 85, 5646, '2.jpg'),
(21, 'Shad Decker', 'Regional Director', 'Tokyo', 45, 4545, '1.jpg');
ALTER TABLE `employee`
ADD PRIMARY KEY (`id`);
ALTER TABLE `employee`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;
//index.php
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>PHP Mysql PDO CRUD(Create, Read, Update and Delete) Search and Pagination with jQuery Ajax Bootstrap</title>
<link rel="stylesheet" href="css/style.css">
<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
</head>
<body>
<div class="container">
<div class="row text-center">
<h2>PHP Mysql PDO CRUD(Create, Read, Update and Delete) Search and Pagination with jQuery Ajax Bootstrap</h2>
</div>
<div class="alert alert-success text-center message" role="alert"></div>
<div class="row mb-3">
<div class="col-3">
<button type="button" class="btn btn-primary" data-toggle="modal" data-target="#employeeModal" id="addnewbtn">Add New </button>
</div>
<div class="col-9">
<div class="input-group input-group-lg">
<div class="input-group-prepend">
<span class="input-group-text" id="basic-addon2"><svg xmlns="http://www.w3.org/2000/svg" width="16" height="16" fill="currentColor" class="bi bi-search" viewBox="0 0 16 16">
<path d="M11.742 10.344a6.5 6.5 0 1 0-1.397 1.398h-.001c.03.04.062.078.098.115l3.85 3.85a1 1 0 0 0 1.415-1.414l-3.85-3.85a1.007 1.007 0 0 0-.115-.1zM12 6.5a5.5 5.5 0 1 1-11 0 5.5 5.5 0 0 1 11 0z"/>
</svg></span>
</div>
<input type="text" class="form-control" aria-label="Sizing example input" aria-describedby="inputGroup-sizing-lg" placeholder="Search..." id="searchinput">
</div>
</div>
</div>
<table class="table" id="employeetable">
<thead>
<tr>
<th scope="col"></th>
<th scope="col">Name</th>
<th scope="col">Position</th>
<th scope="col">Office</th>
<th scope="col">Age</th>
<th scope="col">Salary</th>
<th scope="col"></th>
</tr>
</thead>
<tbody>
</tbody>
</table>
<nav id="pagination"></nav>
<input type="hidden" name="currentpage" id="currentpage" value="1">
<?php
//modal
include_once 'formModal.php';
include_once 'viewModal.php';
?>
</div>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.0/js/bootstrap.min.js"></script>
<script src="js/script.js"></script>
<div id="overlay" style="display:none;">
<div class="spinner-border text-danger" style="width: 3rem; height: 3rem;"></div>
<br />
Loading...
</div>
</body>
</html>
formModal.php
//formModal.php
<!-- add/edit form modal -->
<div class="modal fade" id="employeeModal" tabindex="-1" role="dialog" aria-labelledby="employeeModalLabel" aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Add/Edit Employee</h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<form id="addform" method="POST" enctype="multipart/form-data">
<div class="modal-body">
<div class="form-group">
<label for="recipient-name" class="col-form-label">Name:</label>
<div class="input-group mb-3">
<input type="text" class="form-control" id="name" name="name" required="required">
</div>
</div>
<div class="form-group">
<label for="message-text" class="col-form-label">Position:</label>
<div class="input-group mb-3">
<input type="text" class="form-control" id="position" name="position" required="required">
</div>
</div>
<div class="form-group">
<label for="message-text" class="col-form-label">Office:</label>
<div class="input-group mb-3">
<input type="text" class="form-control" id="office" name="office" required="required">
</div>
</div>
<div class="form-group">
<label for="message-text" class="col-form-label">Photo:</label>
<div class="input-group mb-3">
<div class="custom-file">
<input type="file" class="custom-file-input" name="photo" id="userphoto">
<label class="custom-file-label" for="userphoto">Choose file</label>
</div>
</div>
</div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
<button type="submit" class="btn btn-success" id="addButton">Submit</button>
<input type="hidden" name="action" value="addnew">
<input type="hidden" name="employeeid" id="employeeid" value="">
</div>
</form>
</div>
</div>
</div>
<!-- add/edit form modal end -->
viewModal.php
//viewModal.php
<!-- profile modal start -->
<div class="modal fade" id="empViewModal" tabindex="-1" role="dialog" aria-labelledby="empViewModalLabel"
aria-hidden="true">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
<h5 class="modal-title" id="exampleModalLabel">Profile <i class="fa fa-user-circle-o"
aria-hidden="true"></i></h5>
<button type="button" class="close" data-dismiss="modal" aria-label="Close">
<span aria-hidden="true">×</span>
</button>
</div>
<div class="modal-body">
<div class="container" id="profile"></div>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Close</button>
</div>
</form>
</div>
</div>
</div>
<!-- profile modal end -->
js/script.js
//js/script.js
// get pagination
function pagination(totalpages, currentpage) {
var pagelist = "";
if (totalpages > 1) {
currentpage = parseInt(currentpage);
pagelist += `<ul class="pagination justify-content-center">`;
const prevClass = currentpage == 1 ? " disabled" : "";
pagelist += `<li class="page-item${prevClass}"><a class="page-link" href="#" data-page="${
currentpage - 1
}">Previous</a></li>`;
for (let p = 1; p <= totalpages; p++) {
const activeClass = currentpage == p ? " active" : "";
pagelist += `<li class="page-item${activeClass}"><a class="page-link" href="#" data-page="${p}">${p}</a></li>`;
}
const nextClass = currentpage == totalpages ? " disabled" : "";
pagelist += `<li class="page-item${nextClass}"><a class="page-link" href="#" data-page="${
currentpage + 1
}">Next</a></li>`;
pagelist += `</ul>`;
}
$("#pagination").html(pagelist);
}
// get employee row
function getemployeerow(employee) {
var employeeRow = "";
if (employee) {
const userphoto = employee.photo ? employee.photo : "default.png";
employeeRow = `<tr>
<td class="align-middle"><img src="uploads/${userphoto}" height="50" class="img-thumbnail rounded float-left"></td>
<td class="align-middle">${employee.name}</td>
<td class="align-middle">${employee.position}</td>
<td class="align-middle">${employee.office}</td>
<td class="align-middle">${employee.age}</td>
<td class="align-middle">${employee.salary}</td>
<td class="align-middle">
<a href="#" class="btn btn-success mr-3 profile" data-toggle="modal" data-target="#empViewModal"
title="Prfile" data-id="${employee.id}">View</a>
<a href="#" class="btn btn-warning mr-3 editemployee" data-toggle="modal" data-target="#employeeModal"
title="Edit" data-id="${employee.id}">Edit</a>
<a href="#" class="btn btn-danger deletetemployee" data-id="${employee.id}" title="Delete" data-id="${employee.id}">Delete</a>
</td>
</tr>`;
}
return employeeRow;
}
// get employees list
function listemployee() {
var pageno = $("#currentpage").val();
$.ajax({
url: "/devtest/phpcrudajax/ajax.php",
type: "GET",
dataType: "json",
data: { page: pageno, action: "getusers" },
beforeSend: function () {
$("#overlay").fadeIn();
},
success: function (rows) {
console.log(rows);
if (rows.jsonemplyee) {
var employeeslist = "";
$.each(rows.jsonemplyee, function (index, employee) {
employeeslist += getemployeerow(employee);
});
$("#employeetable tbody").html(employeeslist);
let totalemployees = rows.count;
let totalpages = Math.ceil(parseInt(totalemployees) / 4);
const currentpage = $("#currentpage").val();
pagination(totalpages, currentpage);
$("#overlay").fadeOut();
}
},
error: function () {
console.log("something went wrong");
},
});
}
$(document).ready(function () {
// pagination
$(document).on("click", "ul.pagination li a", function (e) {
e.preventDefault();
var $this = $(this);
const pagenum = $this.data("page");
$("#currentpage").val(pagenum);
listemployee();
$this.parent().siblings().removeClass("active");
$this.parent().addClass("active");
});
// form reset on new button
$("#addnewbtn").on("click", function () {
$("#addform")[0].reset();
$("#employeeid").val("");
});
// searching
$("#searchinput").on("keyup", function () {
const searchText = $(this).val();
if (searchText.length > 1) {
$.ajax({
url: "/devtest/phpcrudajax/ajax.php",
type: "GET",
dataType: "json",
data: { searchQuery: searchText, action: "search" },
success: function (employees) {
if (employees) {
var employeeslist = "";
$.each(employees, function (index, employee) {
employeeslist += getemployeerow(employee);
});
$("#employeetable tbody").html(employeeslist);
$("#pagination").hide();
}
},
error: function () {
console.log("something went wrong");
},
});
} else {
listemployee();
$("#pagination").show();
}
});
// add/edit user
$(document).on("submit", "#addform", function (event) {
event.preventDefault();
var alertmsg =
$("#employeeid").val().length > 0
? "Employee has been updated Successfully!"
: "New employee has been added Successfully!";
$.ajax({
url: "/devtest/phpcrudajax/ajax.php",
type: "POST",
dataType: "json",
data: new FormData(this),
processData: false,
contentType: false,
beforeSend: function () {
$("#overlay").fadeIn();
},
success: function (response) {
console.log(response);
if (response) {
$("#employeeModal").modal("hide");
$("#addform")[0].reset();
$(".message").html(alertmsg).fadeIn().delay(3000).fadeOut();
listemployee();
$("#overlay").fadeOut();
}
},
error: function () {
console.log("Oops! Something went wrong!");
},
});
});
//get user
$(document).on("click", "a.editemployee", function () {
var empid = $(this).data("id");
$.ajax({
url: "/devtest/phpcrudajax/ajax.php",
type: "GET",
dataType: "json",
data: { id: empid, action: "getuser" },
beforeSend: function () {
$("#overlay").fadeIn();
},
success: function (employee) {
if (employee) {
$("#name").val(employee.name);
$("#position").val(employee.position);
$("#office").val(employee.office);
$("#employeeid").val(employee.id);
}
$("#overlay").fadeOut();
},
error: function () {
console.log("something went wrong");
},
});
});
// get profile
$(document).on("click", "a.profile", function () {
var pid = $(this).data("id");
$.ajax({
url: "/devtest/phpcrudajax/ajax.php",
type: "GET",
dataType: "json",
data: { id: pid, action: "getuser" },
success: function (employee) {
if (employee) {
const userphoto = employee.photo ? employee.photo : "default.png";
const profile = `<div class="row">
<div class="col-sm-6 col-md-4">
<img src="uploads/${userphoto}" class="rounded responsive" />
</div>
<div class="col-sm-6 col-md-8">
<h4 class="text-primary">${employee.name}</h4>
<p class="text-secondary">
Position : ${employee.position}
<br />
Office : ${employee.office}
</p>
</div>
</div>`;
$("#profile").html(profile);
}
},
error: function () {
console.log("something went wrong");
},
});
});
// delete user
$(document).on("click", "a.deletetemployee", function (e) {
e.preventDefault();
var pid = $(this).data("id");
if (confirm("Are you sure want to delete this?")) {
$.ajax({
url: "/devtest/phpcrudajax/ajax.php",
type: "GET",
dataType: "json",
data: { id: pid, action: "deletetemployee" },
beforeSend: function () {
$("#overlay").fadeIn();
},
success: function (res) {
if (res.deleted == 1) {
$(".message")
.html("employee has been deleted successfully!")
.fadeIn()
.delay(3000)
.fadeOut();
listemployee();
$("#overlay").fadeOut();
}
},
error: function () {
console.log("something went wrong");
},
});
}
});
// load employees
listemployee();
});
ajax.php
//ajax.php
<?php
$action = $_REQUEST['action'];
if (!empty($action)) {
require_once 'Employee.php';
$obj = new Employee();
}
if ($action == 'addnew' && !empty($_POST)) {
$name = $_POST['name'];
$position = $_POST['position'];
$office = $_POST['office'];
$photo = $_FILES['photo'];
$employeeID = (!empty($_POST['employeeid'])) ? $_POST['employeeid'] : '';
// file (photo) upload
$imagename = '';
if (!empty($photo['name'])) {
$imagename = $obj->uploadPhoto($photo);
$employeeData = [
'name' => $name,
'position' => $position,
'office' => $office,
'photo' => $imagename,
];
} else {
$employeeData = [
'name' => $name,
'position' => $position,
'office' => $office,
];
}
if ($employeeID) {
$obj->update($employeeData, $employeeID);
} else {
$employeeID = $obj->add($employeeData);
}
if (!empty($employeeID)) {
$employeejson = $obj->getRow('id', $employeeID);
echo json_encode($employeejson);
exit();
}
}
if ($action == "getusers") {
$page = (!empty($_GET['page'])) ? $_GET['page'] : 1;
$limit = 4;
$start = ($page - 1) * $limit;
$employee = $obj->getRows($start, $limit);
if (!empty($employee)) {
$employeelist = $employee;
} else {
$employeelist = [];
}
$total = $obj->getCount();
$empjson = ['count' => $total, 'jsonemplyee' => $employeelist];
echo json_encode($empjson);
exit();
}
if ($action == "getuser") {
$employeeID = (!empty($_GET['id'])) ? $_GET['id'] : '';
if (!empty($employeeID)) {
$employeejsongetuser = $obj->getRow('id', $employeeID);
echo json_encode($employeejsongetuser);
exit();
}
}
if ($action == "deletetemployee") {
$employeeID = (!empty($_GET['id'])) ? $_GET['id'] : '';
if (!empty($employeeID)) {
$isDeleted = $obj->deleteRow($employeeID);
if ($isDeleted) {
$message = ['deleted' => 1];
} else {
$message = ['deleted' => 0];
}
echo json_encode($message);
exit();
}
}
if ($action == 'search') {
$queryString = (!empty($_GET['searchQuery'])) ? trim($_GET['searchQuery']) : '';
$results = $obj->searchEmployee($queryString);
echo json_encode($results);
exit();
}
Db.php
//Db.php
<?php
class Database
{
private $dbServer = 'localhost';
private $dbUser = 'root';
private $dbPassword = '';
private $dbName = 'testingdb';
protected $conn;
public function __construct()
{
try {
$dsn = "mysql:host={$this->dbServer}; dbname={$this->dbName}; charset=utf8";
$options = array(PDO::ATTR_PERSISTENT);
$this->conn = new PDO($dsn, $this->dbUser, $this->dbPassword, $options);
} catch (PDOException $e) {
echo "Connection Error: " . $e->getMessage();
}
}
}
Employee.php
//Employee.php
<?php
require_once 'Db.php';
class Employee extends Database
{
// table name
protected $tableName = 'employee';
//function is used to add record
public function add($data)
{
if (!empty($data)) {
$fileds = $placholders = [];
foreach ($data as $field => $value) {
$fileds[] = $field;
$placholders[] = ":{$field}";
}
}
$sql = "INSERT INTO {$this->tableName} (" . implode(',', $fileds) . ") VALUES (" . implode(',', $placholders) . ")";
$stmt = $this->conn->prepare($sql);
try {
$this->conn->beginTransaction();
$stmt->execute($data);
$lastInsertedId = $this->conn->lastInsertId();
$this->conn->commit();
return $lastInsertedId;
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
$this->conn->rollback();
}
}
//function update record
public function update($data, $id)
{
if (!empty($data)) {
$fileds = '';
$x = 1;
$filedsCount = count($data);
foreach ($data as $field => $value) {
$fileds .= "{$field}=:{$field}";
if ($x < $filedsCount) {
$fileds .= ", ";
}
$x++;
}
}
$sql = "UPDATE {$this->tableName} SET {$fileds} WHERE id=:id";
$stmt = $this->conn->prepare($sql);
try {
$this->conn->beginTransaction();
$data['id'] = $id;
$stmt->execute($data);
$this->conn->commit();
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
$this->conn->rollback();
}
}
//function to get records
public function getRows($start = 0, $limit = 4)
{
$sql = "SELECT * FROM {$this->tableName} ORDER BY id DESC LIMIT {$start},{$limit}";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
if ($stmt->rowCount() > 0) {
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
$results = [];
}
return $results;
}
// delete row using id
public function deleteRow($id)
{
$sql = "DELETE FROM {$this->tableName} WHERE id=:id";
$stmt = $this->conn->prepare($sql);
try {
$stmt->execute([':id' => $id]);
if ($stmt->rowCount() > 0) {
return true;
}
} catch (PDOException $e) {
echo "Error: " . $e->getMessage();
return false;
}
}
public function getCount()
{
$sql = "SELECT count(*) as pcount FROM {$this->tableName}";
$stmt = $this->conn->prepare($sql);
$stmt->execute();
$result = $stmt->fetch(PDO::FETCH_ASSOC);
return $result['pcount'];
}
//function to get single record based on the column value
public function getRow($field, $value)
{
$sql = "SELECT * FROM {$this->tableName} WHERE {$field}=:{$field}";
$stmt = $this->conn->prepare($sql);
$stmt->execute([":{$field}" => $value]);
if ($stmt->rowCount() > 0) {
$result = $stmt->fetch(PDO::FETCH_ASSOC);
} else {
$result = [];
}
return $result;
}
//function for search table fields name
public function searchEmployee($searchText, $start = 0, $limit = 4)
{
$sql = "SELECT * FROM {$this->tableName} WHERE name LIKE :search ORDER BY id DESC LIMIT {$start},{$limit}";
$stmt = $this->conn->prepare($sql);
$stmt->execute([':search' => "{$searchText}%"]);
if ($stmt->rowCount() > 0) {
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
$results = [];
}
return $results;
}
//funciton to upload file
public function uploadPhoto($file)
{
if (!empty($file)) {
$fileTempPath = $file['tmp_name'];
$fileName = $file['name'];
$fileSize = $file['size'];
$fileType = $file['type'];
$fileNameCmps = explode('.', $fileName);
$fileExtension = strtolower(end($fileNameCmps));
$newFileName = md5(time() . $fileName) . '.' . $fileExtension;
$allowedExtn = ["jpg", "png", "gif", "jpeg"];
if (in_array($fileExtension, $allowedExtn)) {
$uploadFileDir = getcwd() . '/uploads/';
$destFilePath = $uploadFileDir . $newFileName;
if (move_uploaded_file($fileTempPath, $destFilePath)) {
return $newFileName;
}
}
}
}
} //End Class Employee
css/style.css
//css/style.css
.container {
padding-top: 10px;
}
.rounded {
width: 100px;
}
.img-thumbnail {
width: 80px !important;
}
.message {
display: none;
}
#overlay {
background: #ffffff;
color: #666666;
position: fixed;
height: 100%;
width: 100%;
z-index: 5000;
top: 0;
left: 0;
float: left;
text-align: center;
padding-top: 25%;
opacity: 0.8;
}
