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.
Create Mysql Database Table
In the first step we need to create a database and tables, so here I have created a database of tables “webscodex” and ”customers” with identifiers and columns with names. You simply create a Users table as described in the SQL query.
-- Database: `webscodex` -- Table structure for table `customers` CREATE TABLE `customers` ( `id` int(100) NOT NULL PRIMARY KEY AUTO_INCREAMENT, `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;
}
}
}
?>
Output
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">×</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']
});
}
});
}
//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>
Add Output
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>
<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.
Edit Popup output
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.😊
thanks for this amazing content. it is very helpful for me.
thank for your comment
Thank you for the great content.
Busy learn by working through the samples.
Please add "Auto-increment" to "id-field" otherwise "Cannot Add Record"