CRUD Application Using PHP-OOPs Jquery AJAX, Data-Table with Sweet Alert


CRUD Application Using PHP-OOPs Jquery AJAX, DataTable with SweetAlert

CRUD Application means, Create, Read, Update and Delete. In this  tutorials. I will explain how to perform CRUD operations using PHP OOPs jquery AJAX with Mysql.

Folder and File Structure

config.php
index.php
action.php

Create Mysql 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;

Create Database Class with CRUD method

In this post you will create class for MySQL database connections and CRUD operations, like Select, insert, update and delete with MySQL database. We’ll have a displayRecord() method for selecting client records, an insertRecord() method for inserting client records, an updateRecord() method for updating client data, and a deleteRecord() method for deleting the client’s Own records. and the getRecordById() method to get a single record. Here is a complete client class with all their methods. All you have to do is change the MySQL database connection details while it is running on your server.
but in this post we are using PHP OOPs with jQuery AJAX and also use Data-table for show data with pagination and Sweet alert for sweet popup message. CRUD Application with PHP-OOPS 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();
}
}

// Insert customer data into customer table
public function insertRecond($name, $email, $username, $dob)
{
$sql = "INSERT INTO $this->customerTable (name, email, username, dob) VALUES('$name','$email','$username','$dob')";
$query = $this->con->query($sql);
if ($query) {
return true;
}else{
return false;
}
}

// Fetch customer records for show listing
public function displayRecord()
{
$sql = "SELECT * FROM $this->customerTable";
$query = $this->con->query($sql);
$data = array();
if ($query->num_rows > 0) {
while ($row = $query->fetch_assoc()) {
$data[] = $row;
}
return $data;
}else{
return false;
}
}

// 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;
}
}


public function totalRowCount(){
$sql = "SELECT * FROM $this->customerTable";
$query = $this->con->query($sql);
$rowCount = $query->num_rows;
return $rowCount;
}


// 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;
}
}
}

?>

Insert and view data using php oops with jquery ajax

HTML Code and jQuery AJAX Script

index.php
<!DOCTYPE html>
<html lang="en">
<head>
<title>CRUD Application with OOPs 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-OOPs 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">&times;</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">&times;</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']
});
}
});
}

//insert ajax request data
$("#submit").click(function(e){
if ($("#formData")[0].checkValidity()) {
e.preventDefault();
$.ajax({
url : "action.php",
type : "POST",
data : $("#formData").serialize()+"&action=insert",
success:function(response){
Swal.fire({
icon: 'success',
title: 'Customer added successfully',
});
$("#addModal").modal('hide');
$("#formData")[0].reset();
showAllCustomer();
}
});
}
});

//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>


Insert data using php oops with jquery ajax



Call Insert, View, Update, Edit, and Delete Function

Database is a constructor function used for making localhost connections and database selection.
The public function insertRecord() function has a parameter that accepts input from the HTML form.
The public function displayRecord() reads all data from the customers table.
The public function getRecordById() has a user ID parameter. This function reads data based
 on user ID.
The public function updateRecord() function has some sort of user ID parameter that accepts input from an HTML record update form.
The public function deleteRecord() function has a rid parameter. Based on the error (line ID), 
this function clears the data.
action.php
<?php
// Include config.php file
include_once('config.php');

$dbObj = new Database();

// Insert Record
if (isset($_POST['action']) && $_POST['action'] == "insert") {

$name = $_POST['name'];
$email = $_POST['email'];
$username = $_POST['username'];
$dob = $_POST['dob'];
$dbObj->insertRecond($name, $email, $username, $dob);
}

// View record
if (isset($_POST['action']) && $_POST['action'] == "view") {
$output = "";

$customers = $dbObj->displayRecord();

if ($dbObj->totalRowCount() > 0) {
$output .="<table class='table table-striped table-hover'>
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Email</th>
<th>Username</th>
<th>Date of birth</th>
<th>Action</th>
</tr>
</thead>
<tbody>"
;
foreach ($customers as $customer) {
$output.="<tr>
<td>"
.$customer['id']."</td>
<td>"
.$customer['name']."</td>
<td>"
.$customer['email']."</td>
<td>"
.$customer['username']."</td>
<td>"
.date('d-M-Y', strtotime($customer['dob']))."</td>
<td>
<a href='#editModal' style='color:green' data-toggle='modal'
class='editBtn' id='"
.$customer['id']."'><i class='fa fa-pencil'></i></a>&nbsp;
<a href='' style='color:red' class='deleteBtn' id='"
.$customer['id']."'>
<i class='fa fa-trash' ></i></a>
</td>
</tr>"
;
}
$output .= "</tbody>
</table>"
;
echo $output;
}else{
echo '<h3 class="text-center mt-5">No records found</h3>';
}
}

// 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);
}


// Edit 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>';
}

?>


You will also learn the Export data to excel.
You can always support by sharing on social media or recommending my blog to your friends and colleagues.   
If you have any suggestions / problems about this tutorial, please comment on the  form below.😊


Leave a Reply

This Post Has 2 Comments

  1. Unknown

    thanks for this amazing content. it is very helpful for me.

  2. Networking IT

    Thank you for the great content.
    Busy learn by working through the samples.

    Please add "Auto-increment" to "id-field" otherwise "Cannot Add Record"