HTML5 Inline Editing with PHP, MYSQL & jQuery Ajax

Last Updated: | By: PHPFlow Team

This tutorial will assist you in understanding HTML5 inline editing with PHP and MYSQL, As demonstrated in the previous tutorial HTML inline editing.

We learned how to make any HTML control editable inline by using the contenteditable attribute. Only browsers that support HTML5 will be able to use Inline Editable.

There are many jQuery plugins that provide inline editable functionality, but you can do it much more easily with HTML5 and without the overhead of the jQuery library.

You only need to write some ajax and jquery code to update a record in a MySQL database. This will be helpful for altering a particular column only, not the complete form’s data.

html5 inline editing with php demo

Inline Editing with HTML5, PHP and Mysql

We’ll make a table with a list of all the records. We will add the contenteditable attribute to all 'td's in the table where I need inline editing functionality.

I’ll make a table with a list of all the records. We will add the contenteditable attribute to all 'td's “(td contenteditable=’true’>)” in the table where I need inline editing functionality.

Whenever the user changes the TD data, I will capture the event 'focusout' with jQuery and get the updated data of td, then call a php function to update the data into mysql using the changed td data.

This example will include the files listed below:

  1. connection.php: This file is used to create a connection with MySQL.
  2. index.php: This file is used to display records in a table and has an inline editing option.
  3. server.php: This file will respond to an ajax request and update data in mysql.

Also checkout other related tutorials,

Let’s make an ‘inline-edit’ POC project in the xampp/htdocs folder. We’ll make an index.html file here.

Step 1: Include the bootstrap CSS file and the jQuery library in the page’s head section.

<script type="text/javascript" src="jquery-1.11.1.min.js"></script>

Step 2: Created a connection file connection.php for database connection with PHP.

<?php
/* Database connection start */
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "test";

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

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}
?>

Step 3: Get records from MySQL and save them in a variable, then paste the code below into the index.php file.

<?php
//include connection file 
include_once("connection.php");
$sql = "SELECT * FROM `employee`";
$queryRecords = mysqli_query($conn, $sql) or die("error to fetch employees data");
?>

In this case, I’m fetching data from MySQL in order to display it in an HTML table. You must replace table name 'employee' with the name of your table.

Step 3: Make an HTML table and iterate through the records in each row.

<table id="employee_grid" class="table table-condensed table-hover table-striped bootgrid-table" width="60%" cellspacing="0">
	<thead>
	    <tr>
          <th>Name</th>
          <th>Salary</th>
          <th>Age</th>
        </tr>
	</thead>
	<tbody id="_editable_table">
		  <?php foreach($queryRecords as $res) :?>
			<tr data-row-id="<?php echo $res['id'];?>">
			  <td class="editable-col" contenteditable="true" col-index="0" oldval="<?php echo $res['employee_name'];?>"><?php echo $res['employee_name'];?></td>
			  <td class="editable-col" contenteditable="true" col-index="1" oldval="<?php echo $res['employee_salary'];?>"><!--?php echo $res['employee_salary'];?></td>
			  <td class="editable-col" contenteditable="true" col-index="2" oldval="<?php echo $res['employee_age'];?>"><!--?php echo $res['employee_age'];?></td>
			</tr>
		<?php endforeach;?>
	</tbody>
</table>

To display table data, I use step-2 $queryRecords variable and iterate on tr using PHP foreach() function. As you can see, I have hard coded the col-index='*' attribute on each table td for column name identification. I’ve also added a oldVal='*' attribute to store the table’s old valve; if the new and old values are the same, we won’t send a server request.

I’m also adding the data-row-id="" attribute to each table tr to identify the row-id of data.
This 'id' will be the employee table’s primary key. This indicates which column row we are updating.

Step 4: Created AJAX Using a server-side php script, a jquery request is used to update data in MySQL.

