Creating an editable HTML table with x-editable

“Update” is one of the important and frequently used CRUD operations. Developers used to spend a lot of time in building “Update” forms, handler scripts and validations. But in the recent days, inline editing is replacing these update forms by allowing users to edit any data just by clicking on it. Inline editable fields have so many advantages over the conventional update forms. The user does not have to move between pages to update records. It also saves screen real-estate by cutting down additional “Update” buttons, confirmation/error message containers, etc.

Editable elements not only makes it easier for the users but also reduces a lot of developers’ effort. I have used x-editable in many of my projects. Today I am going to teach you how x-editable can be used to apply editable functionality to a dynamically generated HTML table.

You can view the live demo here.

In this tutorial, you will learn

  1. to create an HTML table and load dynamic rows to it via AJAX
  2. to make each cell of table editable using the X-editable javascript library

You will also learn to

  1. deal with dynamically generated elements when applying X-editable
  2. create a reusable function to help us make all the columns editable without rewriting a lot of code.

This tutorial uses PHP as server-side scripting language and MySQL as the database. But it is very easy to implement x-editable with any server-side scripting language and database. Because x-editable has very less to do with the backend. I used bootstrap for styling and responsiveness and jQuery as the base javascript framework.

Creating an HTML table with dynamically generated rows

HTML table with dynamically generated rows

I have created an HTML table to display employee ID, name, age and department name of various employees. As you can see, the table only has the head row now.

   Employee ID #   Name   Age   Department  

The body of the table is appended with dynamic rows from the get_employee_details.php ‘s JSON response when the “Load data” button is clicked.

$("#load_data_rows").on('click', function() {
 $("#employee_details_table tbody").empty();
 $.ajax({
     url: "get_employee_details.php",
     type: "POST",
     dataType: "JSON",
     success: function(response, textStatus, jqXHR) {
         for (var i = 0; i < response.length; i++) {
             $('#employee_details_table').append("" + response[i].id + "" + response[i].name + "" + response[i].age + "" + response[i].department + "");
         }
     },
     error: function(jqXHR, textStatus, errorThrown) {
         alert("Error " + errorThrown);
     }
 });
 });

You can download the “get_employee_details.php” script file linked at the end of this tutorial.

ALSO READ  Changing background color every X seconds in Javascript

Making the cells editable

Now we have a table with rows dynamically loaded via AJAX. We can start applying the .editable() method to each of the columns in the table.

First, I am applying the .editable() method to the rows in the Employee name column.
Editable HTML table

$('#user_details_table').append(""+response[i].id+""+response[i].name+""+response[i].age+""+response[i].department+"");

I have just added an anchor tag inside the second element and added a class along with few data attributes.

  • The class name will be used as the selector when binding the .editable() method to the cells.
  • The name attribute is the name of the column in the MySQL table which is to be edited.
  • The type attribute defines the type of the input element to be generated for editing. It can be “text”,”texarea” or “select”.
  • The pk attribute specifies the primary key value of the row.

Binding .editable() method to dynamically generated rows

We are dealing with dynamically generated elements. That means elements that are not present in the DOM when the HTML document is loaded. Like all jQuery methods, .editable() also cannot be directly bound to a dynamically generated element.

So I am binding the .editable() method to the “#employee_details_table” selector, which refers to the HTML table. Then using the “selector” property, I tell .editable() method to apply editable functionality to the elements in anchor tag that have the class named “employee_name”.

$("#employee_details_table").editable({   
selector: "a.employee_name",
url: "update_employee_details.php",
title: "Employee name",
ajaxOptions: {
  type: 'POST',
  dataType: 'json'
}
  });

In the above code,

  • url property refers to the URL of the server-side handler script.
  • title property is used as the header for popover that appears when editing. Not used when the mode is set to inline.
  • ajaxOptions property takes an object containing the HTTP method and dataType.

Applying .editable() to all the columns

As you can see in the above image, all the rows in the Employee name is column is now editable. We can just copy the code below.

$("#employee_details_table").editable({

selector: "a.employee_name",
url: "update_employee_details.php",
title: "Employee name",
ajaxOptions: {
  type: 'POST',
  dataType: 'json'
}

}); 

and change the class name and title for each column in the table to apply editable to all the columns. But wait, we will make it even easier in the next step.

ALSO READ  5 Best JSON Online Editor Tools

Optimizing the code

