Data Table Inline Editing using jQuery, Ajax, PHP and MySQL

Inline data editing provides an easy way to edit data in table cells without loading another component. If your web application has data grid functionality, inline edit & delete is the must-have feature. The user can edit and delete content on the same page by clicking the row in the table. Inline table data editing provides a better user experience and makes the web application user-friendly.

Live table editing functionality can be easily integrated with jQuery and Ajax. In this tutorial, we will show you how to implement inline edit and delete functionality using jQuery, AJAX, PHP, and MySQL.

The following functionality will be implemented in our example code.

  • List data as a grid view in a table.
  • Edit and update table row data inline.
  • Delete table row data inline.

Before getting started to build an inline data editing script, take a look at the file structure.

inline_table_data_editing_with_php/
├── DB.class.php
├── index.php
├── userAction.php
├── images/
├── js/
│   └── jquery.min.js
└── css/
    ├── style.css
    └── bootstrap.min

Create Database Table

A table is required in the database to store the HTML table for inline editing dynamically. The following SQL creates a members table in the database with some basic fields.

CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `last_name` varchar(25) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL DEFAULT current_timestamp(),
  `status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Now, insert some data in the members table.

INSERT INTO `members` (`id`, `first_name`, `last_name`, `email`, `created`, `modified`, `status`) VALUES
(NULL, 'John', 'Doe', 'john.doe@gmail.com', NOW(), NOW(), 'Active'),
(NULL, 'Gary', 'Riley', 'gary@hotmail.com', NOW(), NOW(), 'Active'),
(NULL, 'Edward', 'Siu', 'siu.edward@gmail.com', NOW(), NOW(), 'Active');

Database Class (DB.class.php)

The DB Class handles the database related operations (connect, update, and delete) with PHP and MySQL.

  • __construct() – Connect and select the MySQL database.
  • getRows() – Fetch data from the members table and returns the data as an array.
  • update() – Update member data in the database.
  • delete() – Remove member data from the database.

Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) in the respective variables.

<?php 

class DB{
    private 
$dbHost     "localhost";
    private 
$dbUsername "root";
    private 
$dbPassword "root";
    private 
$dbName     "codexworld_db";
    private 
$table      "members";
    
    public function 
__construct(){
        if(!isset(
$this->db)){
            
// Connect to the database
            
$conn = new mysqli($this->dbHost$this->dbUsername$this->dbPassword$this->dbName);
            if(
$conn->connect_error){
                die(
"Failed to connect with MySQL: " $conn->connect_error);
            }else{
                
$this->db $conn;
            }
        }
    }
    
    
/*
     * Returns rows from the database based on the conditions
     * @param array select, where, order_by, limit and return_type conditions
     */
    
public function getRows($conditions = array()){
        
$sql 'SELECT ';
        
$sql .= array_key_exists("select",$conditions)?$conditions['select']:'*';
        
$sql .= " FROM {$this->table} ";
        if(
array_key_exists("where",$conditions)){
            
$sql .= ' WHERE ';
            
$i 0;
            foreach(
$conditions['where'] as $key => $value){
                
$pre = ($i 0)?' AND ':'';
                
$sql .= $pre.$key." = '".$value."'";
                
$i++;
            }
        }
        
        if(
array_key_exists("order_by",$conditions)){
            
$sql .= ' ORDER BY '.$conditions['order_by']; 
        }else{
            
$sql .= " ORDER BY id ASC "
        }
        
        if(
array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            
$sql .= ' LIMIT '.$conditions['start'].','.$conditions['limit']; 
        }elseif(!
array_key_exists("start",$conditions) && array_key_exists("limit",$conditions)){
            
$sql .= ' LIMIT '.$conditions['limit']; 
        }
        
        
$result $this->db->query($sql);
        
        if(
array_key_exists("return_type"$conditions) && $conditions['return_type'] != 'all'){
            switch(
$conditions['return_type']){
                case 
'count':
                    
$data $result->num_rows;
                    break;
                case 
'single':
                    
$data $result->fetch_assoc();
                    break;
                default:
                    
$data '';
            }
        }else{
            if(
$result->num_rows 0){
                while(
$row $result->fetch_assoc()){
                    
$data[] = $row;
                }
            }
        }
        return !empty(
$data)?$data:false;
    }
    
    
/*
     * Update data into the database
     * @param array the data for updating into the table
     * @param array where condition on updating data
     */
    
public function update($data$conditions){
        if(!empty(
$data) && is_array($data)){
            
$colvalSet '';
            
$whereSql '';
            
$i 0;
            if(!
array_key_exists('modified',$data)){
                
$data['modified'] = date("Y-m-d H:i:s");
            }
            foreach(
$data as $key=>$val){
                
$pre = ($i 0)?', ':'';
                
$colvalSet .= $pre.$key."='".$this->db->real_escape_string($val)."'";
                
$i++;
            }
            if(!empty(
$conditions)&& is_array($conditions)){
                
$whereSql .= ' WHERE ';
                
$i 0;
                foreach(
$conditions as $key => $value){
                    
$pre = ($i 0)?' AND ':'';
                    
$whereSql .= $pre.$key." = '".$value."'";
                    
$i++;
                }
            }
            
$query "UPDATE {$this->table} SET ".$colvalSet.$whereSql;
            
$update $this->db->query($query);
            return 
$update?$this->db->affected_rows:false;
        }else{
            return 
false;
        }
    }
    
    
/*
     * Delete data from the database
     * @param array where condition on deleting data
     */
    
public function delete($conditions){
        
$whereSql '';
        if(!empty(
$conditions)&& is_array($conditions)){
            
$whereSql .= ' WHERE ';
            
$i 0;
            foreach(
$conditions as $key => $value){
                
$pre = ($i 0)?' AND ':'';
                
$whereSql .= $pre.$key." = '".$value."'";
                
$i++;
            }
        }
        
$query "DELETE FROM {$this->table} $whereSql";
        
$delete $this->db->query($query);
        return 
$delete?true:false;
    }
}

?>

PHP Datagrid with Inline Editing (index.php)

Initially, all the records are fetched from the database and listed in data table with inline editing feature.

  • The member’s data will be retrieved from the database and listed in the data table.
  • The Edit and Delete buttons are added to each row of the table.
  • On clicking the Edit button, table cells will be editable and a save button will appear on this row.
  • On clicking the Save button, the respective row data will be updated.
  • On clicking the Delete button, a confirm button will appear and the respective table row data will be deleted.

Bootstrap Library:
Include the Bootstrap library to apply styles to the data table and input fields.

<link rel="stylesheet" href="css/bootstrap.min.css">

HTML & PHP Code:
The getRows() function of the DB class is used to fetch the member’s data from the database. Initially, all the records are listed in an HTML table, and each row has an edit & delete button.

<table class="table table-striped">
    <thead>
        <tr>
            <th>ID</th>
            <th>First Name</th>
            <th>Last Name</th>
            <th>Email</th>
            <th>Status</th>
            <th>Action</th>
        </tr>
    </thead>
    <tbody id="userData">
    <?php 
        
// Load and initialize database class
        
require_once 'DB.class.php';
        
$db = new DB();

        
// Get members data from database
        
$members $db->getRows();

        if(!empty(
$members)){
            foreach(
$members as $row){
        
?> <tr id="<?php echo $row['id']; ?>"> <td><?php echo $row['id']; ?></td> <td> <span class="editSpan first_name"><?php echo $row['first_name']; ?></span> <input class="form-control editInput first_name" type="text" name="first_name" value="<?php echo $row['first_name']; ?>" style="display: none;"> </td> <td> <span class="editSpan last_name"><?php echo $row['last_name']; ?></span> <input class="form-control editInput last_name" type="text" name="last_name" value="<?php echo $row['last_name']; ?>" style="display: none;"> </td> <td> <span class="editSpan email"><?php echo $row['email']; ?></span> <input class="form-control editInput email" type="text" name="email" value="<?php echo $row['email']; ?>" style="display: none;"> </td> <td> <span class="editSpan status"><?php echo $row['status']; ?></span> <select class="form-control editInput status" name="status" style="display: none;"> <option value="Active" <?php echo $row['status'] == 'Active'?'selected':''?>>Active</option> <option value="Inactive" <?php echo $row['status'] == 'Inactive'?'selected':''?>>Inactive</option> </select> </td> <td> <button type="button" class="btn btn-default editBtn"><i class="pencil"></i></button> <button type="button" class="btn btn-default deleteBtn"><i class="trash"></i></button> <button type="button" class="btn btn-success saveBtn" style="display: none;">Save</button> <button type="button" class="btn btn-danger confirmBtn" style="display: none;">Confirm</button> <button type="button" class="btn btn-secondary cancelBtn" style="display: none;">Cancel</button> </td> </tr>         <?php
            
}
        }else{
            echo 
'<tr><td colspan="6">No record(s) found...</td></tr>';
        }
    
?> </tbody> </table>

jQuery Library:
The jQuery Ajax is used for inline editing functionality without page refresh, so include the jQuery library first.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.3/jquery.min.js"></script>

JavaScript Code:
The following JavaScript code handles the inline edit and delete functionality using jQuery and Ajax.

  • The edit and delete requests are posted to the server-side script (userAction.php) via Ajax.
<script>
$(document).ready(function(){
    $('.editBtn').on('click',function(){
        //hide edit span
        $(this).closest("tr").find(".editSpan").hide();

        //show edit input
        $(this).closest("tr").find(".editInput").show();

        //hide edit button
        $(this).closest("tr").find(".editBtn").hide();

        //hide delete button
        $(this).closest("tr").find(".deleteBtn").hide();
        
        //show save button
        $(this).closest("tr").find(".saveBtn").show();

        //show cancel button
        $(this).closest("tr").find(".cancelBtn").show();
        
    });
    
    $('.saveBtn').on('click',function(){
        $('#userData').css('opacity', '.5');

        var trObj = $(this).closest("tr");
        var ID = $(this).closest("tr").attr('id');
        var inputData = $(this).closest("tr").find(".editInput").serialize();
        $.ajax({
            type:'POST',
            url:'userAction.php',
            dataType: "json",
            data:'action=edit&id='+ID+'&'+inputData,
            success:function(response){
                if(response.status == 1){
                    trObj.find(".editSpan.first_name").text(response.data.first_name);
                    trObj.find(".editSpan.last_name").text(response.data.last_name);
                    trObj.find(".editSpan.email").text(response.data.email);
                    trObj.find(".editSpan.status").text(response.data.status);
                    
                    trObj.find(".editInput.first_name").val(response.data.first_name);
                    trObj.find(".editInput.last_name").val(response.data.last_name);
                    trObj.find(".editInput.email").val(response.data.email);
                    trObj.find(".editInput.status").val(response.data.status);
                    
                    trObj.find(".editInput").hide();
                    trObj.find(".editSpan").show();
                    trObj.find(".saveBtn").hide();
                    trObj.find(".cancelBtn").hide();
                    trObj.find(".editBtn").show();
                    trObj.find(".deleteBtn").show();
                }else{
                    alert(response.msg);
                }
                $('#userData').css('opacity', '');
            }
        });
    });

    $('.cancelBtn').on('click',function(){
        //hide & show buttons
        $(this).closest("tr").find(".saveBtn").hide();
        $(this).closest("tr").find(".cancelBtn").hide();
        $(this).closest("tr").find(".confirmBtn").hide();
        $(this).closest("tr").find(".editBtn").show();
        $(this).closest("tr").find(".deleteBtn").show();

        //hide input and show values
        $(this).closest("tr").find(".editInput").hide();
        $(this).closest("tr").find(".editSpan").show();
    });
    
    $('.deleteBtn').on('click',function(){
        //hide edit & delete button
        $(this).closest("tr").find(".editBtn").hide();
        $(this).closest("tr").find(".deleteBtn").hide();
        
        //show confirm & cancel button
        $(this).closest("tr").find(".confirmBtn").show();
        $(this).closest("tr").find(".cancelBtn").show();
    });
    
    $('.confirmBtn').on('click',function(){
        $('#userData').css('opacity', '.5');

        var trObj = $(this).closest("tr");
        var ID = $(this).closest("tr").attr('id');
        $.ajax({
            type:'POST',
            url:'userAction.php',
            dataType: "json",
            data:'action=delete&id='+ID,
            success:function(response){
                if(response.status == 1){
                    trObj.remove();
                }else{
                    trObj.find(".confirmBtn").hide();
                    trObj.find(".cancelBtn").hide();
                    trObj.find(".editBtn").show();
                    trObj.find(".deleteBtn").show();
                    alert(response.msg);
                }
                $('#userData').css('opacity', '');
            }
        });
    });
});
</script>

Edit and Delete Data (userAction.php)

This userAction.php file handles the Ajax requests coming from the index.php file. Based on the action type the member data is updated/deleted from the database.

  • If the edit request is posted (action=edit),
    • The input data is retrieved using the $_POST method in PHP.
    • Update member data in the database based on the ID using PHP and MySQL.
  • If the delete request is posted (action=delete),
    • Delete member data from the database based on the ID using PHP and MySQL.

The response is sent back to the success method of Ajax request in JSON format.

<?php 

// Load and initialize database class
require_once 'DB.class.php';
$db = new DB();

if((
$_POST['action'] == 'edit') && !empty($_POST['id'])){
    
// Update data
    
$userData = array(
        
'first_name' => $_POST['first_name'],
        
'last_name' => $_POST['last_name'],
        
'email' => $_POST['email'],
        
'status' => $_POST['status']
    );
    
$condition = array(
        
'id' => $_POST['id']
    );
    
$update $db->update($userData$condition);

    if(
$update){
        
$response = array(
            
'status' => 1,
            
'msg' => 'Member data has been updated successfully.',
            
'data' => $userData
        
);
    }else{
        
$response = array(
            
'status' => 0,
            
'msg' => 'Something went wrong!'
        
);
    }
    
    echo 
json_encode($response);
    exit();
}elseif((
$_POST['action'] == 'delete') && !empty($_POST['id'])){
    
// Delete data
    
$condition = array('id' => $_POST['id']);
    
$delete $db->delete($condition);

    if(
$delete){
        
$returnData = array(
            
'status' => 1,
            
'msg' => 'Member data has been deleted successfully.'
        
);
    }else{
        
$returnData = array(
            
'status' => 0,
            
'msg' => 'Something went wrong!'
        
);
    }
    
    echo 
json_encode($returnData);
    exit();
}

?>

Conclusion

This example script helps you to integrate the table data inline editing functionality in the web application with jQuery using PHP and MySQL. The data table with inline editing feature is very useful when you want to allow users to manage data dynamically on a single page. The user doesn’t require to navigate multiple pages for data management operations (view, add, edit, and delete).

Do you want to get implementation help, or enhance the functionality of this script? Click here to Submit Service Request

12 Comments

  1. Graeme Said...
  2. Nee Said...
  3. Kurnia Said...
  4. Samir Said...
  5. Gareth Said...
  6. Tunde Said...
    • CodexWorld Said...
  7. Domenico Said...
    • CodexWorld Said...
  8. Aram Said...
  9. GeekyMedusa Said...
  10. Sam Said...

Leave a reply

keyboard_double_arrow_up