X-editable Inline Editing Example Using PHP and MySQL

I will demonstrate in this PHP tutorial to create inline editing using PHP and MySQL. You can also create inline editing using HTML5 attribute.

Inline editing is a very important feature of web applications that allows the user to update on the fly without moving any extra pages.

There is a lot of inline editing plugin and grid plugin available that provide an option to edit field value in-line. I am using jQuery x-editable plugin to make HTML elements editable inline.

What’s x-editable

The X-editable bootstrap plugin is fantastic for inline editing. It’s far too simple and straightforward to implement with server-side update functionality. You can use this library to add editable elements to your page. It works with any engine (bootstrap, strong>jquery-ui/strong>, jquery only) and has popup and inline modes.

Features:

• popup and inline modes
• supported inputs:
  • text
  • textarea
  • select
  • date
  • datetime
  • dateui
  • combodate
  • html5types
  • checklist
  • wysihtml5
  • typeahead
  • typeaheadjs
  • select2
• client-side and server-side validation
• customizible container placement
• toggle by click, dblclick or manually
• keyboard support (escape/enter/tab)
• works in IE7+ and all modern browsers
• unit tested
• live events

Why do we require inline editing?

• No special FORM markup that helps to easy development.
• Minimize screens in your application.
• You can easily add and remove fields.
• Simple backend script for data update.

We will use the following files for in-line edit using x-editable

index.php: This file will use to create an HTML layout and handle ajax response.
connection.php: This file will use to create a database connection.
response.php: This file will use to handle all server-side functionality. I will create get employee, update record action method and return back JSON response.

How to Create Dynamic HTML table Using JSON

Step 1: Created connection file connection.php to handle database connection and return db connection object.

<?php
Class dbObj{
 /* Database connection start */ var $servername = "localhost";
 var $username = "root";
 var $password = "";
 var $dbname = "test";
 var $conn;
 function getConnstring() {
  $con = mysqli_connect($this->servername, $this->username, $this->password, $this->dbname) or die("Connection failed: " . mysqli_connect_error());

  /* check connection */  if (mysqli_connect_errno()) {
   printf("Connect failed: %s\n", mysqli_connect_error());
   exit();
  } else {
   $this->conn = $con;
  }
  return $this->conn;
 }
}

?>

Where is:

1. $servername: Your MySQL db hostname
2. $username: MySQL db username
3. $password: MySQL db password
4. $dbname: Database name

Step 2: Included all js and css files into index.php file.

<script src="http://code.jquery.com/jquery-2.0.3.min.js"></script> 
<script src="//netdna.bootstrapcdn.com/bootstrap/3.0.0/js/bootstrap.min.js"></script>       
<script src="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/js/bootstrap-editable.js"></script>

Step 3: Create HTML table for listing data in index.php file.

<table class="table table-condensed table-hover table-striped" width="60%" cellspacing="0">
    <thead>
        <tr>
            <th>Empid</th>

            <th>Name</th>

            <th>Salary</th>

            <th>Age</th>
        </tr>
    </thead>

    <tbody id="employee_grid"></tbody>
</table>

Step 4: Create AJAX method to get results from php file and append with table body using jQuery append() method.

$( document ).ready(function() {
 function getEmployee() {
  $.ajax({
    type: "GET",  
    url: "response.php",
    dataType: "json",       
    success: function(response)  
    {
   for (var i = 0; i < response.length; i++) {
     $('#employee_grid').append("" + response[i].id + "" + response[i].employee_name + "" + response[i].employee_salary + "" + response[i].employee_age + "");
    }
    },
   error: function(jqXHR, textStatus, errorThrown) {
    alert("loading error data " + errorThrown);
   }
  });
 }
});

As you can see, I have created a GET type AJAX request which will use to get all records from the employee table. I am appending one by one JSON response objects with an HTML table.

Step 5: Created response.php file and added employee listing method.