<script type="text/javascript">
$(document).ready(function(){
  $('td.editable-col').on('focusout', function() {
    data = {};
    data['val'] = $(this).text();
    data['id'] = $(this).parent('tr').attr('data-row-id');
    data['index'] = $(this).attr('col-index');
      if($(this).attr('oldVal') === data['val'])
    return false;
    
    $.ajax({   
          
          type: "POST",  
          url: "server.php",  
          cache:false,  
          data: data,
          dataType: "json",       
          success: function(response)  
          {   
            //$("#loading").hide();
            if(response.status) {
              $("#msg").removeClass('alert-danger');
              $("#msg").addClass('alert-success').html(response.msg);
            } else {
              $("#msg").removeClass('alert-success');
              $("#msg").addClass('alert-danger').html(response.msg);
            }
          }   
        });
  });
});

</script>

Here As you can see, I am attaching an event 'focusout' to each td of the table and sending a request if the value of the td changes. First, I am getting the changed value of the td and then I am getting the row-id of the data tuple with the column-id. Aggregate all parameters and send them to the server to update the column value.

Step 5: Now, using server.php, update the data in MySQL.

<?php
  //include connection file 
  include_once("connection.php");
  
  //define index of column
  $columns = array(
    0 =>'employee_name', 
    1 => 'employee_salary',
    2 => 'employee_age'
  );
  $error = true;
  $colVal = '';
  $colIndex = $rowId = 0;
  
  $msg = array('status' => !$error, 'msg' => 'Failed! updation in mysql');

  if(isset($_POST)){
    if(isset($_POST['val']) &amp;&amp; !empty($_POST['val']) &amp;&amp; $error) {
      $colVal = $_POST['val'];
      $error = false;
      
    } else {
      $error = true;
    }
    if(isset($_POST['index']) &amp;&amp; $_POST['index'] >= 0 &amp;&amp;  $error) {
      $colIndex = $_POST['index'];
      $error = false;
    } else {
      $error = true;
    }
    if(isset($_POST['id']) &amp;&amp; $_POST['id'] > 0 &amp;&amp; $error) {
      $rowId = $_POST['id'];
      $error = false;
    } else {
      $error = true;
    }
  
    if(!$error) {
        $sql = "UPDATE employee SET ".$columns[$colIndex]." = '".$colVal."' WHERE id='".$rowId."'";
        $status = mysqli_query($conn, $sql) or die("database error:". mysqli_error($conn));
        $msg = array('status' => !$error, 'msg' => 'Success! updation in mysql');
    }
  }
  
  // send data as json format
  echo json_encode($msg);
?>

This is a simple PHP code; I haven’t included any SQL injection prevention code, nor have I combined all valid conditions into a complex PHP code.

Conclusion

I’ve shown you how to use PHP, MySQL, and jQuery AJAX to implement HTML5 inline editing.
Using the HTML5 contenteditable attribute, you can easily integrate inline editing into your application.

Download Code and Demo Link

  Live Demo
 Download Source Code

 

