HTML5 Inline Editing with PHP, MYSQL & jQuery Ajax

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.

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 >

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,

• Simple Example of Editable Table in HTML5
• X-editable – In-place Editing with Bootstrap and jQuery
• HTML5 Inline Editing With PHP, MYSQL & JQuery Ajax

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" >"><?php echo $res['employee_name'];?></td>
     <td class="editable-col" >"><!--?php echo $res['employee_salary'];?></td>
     <td class="editable-col" >"><!--?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