Create Live Editable Table with jQuery, PHP and MySQL

PHPZAG Team JQuery, PHP

In our previous tutorial, we have explained how to implement Live Add Edit Delete datatables Records with Ajax, PHP and MySQL. In this tutorial, we will explain How To Create Live Editable Table with jQuery, PHP and MySQL.

Live HTML table edit or inline table edit is a very user friendly feature that enable users to edit HTML table value directly by clicking on table cells. In our previous HTML table tutorial you have learned HTML Table Data Export to Excel, CSV and Text using jQuery. In this tutorial you will learn how to implement live editable HTML table with jQuery and PHP. We will use jQuery plugin Tabledit that provides AJAX enabled in place editing for HTML table cells.

Also, read:

We will explain and cover this tutorial in easy steps with live demo to create editable HTML table and save live edit changes into MySQL database table. You can also download source code of live demo.


As we have covered this tutorial with live demo to implement live editable HTML table with jQuery and PHP, so the file structure for this example is following.

  • index.php
  • custom_table_edit.js
  • live_edit.php

Steps1: Create MySQL Database Table
As we will display data record in HTML Table from MySQL database and implement live HTML table edit, so first we will create MySQL table developers and then insert few records to display.
CREATE TABLE `developers` (
`id` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
`skills` varchar(255) NOT NULL,
`address` varchar(255) NOT NULL,
`gender` varchar(255) NOT NULL,
`designation` varchar(255) NOT NULL,
`age` int(11) NOT NULL,
`image` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Steps2: Include jQuery and Tabledit plugin
As we will handle HTML Table data export using jQuery plguin Tabledit, so we will include jQuery and plugin files in index.php.
<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<script type="text/javascript" src="dist/jquery.tabledit.js"></script>
<script type="text/javascript" src="custom_table_edit.js"></script>

Steps3: Create HTML Table with Data from MySQL
Now in index.php, we will create HTML table with dynamic data from MySQL database.

<table id="data_table" class="table table-striped">
<thead>
<tr>
<th>Id</th>
<th>Name</th>
<th>Gender</th>
<th>Age</th>
<th>Designation</th>
<th>Address</th>
</tr>
</thead>
<tbody>
<?php
$sql_query = "SELECT id, name, gender, address, designation, age FROM developers LIMIT 10";
$resultset = mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
while( $developer = mysqli_fetch_assoc($resultset) ) {
?>
<tr id="<?php echo $developer ['id']; ?>">
<td><?php echo $developer ['id']; ?></td>
<td><?php echo $developer ['name']; ?></td>
<td><?php echo $developer ['gender']; ?></td>
<td><?php echo $developer ['age']; ?></td>
<td><?php echo $developer ['designation']; ?></td>
<td><?php echo $developer ['address']; ?></td>
</tr>
<?php } ?>
</tbody>
</table>


Steps4: Make HTML table Editable with Tabledit Plugin
In custom_table_edit.js, we will call Tabledit() function with HTML table id to make table cells editable with required configuration. We will also use url property to make Ajax call to live_edit.php on edit save to save edit changes into MySQL database table.

$(document).ready(function(){
$('#data_table').Tabledit({
deleteButton: false,
editButton: false,
columns: {
identifier: [0, 'id'],
editable: [[1, 'name'], [2, 'gender'], [3, 'age'], [4, 'designation'], [5, 'address']]
},
hideIdentifier: true,
url: 'live_edit.php'
});
});

Steps5: Save Live HTML Table Edit into MySQL Database
Now finally in live_edit.php, we will handle functionality to update edit changes into MySQL database table.


<?php
include_once("db_connect.php");
$input = filter_input_array(INPUT_POST);
if ($input['action'] == 'edit') {
$update_field='';
if(isset($input['name'])) {
$update_field.= "name='".$input['name']."'";
} else if(isset($input['gender'])) {
$update_field.= "gender='".$input['gender']."'";
} else if(isset($input['address'])) {
$update_field.= "address='".$input['address']."'";
} else if(isset($input['age'])) {
$update_field.= "age='".$input['age']."'";
} else if(isset($input['designation'])) {
$update_field.= "designation='".$input['designation']."'";
}
if($update_field && $input['id']) {
$sql_query = "UPDATE developers SET $update_field WHERE id='" . $input['id'] . "'";
mysqli_query($conn, $sql_query) or die("database error:". mysqli_error($conn));
}
}
?>

You will also like these tutorials:


You can view the live demo from the Demo link and can download the script from the Download link below.
Demo Download

48 thoughts on “Create Live Editable Table with jQuery, PHP and MySQL”

  2. I’m getting to know jQuery now, I’ve got help with the “Create Live Editable Table with jQuery, PHP and MySQL”.
    Thanks for the code.

    1. You need to make changes in custom_table_edit.js to add more columns in editable properties.

  4. Hi!
    thank you for your helping code.
    I want to know what I have to do if I want to get updates in my live editable table when some data are changed from database

    1. Please download complete project code from download link located at the end of tutorial. Thanks.

  6. Hi,

    I’m getting $input = filter_input_array(INPUT_POST); as ‘Null’ (in file live_edit.php)

    Due to this sql query is not executing at all…

    Can you please help?

    Best rgds,
    Aashish

  7. Hi, thanks it is working fine.
    How can I auto refresh the index.php when a line is updated ?

    Thanks

  8. how do i can add new fields to save into database o delete fields
    with filds i means full rows add or delete it.

    somebody can help me with that, plz!

    1. Try to bebug the code and find the cause of issue. you can share the error if any fix the issue. thanks!

    2. I was having the same issue. In my case, I didn’t have the correct table name here:
      $sql_query = “UPDATE [table name] SET $update_field…
      in live_edit.php.

      In the php tutorial, take note that “developer” is a variable and “developers” is the table.

  13. Awesome, handy, I use this for a several things.
    If you’re looking for a suggestion, a little search box would be a useful add here.
    Thank you so much, I enjoy using this script

  14. Hello thanks for share!

    I can’t get the save and delete buttons to work, what would be the syntax that I should add?

      1. Hi,
        thanks for great tutorial!
        Could you be so kind to help me get the delete button to work? So far it’s visible but not making changes..

        1. It is working in download code, you can try this. if still issue, try to debug, may be there error that causing issue. thanks!

          1. Hi,
            i found a mistake in my own sql query.. Sorry to bother you! Now it’s working briliantly 😉
            Thanks!

  17. after editing the cell, you need to press enter to save it, or it wont be saved, is there any solutions for the edited to save after cell loses focus or when clicked to another cell?

  18. Hello, thanks for the code, this is helpful and working.
    But, I face a problem. I can add columns by editing live_edit.php , custom_table_edit.php and also index.php.
    But, when I try to implement to other table. Or, by removing column ‘name’, the script becoming error and I dont know why. Please help me.

  19. it work only input type is text
    what i can do for input type is email or others

  20. OMAGAD THIS SCRIPT IS WONDERFUL
    I’m using it with classic asp. I have to tweak one or two things right now, but I made it working in a few time 🙂

  21. This is great stuff. Thanks for providing the code archive >> especially helpful to a noob. Works out of the box.

  23. Very nice script.
    I have got the script working, but it demands a bit more clicks (mouse or finger) so it is less useful than I thought at the first.
    Would it be possible that in the live editor that an ENTER is not necessary? (so a save is automatically done when another field is opened, or any other way?)
    Also would it be possible to change dates from 2022-02-08 format to 08-02-2022 format?

    1. You can make changes as per your need by passing select field instea dof input, thanks!

Comments are closed.