Skip to main content

Editable HTML Table with jQuery, PHP & MySQL

Updated on: coderszine team PHP, Tutorial

In our previous tutorial, we have explained how to implement Event Calendar with jQuery , PHP and MySQL. In this tutorial, you will learn how to implement Editable HTML Table with jQuery, PHP and MySQL.

Live Editable HTML Table or Inline HTML Table Edit is a feature of web applications to allow users to edit HTML table column value. This is very user friendly feature as makes possible to edit save particular value with reloading page.

So if you’re looking for implementing editable HTML table, then it’s very easy using jQuery TableEdit plugin. In this tutorial you will learn how to implement Editable HTML Table with jQuery, PHP and MYSQL. You would also like to checkout HTML Table Data Export to Excel with jQuery tutorial to export html table data to excel format.

We will use jQuery TableEdit plugin to make HTML table editable and handle edit save when editing done.

Also, read:

So let’s create example to implement inline editable HTML table with jQuery, PHP and MySQL. The file structure of this example is following.

  • index.php
  • editable.js
  • save_edit.php
Step1: Create MySQL Database Table

First we will create MySQL database table developers to store employee records.

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
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

We will insert few records into developers table to display in HTML table.

INSERT INTO `developers` (`id`, `name`, `skills`, `address`, `gender`, `designation`, `age`) VALUES
(1, 'Smith', 'Java', 'Newyork', 'Male', 'Software Engineer', 34),
(2, 'David', 'PHP', 'London', 'Male', 'Web Developer', 28),
(3, 'Rhodes', 'jQuery', 'New Jersy', 'Male', 'Web Developer', 30),
(4, 'Sara', 'JavaScript', 'Delhi', 'Female', 'Web Developer', 25),
(5, 'Shyrlin', 'NodeJS', 'Tokiyo', 'Female', 'Programmer', 35),
(6, 'Steve', 'Angular', 'London', 'Male', 'Web Developer', 28),
(7, 'Cook', 'MySQL', 'Paris', 'Male', 'Web Developer', 26),
(8, 'Root', 'HTML', 'Paris', 'Male', 'Web Developer', 28),
(9, 'William', 'jQuery', 'Sydney', 'Male', 'Web Developer', 23),
(10, 'Nathan', 'PHP', 'London', 'Male', 'Web Developer', 28),
(11, 'Shri', 'PHP', 'Delhi', 'Male', 'Web Developer', 38),
(12, 'Jay', 'PHP', 'Delhi, India', 'Male', 'Web Developer', 30);
Step2: Inlcude jQuery, Bootstrap and Table Edit jQuery Plugin

As we will implement HTML Table Edit with Bootstrap and jQuery, so in index.php file, we will include files related to jQuery, Bootstrap and jQuery TableEdit Plugin.

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js"></script>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
<script type="text/javascript" src="dist/jquery.tabledit.js"></script>
Step3: Create Bootstrap HTML and Display Records

Now in .index.php file, we will create Bootstrap HTML table and display records from MySQL database table developers with PHP.

<table id="employeeTable" class="table table-striped">
	<thead>
		<tr>
			<th>Id</th>
			<th>Name</th>
			<th>Gender</th>
			<th>Age</th>					
			<th>Address</th>
		</tr>
	</thead>
	<tbody>
		<?php 
		include_once("inc/db_connect.php");
		$sqlQuery = "SELECT id, name, gender, address, age FROM developers LIMIT 5";
		$result = mysqli_query($conn, $sqlQuery) or die("database error:". mysqli_error($conn));
		while( $emp = mysqli_fetch_assoc($result) ) {
		?>
		   <tr id="<?php echo $emp ['id']; ?>">
		   <td><?php echo $emp ['id']; ?></td>
		   <td><?php echo $emp ['name']; ?></td>
		   <td><?php echo $emp ['gender']; ?></td>
		   <td><?php echo $emp ['age']; ?></td> 		   
		   <td><?php echo $emp ['address']; ?></td>   
		   </tr>
		<?php } ?>
	</tbody>
</table>
Step4: Implement HTML Table Editable

In editable.js file, we will implement HTML table editable using jQuery Tabledit plugin. We will configure HTML table columns to handle column edit and save. We will also make Ajax request to save_edit.php to save changes to database when table field edited and press Enter key.

$(document).ready(function(){
	$('#employeeTable').Tabledit({
		deleteButton: false,
		editButton: false,   		
		columns: {
		  identifier: [0, 'id'],                    
		  editable: [[1, 'name'], [2, 'gender'], [3, 'age'], [4, 'address']]
		},
		hideIdentifier: true,
		url: 'save_edit.php'		
	});
});
Step5: Save Editable Table Changes to Database

Now finally in save_edit.php file, we will handle functionality to save edited column value to database table.

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

You may also like:

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