Insert or Add View and Edit using OOPS CRUD System with PHP Jquery AJAX and MySQL

In the post we will create a crud application using object-oriented PHP programming with Ajax, you will learn how to insert or add record to MySQL database tables using object-oriented PHP programming concepts with the JQuery Ajax method. So we can enter or add data and update data in the HTML table without reload the page.

In previous post you will learn PHP CRUD application with Mysql. In this system we will develop PHP Crud Operation without refreshing the page using object-oriented programming PHP with Ajax Jquery and Bootstrap Framework. When we use object oriented PHP code as the back, we use Ajax on the front. So this type of system runs much faster than regular PHP scripting systems.

In the latest trend, most of the web applications are using Ajax for all kinds of operations. This is because the user can perform any operation without opening another page. Insert data to Mysql PHP jquey AJAX. 

So in this section we will not only see how we can insert data but also View and Edit using the Ajax Jquery method with object-oriented PHP programming without refreshing the page. To create a stylish shape we will use Bootstrap modal we will also using the Data-Table for View records and pagination search.

So in this post system, we have seen how we can use Bootstrap Modal to insert data into MySQL database tables by using Ajax Jquery method with PHP object oriented programming to add new records without refreshing the page.

We can also call enter or add live data or web applications on the page. In the next section we will discuss how to update, delete and export data to excel sheet MySQL table data with Ajax using PHP scripts for object oriented programming.

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 INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `username` varchar(100) NOT NULL,
  `dob` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--

Output

Insert or Add View and Edit using OOPS CRUD System with PHP Jquery AJAX and MySQL

Database Connection and Insert Display and Edit Function

Create database connection to database and Insert,display record and view in bootstrap modal. You will use the Data-table and sweet alert popup message using sweet alert library to display good looking message to add, update and delete time to record.

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

  }
?>

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

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

Output

Insert or Add View and Edit using OOPS CRUD System with PHP Jquery AJAX and MySQL

Call CRUD Function File

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

?>

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

Insert or Add View and Edit using OOPS CRUD System with PHP Jquery AJAX and MySQL

4 Comments

Leave a Reply

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