HTML MySQL Table edit with no page reloading via Ajax

An example of updating or editing an HTML table from MySQL data without reloading or changing pages thanks to Jquery and Ajax.

The Ajax requests are done API style to a PHP backend for fetching data (GET) and then updating it (POST) All without reloading the page via a pop up modal form which gets sent with Ajax.

Interactive example on CodePen.

This is the HTML contents, the frame for the table and the modal form which isn’t shown unless called upon.

id="contents" is what gets filled with the data.

<div class="container">
  <div class="row">
    <div class="col-12">
      <table class="table table-striped table-sm">
        <thead class="table-primary">
          <tr>
            <td>Id</td>
            <td>Name</td>
            <td>Price</td>
            <td>QTY</td>
            <td>Sold</td>
            <td>Updated</td>
            <td></td>
            <td></td>
          </tr>
        </thead>
        <tbody id="contents">

        </tbody>
      </table>
      <div class="modal fade" id="modalEditForm" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
        <div class="modal-dialog" role="document">
          <div class="modal-content">
            <div class="modal-header text-center">
              <h4 class="modal-title w-100 font-weight-bold">Edit product</h4>
              <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                <span aria-hidden="true">&times;</span>
              </button>
            </div>
            <form id="updateForm" type="POST">
              <div class="modal-body mx-3">
                <div class="md-form mb-3">
                  <label for="product_name">Name (disabled for demo)</label>
                  <input type="text" id="product_name" name="product_name" class="form-control" minlength="1" maxlength="32" disabled>
                </div>
                <input type="hidden" name="product_id" id="product_id" />
                <div class="md-form mb-3">
                  <label for="product_price">Price</label>
                  <input type="number" id="product_price" name="product_price" class="form-control" min="0" step="0.01" max="999.0">
                </div>
                <div class="md-form mb-3">
                  <label for="product_sold">Sold</label>
                  <input type="number" id="product_sold" name="product_sold" class="form-control" min="0" step="1" max="999">
                </div>
                <div class="md-form mb-3">
                  <label for="product_qty">Quantity</label>
                  <input type="number" id="product_qty" name="product_qty" class="form-control" min="0" step="1" max="999">
                </div>
              </div>
              <div class="modal-footer d-flex justify-content-center">
                <button class="btn btn-primary btn-block" type="submit">Update</button>
              </div>
            </form>
          </div>
        </div>
      </div>
    </div>
  </div>
</div>

The full javascript file, this includes the functions to GET and POST (update the DB) as well as the reset the table and pop up the modal form.

function buildTable() {
  $.ajax({
    type: "GET",
    url: "https://peachpuff.srv3r.com/api/index.php",
    data: { type: "SELECT" },
    success: function (msg) {
      var i;
      for (i = 0; i < msg.length; i++) {
        var row =
          "<tr><td> " +
          msg[i]["id"] +
          " </td> <td> " +
          msg[i]["name"] +
          "</td><td>" +
          msg[i]["price"] +
          "</td><td>" +
          msg[i]["qty"] +
          "</td><td>" +
          msg[i]["sold"] +
          "</td><td>" +
          msg[i]["updated"] +
          "</td></td><td><a id='edit' class='btn btn-primary btn-sm' href='#' role='button' data-toggle='modal' data-target='#modalEditForm' value='" +
          msg[i]["id"] +
          "'>edit</a></td><td><a id='oos' class='btn btn-danger btn-sm' href='#' role='button' value='" +
          msg[i]["id"] +
          "'>oos</a></td></tr>";
        $("#contents").append(row);
      }
    }
  });
}

function modalEditForm(id) {
  $.ajax({
    type: "GET",
    url: "https://peachpuff.srv3r.com/api/index.php",
    data: { type: "SELECT", id: id },
    success: function (result) {
      document.getElementById("product_name").value = result[0]["name"];
      document.getElementById("product_price").value = result[0]["price"];
      document.getElementById("product_qty").value = result[0]["qty"];
      document.getElementById("product_sold").value = result[0]["sold"];
    }
  });
}

function OOS(id) {
  $.ajax({
    type: "POST",
    url: "https://peachpuff.srv3r.com/api/index.php",
    data: { type: "UPDATE", id: id },
    success: function (result) {
      console.log(result);
    }
  });
}

function resetTable() {
  const myNode = document.getElementById("contents");
  myNode.innerHTML = "";
  buildTable();
}

$(document).ready(function () {
  buildTable();
  $("#updateForm").submit(function (e) {
    e.preventDefault();
    $.ajax({
      type: "POST",
      url: "https://peachpuff.srv3r.com/api/index.php",
      data: $(this).serialize(),
      success: function (data) {
        resetTable();
        $("#modalEditForm").modal("hide");
      }
    });
  });
});

$(document).on("click", "#oos", function () {
  OOS(this.getAttribute("value"));
  resetTable();
});

$(document).on("click", "#edit", function () {
  var prodId = this.getAttribute("value");
  document.getElementById("product_id").value = prodId;
  modalEditForm(prodId);
});

The PHP (api/index.php):

header('Access-Control-Allow-Origin: *');
header('Content-Type: application/json');

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['product_qty'])) {
        $update = $db->prepare("UPDATE `shop` SET `qty` = ?, `price` = ?, `sold` = ? WHERE `id` = ? LIMIT 1;");
        $update->execute([$_POST['product_qty'], $_POST['product_price'], $_POST['product_sold'], $_POST['product_id']]);
        echo json_encode($_POST);
    } else {
        $update = $db->prepare("UPDATE `shop` SET `qty` = 0 WHERE `id` = ? LIMIT 1;");
        $update->execute([$_POST['id']]);
        echo json_encode($_POST);
    }
} elseif ($_SERVER['REQUEST_METHOD'] === 'GET') {
    if (isset($_GET['id'])) {
        $select = $db->prepare("SELECT `id`, `name`, `qty`, `price`, `sold` FROM `shop` WHERE `id` = ?;");
        $select->execute([$_GET['id']]);
        echo json_encode($select->fetchAll(PDO::FETCH_ASSOC));
    } else {
        $select = $db->prepare("SELECT `id`, `name`, `qty`, `price`, `sku`, `sold`, date_format(updated, '%e %b %l:%i %p') as updated FROM `shop`;");
        $select->execute();
        echo json_encode($select->fetchAll(PDO::FETCH_ASSOC));
    }
} else {
    echo json_encode(array());
}

Video example: