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.

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 can view the live demo from the Demo link and can download the script from the Download link below.
Demo [sociallocker]Download[/sociallocker]