1. Tutorials
  2. JavaScript Tutorial
  3. Inline Table Row Insert, Update, and Delete using PHP and jQuery Tutorial

Inline Table Row Insert, Update, and Delete using PHP and jQuery Tutorial

Submitted by oretnom23 on Monday, August 23, 2021 - 14:48.

In this tutorial, you wil learn how to create an Inline Table Row Insert, Update, and Delete using PHP/OOP, MySQL Database,and JavaScript (jQuery adn Ajax). Our main goal for this tutorial is to display data from database to HTML table that can add, editm remove table row dynamically. The table cells in the table will be editable if the user add or edit a row. The source code must have a field validator for required fields and field text types.

Getting Started

I'll be using Bootstrap and jQuery plugins/libraries on this tutorial. And to run my PHP Script, I will use XAMPP version 3.3.0 which has a PHP version 8.0.7.

Before we continue, please make sure that your XAMPP/WAMP's Apache and MySQL are already started. To do this, open your XAMPP/WAMP's Control Panel and start the mentioned web and database server.

Creating the Database

Open the PHPMyAdmin in a browser i.e. http://localhost/phpmyadmin and create new dayabase naming inline_db. After that, navigate the page to the SQL Tab and copy/paste the sql script below.

  1.         CREATE TABLE `members` (
  2.           `id` int(30) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  3.           `name` text NOT NULL,
  4.           `contact` varchar(50) NOT NULL,
  5.           `email` varchar(250) NOT NULL,
  6.           `address` text NOT NULL
  7.         ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Creating the Database Connection

Create a new PHP File and save it as database.php. Then, copy/paste the folling PHP script below.

  1. <?php
  2. class DBConnection{
  3.  
  4.     private $host = 'localhost';
  5.     private $username = 'root';
  6.     private $password = '';
  7.     private $database = 'inline_db';
  8.    
  9.     public $conn;
  10.    
  11.     public function __construct(){
  12.  
  13.         if (!isset($this->conn)) {
  14.            
  15.             $this->conn = new mysqli($this->host, $this->username, $this->password, $this->database);
  16.            
  17.             if (!$this->conn) {
  18.                 echo 'Cannot connect to database server';
  19.                 exit;
  20.             }            
  21.         }    
  22.        
  23.     }
  24.     public function __destruct(){
  25.         $this->conn->close();
  26.     }
  27. }
  28. ?>

Creating Our Interface

The script below is the code for the index file of the web application. Save the following script as index.php.

Note: please configure the external CSS and Javascript File Script according to your assets directory.
  1. <?php
  2. //Including the Database Connection Class
  3. require_once('database.php');
  4. $db = new DBConnection();
  5. $conn = $db->conn;
  6. ?>
  7. <!DOCTYPE html>
  8. <html lang="en">
  9. <head>
  10.     <meta charset="UTF-8">
  11.     <meta http-equiv="X-UA-Compatible" content="IE=edge">
  12.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  13.     <title>Inline Table Form</title>
  14.         <link rel="stylesheet" href="./assets/css/bootstrap.css">
  15. <style>
  16.     .editable{
  17.         display:none;
  18.     }
  19. </style>
  20. </head>
  21. <body>
  22. <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
  23.   <div class="container-fluid">
  24.     <a class="navbar-brand" href="https://sourcecodester.com">SourceCodester</a>
  25.   </div>
  26. </nav>
  27. <div class="container py-3">
  28.     <h2 class="border-bottom border-dark">Table Inline Form</h2>
  29.     <span>Inline Table row cells adding and editing data using PHP and jQuery</span>
  30.  
  31.     <div class="row">
  32.         <div class="col-12">
  33.             <h3 class="text-center">Member List</h3>
  34.         </div>
  35.         <hr>
  36.         <div class="col-12">
  37.             <!-- Table Form start -->
  38.             <form action="" id="form-data">
  39.                 <input type="hidden" name="id" value="">
  40.                 <table class='table table-hovered table-stripped table-bordered' id="form-tbl">
  41.                     <colgroup>
  42.                         <col width="20%">
  43.                         <col width="25%">
  44.                         <col width="15%">
  45.                         <col width="25%">
  46.                         <col width="15%">
  47.                     </colgroup>
  48.                     <thead>
  49.                         <tr>
  50.                             <th class="text-center p-1">Name</th>
  51.                             <th class="text-center p-1">Email</th>
  52.                             <th class="text-center p-1">Contact</th>
  53.                             <th class="text-center p-1">Address</th>
  54.                             <th class="text-center p-1">Action</th>
  55.                         </tr>
  56.                     </thead>
  57.                     <tbody>
  58.                     <?php
  59.                     $query = $conn->query("SELECT * FROM `members` order by id asc");
  60.                     while($row = $query->fetch_assoc()):
  61.                     ?>
  62.                     <tr data-id='<?php echo $row['id'] ?>'>
  63.                         <td name="name"><?php echo $row['name'] ?></td>
  64.                         <td name="email"><?php echo $row['email'] ?></td>
  65.                         <td name="contact"><?php echo $row['contact'] ?></td>
  66.                         <td name="address"><?php echo $row['address'] ?></td>
  67.                         <td class="text-center">
  68.                             <button class="btn btn-primary btn-sm rounded-0 py-0 edit_data noneditable" type="button">Edit</button>
  69.                             <button class="btn btn-danger btn-sm rounded-0 py-0 delete_data noneditable" type="button">Delete</button>
  70.                             <button class="btn btn-sm btn-primary btn-flat rounded-0 px-2 py-0 editable">Save</button>
  71.                             <button class="btn btn-sm btn-dark btn-flat rounded-0 px-2 py-0 editable" onclick="cancel_button($(this))" type="button">Cancel</button></td>
  72.                         </td>
  73.                     </tr>
  74.                     <?php endwhile; ?>
  75.                     </tbody>
  76.                 </table>
  77.             </form>
  78.             <!-- Table Form end -->
  79.         </div>
  80.         <div class="w-100 d-flex pposition-relative justify-content-center">
  81.             <button class="btn btn-flat btn-primary" id="add_member" type="button">Add New Member</button>
  82.         </div>
  83.     </div>
  84. </div>
  85. </body>
  86. <script type="text/javascript" src="./assets/js/jquery-3.6.0.js"></script>
  87. <script type="text/javascript" src="./assets/js/bootstrap.js"></script>
  88. <!-- This is the javascript file that contains the actions scripts of the table -->
  89. <script type="text/javascript" src="./assets/js/script.js"></script>
  90. </html>

Creating the JavaScript File

In this script, it contains the functionalities of the buttons and the Ajax Request scripts for the form submission. Save the following script as script.js

  1. $(function() {
  2.         // Create New Row
  3.         $('#add_member').click(function() {
  4.             if ($('tr[data-id=""]').length > 0) {
  5.                 $('tr[data-id=""]').find('[name="name"]').focus()
  6.                 return false;
  7.             }
  8.             var tr = $('<tr>')
  9.             $('input[name="id"]').val('')
  10.             tr.addClass('py-1 px-2');
  11.             tr.attr('data-id', '');
  12.             tr.append('<td contenteditable name="name"></td>')
  13.             tr.append('<td contenteditable name="email"></td>')
  14.             tr.append('<td contenteditable name="contact"></td>')
  15.             tr.append('<td contenteditable name="address"></td>')
  16.             tr.append('<td class="text-center"><button class="btn btn-sm btn-primary btn-flat rounded-0 px-2 py-0">Save</button><button class="btn btn-sm btn-dark btn-flat rounded-0 px-2 py-0" onclick="cancel_button($(this))" type="button">Cancel</button></td>')
  17.             $('#form-tbl').append(tr)
  18.             tr.find('[name="name"]').focus()
  19.         })
  20.  
  21.         // Edit Row
  22.         $('.edit_data').click(function() {
  23.             var id = $(this).closest('tr').attr('data-id')
  24.             $('input[name="id"]').val(id)
  25.             var count_column = $(this).closest('tr').find('td').length
  26.             $(this).closest('tr').find('td').each(function() {
  27.                 if ($(this).index() != (count_column - 1))
  28.                     $(this).attr('contenteditable', true)
  29.             })
  30.             $(this).closest('tr').find('[name="name"]').focus()
  31.             $(this).closest('tr').find('.editable').show('fast')
  32.             $(this).closest('tr').find('.noneditable').hide('fast')
  33.         })
  34.  
  35.         // Delete Row
  36.         $('.delete_data').click(function() {
  37.             var id = $(this).closest('tr').attr('data-id')
  38.             var name = $(this).closest('tr').find("[name='name']").text()
  39.             var _conf = confirm("Are you sure to delete \"" + name + "\" from the list?")
  40.             if (_conf == true) {
  41.                 $.ajax({
  42.                     url: 'api.php?action=delete',
  43.                     method: 'POST',
  44.                     data: { id: id },
  45.                     dataType: 'json',
  46.                     error: err => {
  47.                         alert("An error occured while saving the data")
  48.                         console.log(err)
  49.                     },
  50.                     success: function(resp) {
  51.                         if (resp.status == 'success') {
  52.                             alert(name + ' is successfully deleted from the list.')
  53.                             location.reload()
  54.                         } else {
  55.                             alert(resp.msg)
  56.                             console.log(err)
  57.                         }
  58.                     }
  59.                 })
  60.             }
  61.         })
  62.  
  63.         $('#form-data').submit(function(e) {
  64.             e.preventDefault();
  65.             var id = $('input[name="id"]').val()
  66.             var data = {};
  67.             // check fields promise
  68.             var check_fields = new Promise(function(resolve, reject) {
  69.                     data['id'] = id;
  70.                     $('td[contenteditable]').each(function() {
  71.                         data[$(this).attr('name')] = $(this).text()
  72.                         if (data[$(this).attr('name')] == '') {
  73.                             alert("All fields are required.");
  74.                             resolve(false);
  75.                             return false;
  76.                         }
  77.                     })
  78.                     resolve(true);
  79.                 })
  80.                 // continue only if all fields are filled
  81.             check_fields.then(function(resp) {
  82.                 if (!resp)
  83.                     return false;
  84.                 // validate email
  85.                 if (!IsEmail(data['email'])) {
  86.                     alert("Invalid Email.");
  87.                     $('[name="email"][contenteditable]').addClass('bg-danger text-light bg-opacity-50').focus();
  88.                     return false;
  89.                 } else {
  90.                     $('[name="email"][contenteditable]').removeClass('bg-danger text-light bg-opacity-50')
  91.                 }
  92.  
  93.                 // validate contact #
  94.                 if (!isContact(data['contact'])) {
  95.                     alert("Invalid Contact Number.");
  96.                     $('[name="contact"][contenteditable]').addClass('bg-danger text-light bg-opacity-50').focus();
  97.                     return false;
  98.                 } else {
  99.                     $('[name="contact"][contenteditable]').removeClass('bg-danger text-light bg-opacity-50')
  100.                 }
  101.                 $.ajax({
  102.                     url: "./api.php?action=save",
  103.                     method: 'POST',
  104.                     data: data,
  105.                     dataType: 'json',
  106.                     error: err => {
  107.                         alert('An error occured while saving the data');
  108.                         console.log(err)
  109.                     },
  110.                     success: function(resp) {
  111.                         if (!!resp.status && resp.status == 'success') {
  112.                             alert(resp.msg);
  113.                             location.reload()
  114.                         } else {
  115.                             alert(resp.msg);
  116.                         }
  117.                     }
  118.                 })
  119.             })
  120.  
  121.  
  122.         })
  123.     })
  124.     //Email Validation Function
  125. window.IsEmail = function(email) {
  126.         var regex = /^([a-zA-Z0-9_\.\-\+])+\@(([a-zA-Z0-9\-])+\.)+([a-zA-Z0-9]{2,4})+$/;
  127.         if (!regex.test(email)) {
  128.             return false;
  129.         } else {
  130.             return true;
  131.         }
  132.     }
  133.     //Contact Number Validation Function
  134. window.isContact = function(contact) {
  135.     return ($.isNumeric(contact) && contact.length == 11 && contact.substr(0, 2) == '09');
  136. }
  137.  
  138. // removing table row when cancel button triggered clicked
  139. window.cancel_button = function(_this) {
  140.     if (_this.closest('tr').attr('data-id') == '') {
  141.         _this.closest('tr').remove()
  142.     } else {
  143.         $('input[name="id"]').val('')
  144.         _this.closest('tr').find('td').each(function() {
  145.             $(this).removeAttr('contenteditable')
  146.         })
  147.         _this.closest('tr').find('.editable').hide('fast')
  148.         _this.closest('tr').find('.noneditable').show('fast')
  149.     }
  150. }

Creating Our PHP API

Lastly, we will create the PHP File that contains our API Queries for Insertinh, Updating, and Deleting the data in the database. Save the file as api.php.

  1. <?php
  2. require_once('database.php');
  3. Class API extends DBConnection{
  4.     public function __construct(){
  5.         parent::__construct();
  6.     }
  7.     public function __destruct(){
  8.                 parent::__destruct();
  9.         }
  10.  
  11.     function save_member(){
  12.         $data = "";
  13.         $id = $_POST['id'];
  14.         foreach($_POST as $k => $v){
  15.             // excluding id
  16.             if(!in_array($k,array('id'))){
  17.                 // add comma if data variable is not empty
  18.                 if(!empty($data)) $data .= ", ";
  19.                 $data .= " `{$k}` = '{$v}' ";
  20.             }
  21.         }
  22.         if(empty($id)){
  23.             // Insert New Member
  24.             $sql = "INSERT INTO `members` set {$data}";
  25.         }else{
  26.             // Update Member's Details
  27.             $sql = "UPDATE `members` set {$data} where id = '{$id}'";
  28.         }
  29.         $save = $this->conn->query($sql);
  30.         if($save && !$this->conn->error){
  31.             $resp['status'] = 'success';
  32.             if(empty($id))
  33.                 $resp['msg'] = 'New Member successfully added';
  34.             else
  35.             $resp['msg'] = 'Member\'s Details successfully updated';
  36.  
  37.         }else{
  38.             $resp['status'] = 'failed';
  39.             $resp['msg'] = 'There\'s an error occured while saving the data';
  40.             $resp['error'] = $this->conn->error;
  41.         }
  42.         return json_encode($resp);
  43.     }
  44.     function delete_member(){
  45.         $id = $_POST['id'];
  46.         // Delete Query
  47.         $delete = $this->conn->query("DELETE FROM `members` where id = '{$id}'");
  48.         if($delete){
  49.             $resp['status'] = 'success';
  50.         }else{
  51.             $resp['status'] = 'failed';
  52.             $resp['msg'] = 'There\'s an error occured while deleting the data';
  53.             $resp['error'] = $this->conn->error;
  54.         }
  55.         return json_encode($resp);
  56.     }
  57.  
  58. }
  59.  
  60. $action = isset($_GET['action']) ? $_GET['action'] : '';
  61. $api = new API();
  62. switch ($action){
  63.     case('save'):
  64.         echo $api->save_member();
  65.         break;
  66.     case('delete'):
  67.         echo $api->delete_member();
  68.         break;
  69.     default:
  70.         echo json_encode(array('status'=>'failed','error'=>'unknown action'));
  71.         break;
  72.    
  73. }
  74. ?>

DEMO

There you go! You can now test your work at your end and if an error occurred please review the scripts above. You can also download the working source code that I have created for this tutorial. Just simply click the Download Button below to download the provided source code zip file.

I hope this tutorial will help you with what you are looking for and you'll find this useful for your future web app projects using PHP and jQuery. Explore more on this website for more Free Source Codes and Tutorials.

Happy Coding :)