39 thoughts on “HTML5 Inline Editing with PHP, MYSQL & jQuery Ajax”

  1. Can you help me? Only the first column is editable, when I try any other column the change reflect in the first one.

    Reply

    • The reason was ‘$sql = “UPDATE employee SET “.$columns[$colIndex].” = ‘”.$colVal.”‘ WHERE id='”.$rowId.”‘”;’, i am not sending updated colindex which want to update.

      Reply

  3. hy, thanks the script before but in there i have a question :
    how to make other coumn can be editable. and when iam try to edit the other column the reflect is in first column.

    Reply

      • No it’s not working, the error handling logic is broken. While $colVal gets the value from $_POST[‘val’] correctly, it changes $error to false. And for that reason $colIndex never get’s the value of $_POST[‘index’] (so it puts the new updated values to the first column whose id is 0), and the $error is changed back to true, which makes the updated row correct again.

        Reply

  4. Hi, I’m running the script on local Ubuntu/Xammp. Per row only the first field updates fine, second and third overwrite the first and don’t change. To create mySQL table I used following:

    SET SQL_MODE=”NO_AUTO_VALUE_ON_ZERO”;
    CREATE TABLE IF NOT EXISTS `employee` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `employee_name` varchar(50) NOT NULL,
    `employee_salary` varchar(50) NOT NULL,
    `employee_age` varchar(50) NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

    INSERT INTO `employee` (`id`, `employee_name`, `employee_salary`, `employee_age`) VALUES
    (1, ‘John’, ‘1000’, ’22’),
    (2, ‘Judy’, ‘1000’, ’24’),
    (3, ‘Claire’, ‘1000’, ’26’),
    (4, ‘Mike’, ‘1200’, ’28’);

    To see all values in index.php I changed line 4 from:

    $sql = “SELECT * FROM `employee` limit 1,10 “;

    to:

    $sql = “SELECT * FROM `employee` limit 0,10 “;

    I have been fiddeling quite a while on this and will keep on, because it’s very nice rocket technology. Some help can be helpful. Thx 😉

    Reply

  6. Hi thank you for this post. it’s really helped.
    And I wondering if I can update the (inline-input-data) with a button to each row, if possible?
    Could you please help me where can I define to follow the button click event?

    Reply

  8. Hi, Thank you so much for the post! It’s very helpful.

    I am facing a problem. The data is not loading into the table. I have created an “emplyoee” table in my database with the exact fields and entered the connection details. It would be grate of you could help me out.

    Thanks in advance!

    Reply

  9. Thank you very much for the post. I learnt from this tutorial but found a problem. The codes work well on my Windows system, but the table does not render with any contents when the codes are implemented in Linux server (Operating System: CentOS 7 64Bit). Does it need any module to support the functionality? Thanks!
    https://uploads.disquscdn.com/images/8c2522f29665bed72e9ba19246a97903ec3a9b26c0dadc1aee3cd8b36146b632.png

    Reply

  10. make sure dbname, username and password updated in connnection.php file, the table name should be employee and connection file included into server.php file.

    Reply

  11. ur browser version supported HTML5? I sent u my working source code in ur mailid as well and I hvnt tested on linux.

    Reply

    • Thanks for sending me the files. Yes. My browser supports HTML5. And I have also tested your files by cloning the ’employee’ table to my database. There turns nothing for the table on the webpage if I post the files to the Linux server. However, it works perfect on my local computer. Do you think if there is any environment issues?
      Thanks!

      Reply

  12. I have all the code working just fine … however If I enter some text in a Textarea, including some line breaks …. it does not maintain the line breaks and puts all the text on the same line …

    So the line breaks are not making there way to the database and when the page is updated it is one long line of text all mashed together.

    How can I maintain the line breaks when written to the database ?

    Reply

    • have u check ur text has new line char before the insert query,If yes u can convert special chart in unicode and store db and revert back

      Reply

  14. Thanks for this post – very clear and helpful in explaining this. I have set it up though I keep getting the error – Failed! updation in mysql when i try to test making a change. Can you give an idea what the problem might be?

    Reply

  16. Very nice work. A little bug in the code error handling above, but in the download link it is fixed, a simple but hard-to-see fix 🙂 Thank you.

    Reply

  17. I do have one question. It seems that if we are using this on a big table with many rows, it does not work until the page is fully loaded, so if a user loads the table and starts trying to edit right away, those edits are not saved. Do you know of a good way around this? Perhaps preventing the contenteditable from working until the page is fully loaded?

    Reply

    • you can use x-editable for inline edit, https://phpflow.com/php/x-editable-inline-editing-example-using-php-mysql/

      Reply

  20. Hello! Great tip! Thank you.
    I’m wondering to build a simple invoice for myself :-).
    So after the head of the invoice (myself recipient + customer recipient details) I’m going to add the rows for the items, typically
    Quantity, description, price, VAT, rowtotal

    This will be a grid of data and for a new invoice it will be empty and with just one row (which fields are empty)

    So basically I need the cells of this grid to initially be input fields.

    With javascript I know how to add/append the additional subsequent lines.

    I’m a bit stuck in how to open/begin this 🙂 since we are not editing existing rows (this will happen, eventually, later), instead I’m creating these records from scratch for this new invoice.

    The final result will be that I have the grid made of the items rows and these must be saved into the DB related to this invoice.

    Can you kindly hint on how to setup this job and so if the code you have kindly provided could be the right base where to start from?
    Thank you

    Reply

  22. thank you for the code.
    I get the error in the console:
    Error: A listener indicated an asynchronous response by returning true, but the message channel closed before a response was received

    what can it be?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *