This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish.

Update, Delete and Export data using OOPS CRUD System with PHP Jquery AJAX and MySQL

Update, Delete and Export data using OOPS CRUD System with PHP Jquery AJAX and MySQL

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 Mysql Database and 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;

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

?>

Output

Update, Delete and Export data using OOPS CRUD System with PHP Jquery AJAX and MySQL

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">&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']
          });
        }
      });
    }


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

Output

Update, Delete and Export data using OOPS CRUD System with PHP Jquery AJAX and MySQL

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

?>

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

Related Blog Posts

Leave a Reply

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

Trending now

How to Send Bulk Emails in PHP using PHPMailer with Ajax jQuery
How to Created PHP CRUD using OOPS with MYSQLi in MVC
Create User Registration and Login using PHP Oops Concepts
Generate PDF File From MySQL Database Using PHP

About Webs codex

Webs Codex is a free online resource on programming and web development for beginners to advance. It was founded in Jan 2020 by Manish Pushpakar to deliver the best and fresh articles for students and our readers to skill up as they would like to. we are share multiple technologies tutorials.

Customer Area

Quick Links

Copyright © 2020-22 Webscodex.com All rights reserved