Imagine if the table has 15 columns, you will end up having 15 such code snippets to bind all the columns to .editable() method. So I just wrote a re-usable function as seen below.

function bind_editable_to_column(table_selector,column_selector,ajax_url,title,options) {
      options = typeof options !== 'undefined' ? options : ''; 
$(table_selector).editable({   
    selector: column_selector,
    url: ajax_url,
    title: title,
    ajaxOptions: {
          type: 'POST',
      dataType: 'json'
    },
    source: options
  });
}

Now the function can be simply called with the selector string, title and other values for each column, without having to rewrite the same code over and over again. Since the function also takes in a table_selector parameter, you can also use the function to create any number of editable HTML tables in the same page.

Now to make the Employee age column editable,

bind_editable_to_column('#employee_details_table','a.employee_age','update_employee_details.php','Employee Age');

Using drop-down menu with editable

X-editable on dynamic HTML table
We just completed making all the columns editable except the Department name column. The department column will only have a set known values. So it can be a drop down menu. The same javascript function given above can be re-used for applying editable drop down menus just providing values to the options parameter. The options parameter is not required for text type input elements.

Initializing the option property

var department_names = [
{value: 'SALES', text: 'SALES'},
{value: 'SUPPORT', text: 'SUPPORT'},
{value: 'PRODUCTION', text: 'PRODUCTION'},
{value: 'MAINTENANCE', text: 'MAINTENANCE'},
{value: 'LOGISTICS', text: 'LOGISTICS'}
];

Applying .editable() to the Department name column

bind_editable_to_column('#employee_details_table','tr td a.employee_department','update_employee_details.php','Employee Department',department_names);

Backend script

On the server, I use the following PHP script to validate the submitted data and update the MySQL table. Please add code to prevent Cross Site Scripting before you use this code on the production server.


 0 && strlen($column_name) > 0 && strlen($value) > 0) {
 //connecting to database
 try {
 $pdo = new PDO("mysql:host=localhost;dbname=YOUR_DB_NAME","YOUR_USER_NAME","YOUR_PASSWORD");
 }
 catch(PDOException $e) {
 echo $e->getMessage();
 }

 $query = "UPDATE `tbl_employees` SET {$column_name} = :value WHERE `id`=:id";
 $stmt = $pdo->prepare($query);
 $stmt->bindParam(':value',$value);
 $stmt->bindParam(':id',$employee_id);
 if($stmt->execute()) {
 $result['response']['success'] = true;
 $result['response']['message'] = "Updated the $column_name with $value";
 echo json_encode($result);

 }
 else {
 header('HTTP/1.1 400 Bad Request', true, 400); 
 echo "Error: Unable to update"; 

 }

}
else {
 header('HTTP/1.1 400 Bad Request', true, 400);
 echo "One or more required values are missing.";
}

?>

I have decided not to allow empty or null values when the user is updating data. So I am using

strlen($employee_id) > 0 && strlen($column_name) > 0 && strlen($value) >; 0 

to validate the submitted data.

If you want to allow empty strings, you can use

strlen($employee_id) > 0 && strlen($column_name) > 0 && isset($_POST['value']) > 0 

Specifying default properties of the editable method

Everything is set up. But if you want to apply some global default settings to all the editable elements at once, you can use

$.fn.editable.defaults.propertyName = 'value'; .

For instance, if you want the input container to open in a popover instead of opening in-line, you can specify it as follows.

$.fn.editable.defaults.mode = 'popup';

And if you want to specify the position of the popover you can use the position property as seen below. The possible values accepted by this property are “top”,”right”,”bottom” and “left”.

$.fn.editable.defaults.placement = 'bottom';

These defaults can be overridden by definitions specified inside the individual .editable() methods.

You can also check the list of available properties in the official documentation of the x-editable library.

Conclusion

As I mentioned at the beginning of this tutorial, we have made it extremely simple for the user to view, edit any cell in the table and confirm if the data is updated in the database without any errors, all in one single page.
We neither had an “Update” button next to each row nor had taken the user through update and confirmation pages to complete editing data.

I hope you enjoyed building an editable HTML table today. If you think this tutorial can be improved further, let me know through comments.

Download Source Code

4 Comments

  1. Arup Ghosh May 24, 2016
    • Narendran Parivallal May 24, 2016
  2. Kesh September 30, 2016
  3. jabrane October 7, 2016

Leave a Reply