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.
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
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');
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;
}
}
?>
Initially, all the records are fetched from the database and listed in data table with inline editing feature.
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.
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>
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.
action=edit
),
action=delete
),
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();
}
?>
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
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?
How to record information and how to file information
Works like a charm!!.. Specially DB.class.
Totally simple and works!
How can we use OR option
Are there any instructions on how to do this with multiple tables please?
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
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);
HI,
Thanks for this amazing script!
How can I add/use the where condition?
Thanks a lot
Yes, you can easily. See the example below.
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
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 !
Hi,
It is working. I changed table name. In two files, I need to change. I found it.
Thanks, DUDE.