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

By: CodexWorld | In: PHP | Last Updated: Jan 23, 2023

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

Download Source Code View Demo

Ajax File Upload with Form Data using PHP

Add a Local File Picker to Image Dialog in TinyMCE

12 Comments

Graeme Said...

Excellent, really useful and easy to follow. After saving, the script returns the new value to the data table. OK for plain text input, not so much for drop-down select lists. Can you suggest a simple workaround for different types of input box besides text?

October 18, 2023 at 12:38 PM
Nee Said...

How to record information and how to file information

May 21, 2022 at 9:32 AM
Kurnia Said...

Works like a charm!!.. Specially DB.class.
Totally simple and works!

September 27, 2021 at 1:28 PM
Samir Said...

How can we use OR option

March 23, 2021 at 10:23 AM
Gareth Said...

Are there any instructions on how to do this with multiple tables please?

September 24, 2019 at 8:04 PM
Tunde Said...

Hello, this script is working; i can display my table but i can add my WHERE Clause. Can the script be simplified? or can you this condition for me in the UPDATE? My WHERE clause is where Subject =”…” AND Session=”…”

Thank u

November 12, 2018 at 5:23 PM

CodexWorld Said...

You can specify multiple WHERE conditions in getRows() function. As per your requirement, the example code is given below:

// Get users from database
$con = array(
    'where' => array(
        'Subject' => '...',
        'Session' => '...'
    )
);
$users = $db->getRows('users', $con);

November 12, 2018 at 9:01 PM

Domenico Said...

HI,
Thanks for this amazing script!

How can I add/use the where condition?

Thanks a lot

August 23, 2018 at 12:45 PM

CodexWorld Said...

Yes, you can easily. See the example below.

$conditions = array(
    'where' => array(
        'id' => $id
    )
);
$users = $db->getRows('users', $conditions);

August 24, 2018 at 6:27 PM

Aram Said...

Hello there. thanks for this wonderful example.
I added a checkbox in this example. There are “yes” and “no” options. but there was no change in the database. How to add CheckBox Thank you

May 11, 2018 at 10:15 PM
GeekyMedusa Said...

How to add insert functionality whenever we click on ADD button it should add a row in the last of the table with a insert button and then adding data to database. Kindly help Stuck in it ! Thanks in advance !

March 8, 2018 at 10:51 AM
Sam Said...

Hi,

It is working. I changed table name. In two files, I need to change. I found it.
Thanks, DUDE.

December 28, 2017 at 9:18 PM

Cancel

MOBILE APP DEVELOPMENT

Do you need a mobile app for your business? We are here to help you out!
We develop cost-effective cross-platform mobile Apps with complete solutions. Build your Android and iOS at an affordable cost.

GET A FREE QUOTE

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

Create Database Table

Database Class (DB.class.php)

PHP Datagrid with Inline Editing (index.php)

Edit and Delete Data (userAction.php)

Conclusion

RELATED TUTORIALS

12 Comments

Leave a reply

Cancel

MOBILE APP DEVELOPMENT

Do you need a mobile app for your business? We are here to help you out!
We develop cost-effective cross-platform mobile Apps with complete solutions. Build your Android and iOS at an affordable cost.

TRENDING TUTORIALS

TOPICSall topics

LATEST HOW TO GUIDES

ABOUT US

Contact

Terms

Extra Links

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

Create Database Table

Database Class (DB.class.php)

PHP Datagrid with Inline Editing (index.php)

Edit and Delete Data (userAction.php)

Conclusion

RELATED TUTORIALS

12 Comments

Leave a reply

Cancel

MOBILE APP DEVELOPMENT

Do you need a mobile app for your business? We are here to help you out!We develop cost-effective cross-platform mobile Apps with complete solutions. Build your Android and iOS at an affordable cost.

TRENDING TUTORIALS

TOPICSall topics

LATEST HOW TO GUIDES

Do you need a mobile app for your business? We are here to help you out!
We develop cost-effective cross-platform mobile Apps with complete solutions. Build your Android and iOS at an affordable cost.