Inline Editing using PHP MySQL and jQuery Ajax

During developing PHP project, sometimes we need to implement edit in place or inline editing to allow users to edit content on same page by clicking on it. In this tutorial, we are going to explain how to handle edit in place or inline editing using jQuery with PHP and MySQL.

You may also like:

• Create Live Editable Table with jQuery, PHP and MySQL
• Live Add Edit Delete datatables Records with Ajax, PHP and MySQL

In this tutorial we will use HTML contenteditable Attribute to handle inline editing. Here we will set contenteditable Attribute value as true like contenteditable=”true” that will allow user to edit content on clicking the content in a page.

Whenever user will edit or change column data and focus out, it will call PHP function using Ajax to update changed data into MySQL table.

 
Following files needed for this inline edit example

• db_connect.php : This file used to create connection with MySQL.
• index.php : This file used to display records into table and inline editing options.
• functions.js : This file contains JavaScript functions to get changed value and make Ajax request to PHP.
• saveInlineEdit.php : This file will be called on ajax request and update data into MySQL.

 

Step 1: You need to include bootstrap css file and jQuery library into head section of page.

<script type="text/javascript" src="jquery-1.11.1.min.js"></script>
<link rel="stylesheet" type="text/css" href="bootstrap.min.css"/>

Step 2: You need to create create connection file db_connect.php for MySQL database connection with PHP.

$servername = "localhost";
$username = "root";
$password = "";
$dbname = "phpzag_demos";
$conn = mysqli_connect($servername, $username, $password, $dbname) 
or die("Connection failed: " . mysqli_connect_error());

Step 3: You need to get records from MySQL table and display in a page with editing options in index.php.

<?php
$sql = "SELECT id, employee_name, employee_salary, employee_age 
FROM employee LIMIT 10";
$resultset = mysqli_query($conn, $sql) or 
die("database error:". mysqli_error($conn));
?>
<table class="table table-condensed 
table-hover table-striped bootgrid-table">
<thead>
<tr>
<th>Employee Name</th>
<th>Salary</th>
<th>Age</th>
</tr>
</thead>
<tbody>
<?php
while( $rows = mysqli_fetch_assoc($resultset) ) {
?>
<tr>
<td contenteditable="true" 
data-old_value="<?php echo $rows["employee_name"]; ?>"
 onBlur="saveInlineEdit(this,'employee_name','<?php 
echo $rows["id"]; ?>')" 
onClick="highlightEdit(this);"><?php echo $rows["employee_name"]; ?></td>
<td contenteditable="true" 
data-old_value="<?php echo $rows["employee_salary"]; ?>" 
onBlur="saveInlineEdit(this,'employee_salary','<?php echo 
$rows["id"]; ?>')" onClick="highlightEdit(this);"><?php 
echo $rows["employee_salary"]; ?></td>
<td contenteditable="true" data-old_value="<?php 
echo $rows["employee_age"]; ?>" 
onBlur="saveInlineEdit(this,'employee_age','<?php 
echo $rows["id"]; ?>')" 
onClick="highlightEdit(this);"><?php echo $rows["employee_age"]; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>

In above code, I have displayed employee records with HTML attribute contenteditable=”true” to make column editable and also an attribute data-old_value to keep old value to check before making Ajax request to update changed value in MySQL database table. I have used function saveInlineEdit() on blur event to update changed value and function highlightEdit() to highlight column in edit mode.

Step 4: Now need to create jQuery AjAX request to PHP function to update changed data into MySQL using PHP script.
Here is JavaScript function saveInlineEdit that is checking for changed value. If value is changed then it will make Ajax request to PHP function saveInlineEdit.php by passing required values. Finally on success of edit request, updating data attribute data-old_value with updated value to check for changed value to make Ajax request.

function saveInlineEdit(editableObj,column,id) {
// no change change made then return false
if($(editableObj).attr('data-old_value') === editableObj.innerHTML)
return false;
// send ajax to update value
$(editableObj).css("background","#FFF url(loader.gif) no-repeat right");
$.ajax({
url: "saveInlineEdit.php",
type: "POST",
dataType: "json",
data:'column='+column+'&value='+editableObj.innerHTML+'&id='+id,
success: function(response) {
// set updated value as old value
$(editableObj).attr('data-old_value',editableObj.innerHTML);
$(editableObj).css("background","#FDFDFD");
},
error: function () {
console.log("errr");
}
});
}

Step 5: Finally updating MySQL database table employee with changed data in saveInlineEdit.php.

<?php
$sql = "UPDATE employee set " . $_POST["column"] . " = '".$_POST["value"]."'
 WHERE id=".$_POST["id"];
mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
?>

Step 6: To run this example, you need MySQL database table with records. So you just need to run this code to create MySQL table and insert data to run example.

CREATE TABLE IF NOT EXISTS 'employee' (
'id' int(11) NOT NULL AUTO_INCREMENT COMMENT 'primary key',
'employee_name' varchar(255) NOT NULL COMMENT 'employee name',
'employee_salary' double NOT NULL COMMENT 'employee salary',
'employee_age' int(11) NOT NULL COMMENT 'employee age',
PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='datatable 
demo table' AUTO_INCREMENT=58 ;
INSERT INTO 'employee' ('id', 'employee_name','employee_salary', 'employee_age')
 VALUES
(1, 'Tiger Nixon', 320800, 61),
(2, 'Garrett Winters', 170750, 63),
(3, 'Ashton Cox', 86000, 66),
(4, 'Cedric Kelly', 433060, 22),
(5, 'Airi Satou', 162700, 33),
(6, 'Brielle Williamson', 372000, 61),
(7, 'Herrod Chandler', 137500, 59),
(8, 'Rhona Davidson', 327900, 55),
(9, 'Colleen Hurst', 205500, 39),
(10, 'Sonya Frost', 103600, 23);

You may also like:

• Build Live Chat System with Ajax, PHP & MySQL
• Create Event Calendar with jQuery, PHP and MySQL
• Build Invoice System with PHP & MySQL
• Push Notification System with PHP & MySQL
• Create Bootstrap Cards with PHP and MySQL
• Build Content Management System with PHP & MySQL
• Convert Unix Timestamp To Readable Date Time in PHP
• Ajax Drop Down Selection Data Load with PHP & MySQL
• Inventory Management System with Ajax, PHP & MySQL
• Drag and Drop File Upload using jQuery and PHP
• Load Dynamic Content in Bootstrap Popover with Ajax, PHP & MySQL

This is a simple example to implement inline edit functionality in your PHP project with jQuery. You can view the live demo from the Demo link. If you want to download running demo script, you can download the script from the Download link below.

Demo  Download