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:

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:

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

14 thoughts on “Inline Editing using PHP MySQL and jQuery Ajax

  1. Thank you, clean and effective.
    Sorting data and adding new records may be added in the future.

  2. Hi, Im having an issue with this inline-editing code. First of all… the tutorial is easy to understand and well formatted.

    I’m having a database with 3 tables to inline-edit, the code above only works for one table though – even though I copied it to the other files (changing the table name and php-File of course). I also noticed, that even on the working table, I get the “errr” message when an update is successful – the success function is never in use somehow.

    I also checked this on your “Live Demo” and I can also see the “errr” message.

    Do you have an idea what I did wrong or what could be the issue?
    Would be great if you could contact me if you have an idea.

  3. Ive followed the instructions however nothing happens when I change the data in a row I get no errors , nothing. Any ideas?

    1. Have you implemented update query correctly? check everything carefully, may be you have missed something.

  4. Hi thanks for this nice Job, and work great !

    I have a request about an add &remove a row ? and module become really powerfull !
    Can you think it’s possible to implement this functionality?

  5. Very nice tutorial but one thing is missing – please add a search box at the top right of the table. How do one do it?

    1. Thanks! This is easy, you can add this yourself according to requirement. If you face any issue, you can provide us source code to help you.

Comments are closed.