Tags
php
MySQL Database
php oop
php crud
PHP Tutorial for Beginners
Ajax and jQuery
Dynamic
Bootstrap
Javascript
HTML Table

Comments

Submitted by HUSSAIN MERCHANT (not verified) on Sat, 12/04/2021 - 23:55

code is not working on server its shwoing error for jquery.min.js file PLEASE HELP?

Submitted by Mark Stevens (not verified) on Mon, 12/06/2021 - 10:17

Hey love the tutorial! I'm going to break them down to learn from. I'm fairly well versed in web development and databases, BUT, I keep getting this error in the isContact: Uncaught (in promise) ReferenceError: isContact is not defined http://localhost/inline-table-form/assets/js/script.js:94 promise callback* http://localhost/inline-table-form/assets/js/script.js:81 jQuery 9 http://localhost/inline-table-form/assets/js/script.js:63 jQuery 13 I looked everywhere for it can't find it! Driving me nuts! Checked my DB for the type, changed it to a varchar, nothing! Any ideas?

Submitted by mark stevens (not verified) on Mon, 12/06/2021 - 21:24

I found the error! In the script.js file on line 94 there is a lowercase "i" in isContact, should be upper case "IsContact". FYI

Submitted by AbdullahK (not verified) on Wed, 01/31/2024 - 21:11

Hi, Thank you for your codes. I used your codes without any problems. But when i create second form-table and different database and tables in same page, i have got some problems. Secondly form-table is not working. First table is working but not good. Can you explain to me for what can i do? Best Regards

Add new comment

About text formats