<?php
 //include connection file 
 include_once("connection.php");
 
 $db = new dbObj();
 $connString =  $db->getConnstring();

 $params = $_REQUEST;
 
 $action = isset($params['action']) != '' ? $params['action'] : '';
 $empCls = new Employee($connString);

 switch($action) {
  default:
  $empCls->getEmployees($params);
  return;
 }
 
 class Employee {
 protected $conn;
 protected $data = array();
 function __construct($connString) {
  $this->conn = $connString;
 }
 
 public function getEmployees($params) {
  
  $this->data = $this->getRecords($params);
  
  echo json_encode($this->data);
 }
 
 function getRecords($params) {
    
    // getting total number records without any search
  $sql = "SELECT * FROM `employee` LIMIT 0, 10";
  
  $queryRecords = mysqli_query($this->conn, $sql) or die("error to fetch employees data");
  
  while( $row = mysqli_fetch_assoc($queryRecords) ) { 
   $data[] = $row;
  }
  
  return $data;   // total data array
 }
}
?>

Added switch php method and default action would be to get employee record from MySQL table return json objects.

How to Make HTML table Column Editable

We have generated an HTML table dynamically using ajax and loaded data into it, now we will make an editable HTML table cell using x-editable method. We need to apply an editable option on each column of the table which we want to inline edit.

I have created a jQuery method to enable inline editing on a table cell. We need to pass the following parameters for each table column.

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

Where as:

• selector: The column selector identifier of an element, which could be element id, class, name etc.
• url: The server-side script that will handle requests.
• title: Use to define the Header title for popover that will show on the time editing cell.

The jQuery method is:

function make_editable_col(table_selector,column_selector,ajax_url,title) {
 $(table_selector).editable({   
  selector: column_selector,
  url: ajax_url,
  title: title,
  type: "POST",
  dataType: 'json'
 });
}

Now we will call the above method for each cell like below,

make_editable_col('#employee_grid','td.employee_name','response.php?action=edit','Employee Name');
make_editable_col('#employee_grid','td.employee_age','response.php?action=edit','Employee Age');
make_editable_col('#employee_grid','td.employee_salary','response.php?action=edit','Employee Salary');

Now we will modify the table td attribute for in-line edit, We need to add some custom attributes that are used for inline edit and send request parameters to the server on update.

$('#employee_grid').append("" + response[i].id + "" + response[i].employee_name + "" + response[i].employee_salary + "" + response[i].employee_age + "");

The main attributes are:

• data-type – type of input HTML element (text, textarea, select, etc)
• data-name – The Mysql table column name.
• data-pk – The primary key of the record to be updated (ID in db)

You can also define all attributes on the HTML element at once using the below syntax, but make sure content would be static or loaded on DOM.

<a href="#" id="username" data-type="text" data-pk="1" data-url="/post" data-title="Enter username">superuser</a>

Update record using x-editable and PHP

We have to make an editable HTML cell using x-editable, so now we will create an update method in response.php file that will take request parameters and update employee record data using SQL Query.

Step 1: We have added edit action in response.php file under the switch method.

switch($action) {
 case 'edit':
 $empCls->updateEmployee($params);
 break;
 default:
 $empCls->getEmployees($params);
 return;
}

Step 2: Added method to update record into employee MySQL table.

function updateEmployee($params) {
  $data = array();
  $sql = "Update `employee` set ".$params["name"]." = '" . $params["value"] . "' WHERE id='".$params["pk"]."'";
  
  if($result = mysqli_query($this->conn, $sql)) {
   echo 'Successfully! Record updated...';
  } else {
   die("error to update '".$params["name"]."' with '".$params["value"]."'");
  }
 }

When we have clicked, ok option on in-line edit popup, We will send some parameters to the server-side which are the col name, value, and id of the record that will use to update the record.

Conclusion:

We have demonstrated the update records into MySQL using x-editable jQuery plugin. We are using Bootstrap, jQuery, PHP and MySQL in this tutorial. You can also use any programming server-side language for inline editing.

You can download the source code and Demo from the below link.