Update, Delete and Export data using OOPS CRUD System with PHP Jquery AJAX and MySQL
5 min readNov 1, 2020
In this post we will learn other operations for creating a CRUD operation using the Ajax JQuery method with object-oriented PHP programming scripts. In this section we have explained how we can update, delete and export records in MySQL database tables using object-oriented PHP scripts using the Ajax method. So we can change the data in the table without refreshing the page.
In the previous section we looked at inserting or adding new records to MySQL database tables using Ajax using PHP scripts and Display records. Every web or system application needs changing or updating or editing data because first, when we add wrong information to any system, then after we want to change that information we want to need an updating process so that the updated data is part of the process. Rugged system. In this system we do not only change data, but also modify or delete record and export all data in excel using Ajax using PHP scripts.
In this section, we will first fetch individual data using Ajax using object-oriented PHP scripts in JSON format and then, after we load this data into other fields of the form, so that the data can be entered into the form fields, we can check the value modify form fields and click the submit button to send data from the form to the server with data files and objects, and send data from the form to the server using the Ajax method. This way we can edit or update using the object oriented Ajax method. In the next section you will learn how to clear data with Ajax using object-oriented PHP scripts without refreshing the page.
Create Database Table
-- Database: `crud_oops`-- Table structure for table `customers`CREATE TABLE `customers` (
`id` int(100) NOT NULL,
`name` varchar(100) NOT NULL,
`email` varchar(100) NOT NULL,
`username` varchar(100) NOT NULL,
`dob` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Database Connection using OOPS PHP and Edit, Update and Delete Function
Create database connectivity and edit update and delete function using OOPS PHP with Mysql.
config.php
<?php
class Database
{
private $servername = "localhost";
private $username = "root";
private $password = "";
private $dbname = "crud_oops";
public $con;
public $customerTable = "customers"; public function __construct()
{
try {
$this->con = new mysqli($this->servername, $this->username, $this->password, $this->dbname);
} catch (Exception $e) {
echo $e->getMessage();
}
} // Fetch single data for edit from customer table
public function getRecordById($id)
{
$query = "SELECT * FROM $this->customerTable WHERE id = '$id'";
$result = $this->con->query($query);
if ($result->num_rows > 0) {
$row = $result->fetch_assoc();
return $row;
}else{
return false;
}
}
// Update customer data into customer table
public function updateRecord($id, $name, $email, $username, $dob)
{
$sql = "UPDATE $this->customerTable SET name = '$name', email = '$email', username = '$username', dob = '$dob'
WHERE id = '$id'";
$query = $this->con->query($sql);
if ($query) {
return true;
}else{
return false;
}
} // Delete customer data from customer table
public function deleteRecord($id)
{
$sql = "DELETE FROM $this->customerTable WHERE id = '$id'";
$query = $this->con->query($sql);
if ($query) {
return true;
}else{
return false;
}
}
}?>
HTML Code and jQuery AJAX Script
index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>CRUD Application with OOP in PHP using Jquery AJAX and MYSQL</title>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<link rel="stylesheet" href="https://pro.fontawesome.com/releases/v5.10.0/css/all.css"/>
<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/v/bs4/dt-1.10.22/datatables.min.css"/>
<link rel="stylesheet" href="https://pro.fontawesome.com/releases/v5.10.0/css/all.css"/>
</head>
<body><div class="card text-center" style="padding:15px;">
<h3>CRUD Application Using PHP-OOP Jquery AJAX, DataTable with SweetAlert</h3>
</div><br><br> <div class="container">
<div class="row">
<div class="col-lg-6">
<h4>All Customers from Database Records</h4>
</div>
<div class="col-lg-6">
<button type="button" class="btn btn-primary m-1 float-right" data-toggle="modal" data-target="#addModal">
<i class="fa fa-plus"></i> Add New Record</button>
<a href="action.php?export=excel" class="btn btn-success m-1 float-right"> <i class="fa fa-download"></i>
Export To Excel</a>
</div>
</div><br>
</div>
<div class="container">
<div class="row">
<div class="col-lg-12 col-md-12 col-sm-12">
<div class="table-responsive" id="tableData">
<h3 class="text-center text-success" style="margin-top: 150px;">Loading...</h3>
</div>
</div>
</div>
</div><!-- Add Record Modal -->
<div class="modal" id="addModal">
<div class="modal-dialog">
<div class="modal-content">
<!-- Modal Header -->
<div class="modal-header">
<h4 class="modal-title">Add New Customer</h4>
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<!-- Modal body -->
<div class="modal-body">
<form id="formData">
<div class="form-group">
<label for="name">Name:</label>
<input type="text" class="form-control" name="name" placeholder="Enter name" required="">
</div>
<div class="form-group">
<label for="email">Email address:</label>
<input type="email" class="form-control" name="email" placeholder="Enter email" required="">
</div>
<div class="form-group">
<label for="username">Username:</label>
<input type="text" class="form-control" name="username" placeholder="Enter username" required="">
</div>
<div class="form-group">
<label for="date">Date of birth:</label>
<input type="date" class="form-control" name="dob" placeholder="Enter dob" required="">
</div>
<hr>
<div class="form-group float-right">
<button type="submit" class="btn btn-success" id="submit">Submit</button>
<button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
</div>
</form>
</div>
</div>
</div>
</div><!-- Edit Record Modal -->
<div class="modal" id="editModal">
<div class="modal-dialog">
<div class="modal-content">
<!-- Modal Header -->
<div class="modal-header">
<h4 class="modal-title">Edit Customer</h4>
<button type="button" class="close" data-dismiss="modal">×</button>
</div>
<!-- Modal body -->
<div class="modal-body">
<form id="EditformData">
<input type="hidden" name="id" id="edit-form-id">
<div class="form-group">
<label for="name">Name:</label>
<input type="text" class="form-control" name="uname" id="name" placeholder="Enter name" required="">
</div>
<div class="form-group">
<label for="email">Email address:</label>
<input type="email" class="form-control" name="uemail" id="email" placeholder="Enter email" required="">
</div>
<div class="form-group">
<label for="username">Username:</label>
<input type="text" class="form-control" name="uusername" id="username" placeholder="Enter username" required="">
</div>
<div class="form-group">
<label for="date">Date of birth:</label>
<input type="date" class="form-control" name="udob" id="dob" placeholder="Enter dob" required="">
</div>
<hr>
<div class="form-group float-right">
<button type="submit" class="btn btn-primary" id="update">Update</button>
<button type="button" class="btn btn-danger" data-dismiss="modal">Close</button>
</div>
</form>
</div>
</div>
</div>
</div><script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script type="text/javascript" src="https://cdn.datatables.net/v/bs4/dt-1.10.22/datatables.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@10"></script><script type="text/javascript">
$(document).ready(function(){
showAllCustomer();
//View Record
function showAllCustomer(){
$.ajax({
url : "action.php",
type: "POST",
data : {action:"view"},
success:function(response){
$("#tableData").html(response);
$("table").DataTable({
order:[0, 'DESC']
});
}
});
}
//Edit Record
$("body").on("click", ".editBtn", function(e){
e.preventDefault();
var editId = $(this).attr('id');
$.ajax({
url : "action.php",
type : "POST",
data : {editId:editId},
success:function(response){
var data = JSON.parse(response);
$("#edit-form-id").val(data.id);
$("#name").val(data.name);
$("#email").val(data.email);
$("#username").val(data.username);
$("#dob").val(data.dob);
}
});
});
//update ajax request data
$("#update").click(function(e){
if ($("#EditformData")[0].checkValidity()) {
e.preventDefault();
$.ajax({
url : "action.php",
type : "POST",
data : $("#EditformData").serialize()+"&action=update",
success:function(response){
Swal.fire({
icon: 'success',
title: 'Customer updated successfully',
});
$("#editModal").modal('hide');
$("#EditformData")[0].reset();
showAllCustomer();
}
});
}
}); //Delete Record
$("body").on("click", ".deleteBtn", function(e){
e.preventDefault();
var tr = $(this).closest('tr');
var deleteBtn = $(this).attr('id');
if (confirm('Are you sure want to delete this Record')) {
$.ajax({
url : "action.php",
type : "POST",
data : {deleteBtn:deleteBtn},
success:function(response){
tr.css('background-color','#ff6565');
Swal.fire({
icon: 'success',
title: 'Customer delete successfully',
});
showAllCustomer();
}
});
}
});
});
</script></body>
</html>
Call Update, Edit, Delete and Export data Function
action.php
<?php
// Include config.php file
include_once('config.php'); $dbObj = new Database(); // Edit Record
if (isset($_POST['editId'])) {
$editId = $_POST['editId'];
$row = $dbObj->getRecordById($editId);
echo json_encode($row);
}
// Update Record
if (isset($_POST['action']) && $_POST['action'] == "update") {
$id = $_POST['id'];
$name = $_POST['uname'];
$email = $_POST['uemail'];
$username = $_POST['uusername'];
$dob = $_POST['udob'];
$dbObj->updateRecord($id, $name, $email, $username, $dob);
}
// Delete Record
if (isset($_POST['deleteBtn'])) {
$deleteBtn = $_POST['deleteBtn'];
$dbObj->deleteRecord($deleteBtn);
}
// Export to excel
if (isset($_GET['export']) && $_GET['export'] == 'excel') { header("Content-type: application/vnd.ms-excel; name='excel'");
header("Content-Disposition: attachment; filename=customers.xls");
header("Pragma: no-cache");
header("Expires: 0"); $exportData = $dbObj->displayRecord(); echo'<table border="1">
<tr style="font-weight:bold">
<td>Id</td>
<td>Name</td>
<td>Email</td>
<td>Username</td>
<td>Dob</td>
</tr>';
foreach ($exportData as $export) {
echo'<tr>
<td>'.$export['id'].'</td>
<td>'.$export['name'].'</td>
<td>'.$export['email'].'</td>
<td>'.$export['username'].'</td>
<td>'.date('d-M-Y', strtotime($export['dob'])).'</td>
</tr>';
}
echo '</table>';
}?>
Update, Delete and Export data using OOPS CRUD System with PHP Jquery AJAX and MySQL
Originally published at https://www.webscodex.com on November 1, 2020.