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

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

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

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>

Add Output

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

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

Database is a constructor function used for making localhost connections and database selection.

  1. The public function insertRecord() function has a parameter that accepts input from the HTML form.
  2. The public function displayRecord() reads all data from the customers table.
  3. The public function getRecordById() has a user ID parameter. This function reads data based
  4.  on user ID.
  5. The public function updateRecord() function has some sort of user ID parameter that accepts input from an HTML record update form.
  6. The public function deleteRecord() function has a rid parameter. Based on the error (line ID), 
  7. 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 Popup output

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

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

3 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *