CRUD Application Using PHP OOP 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.

First, we need the jQuery library, Bootstrap for a better design to our app and Sweet Alert. I’ve included these files in the downloadable of this tutorial but if you want, you can download them yourself using the links below:

Getting Started our Project to include Some Library

  1. For Bootstrap
  2. For jQuery
  3. For Sweet Alert

Folder and File Structure

  1. config.php
  2. index.php
  3. action.php

Creating MySQLi Database and Table

Next, we create the database that we are going to filter in this tutorial.You should be able to create a database named crud_oops. you can also run the code below in the SQL page in phpmyadmin.

-- Database: `crud_oops`

-- Table structure for table `customers`

CREATE TABLE `customers` (
  `id` int(100) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `username` varchar(100) NOT NULL,
  `dob` date NOT NULL
   PRIMARY KEY (id)
) 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.

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

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>

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

?>

View Output

Insert and view data using php oops with jquery ajax

 

Add new output:

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.

Edit 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.😊


Leave a Reply

This Post Has One Comment

  1. 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"