Saturday, 1 April 2017

Inline Edit data by using X-editable with PHP and Mysql



If you looking for web tutorial on X-editable jquery plugin, then you have come on right place in this post we have make discussion on how to live update or edit of mysql table data by using X-editable jquery plugin as front-end and as back-end we have use PHP script. We will make simple inline table edit of data by using X-editable with PHP. We have received many request from our readers so we have make this type of tutorial.

In this post we will inline edit of data with X-editable with PHP and Mysql. Live Inline updating of data is extremely required functionality of website that will authorize user can edit without going to other pages. In this you can edit single column data at one time. There are many inline editing plugin available but we have use X-editable plugin that make html element editable inline.



First we have include following links in our index page.


<link href="//netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet">
  <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>
  <link href="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/css/bootstrap-editable.css" rel="stylesheet">
  <script src="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/js/bootstrap-editable.js"></script>


Thenafter we have define simple html table, we will load data under this table.


<table class="table table-bordered table-striped">
    <thead>
     <tr>
      <th width="10%">ID</th>
      <th width="40%">Name</th>
      <th width="10%">Gender</th>
      <th width="30%">Designation</th>
      <th width="10%">Age</th>
     </tr>
    </thead>
    <tbody id="employee_data">
    </tbody>
   </table>


We have make this jquery function which fetch data from table and display under above table.


function fetch_employee_data()
 {
  $.ajax({
   url:"fetch.php",
   method:"POST",
   dataType:"json",
   success:function(data)
   {
    for(var count=0; count<data.length; count++)
    {
     var html_data = '<tr><td>'+data[count].id+'</td>';
     html_data += '<td data-name="name" class="name" data-type="text" data-pk="'+data[count].id+'">'+data[count].name+'</td>';
     html_data += '<td data-name="gender" class="gender" data-type="select" data-pk="'+data[count].id+'">'+data[count].gender+'</td>';
     html_data += '<td data-name="designation" class="designation" data-type="text" data-pk="'+data[count].id+'">'+data[count].designation+'</td>';
     html_data += '<td data-name="age" class="age" data-type="text" data-pk="'+data[count].id+'">'+data[count].age+'</td></tr>';
     $('#employee_data').append(html_data);
    }
   }
  })
 }


We have write this php code that received request from Ajax and fetch data from table and send back data to Ajax request.


<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = "SELECT * FROM employee";
$result = mysqli_query($connect, $query);
$output = array();
while($row = mysqli_fetch_assoc($result))
{
 $output[] = $row;
}
echo json_encode($output);
?>


Now we want to make table column editable by using this editable method, here we have make editable table column like name, gender, designation and age table column. We have also put validation like required field for all column and we have also use number only validation for age table column also. We have also make select box in gender table column also. This all feature we have use by using this X-editable plugin. Here we have send ajax request for update of data to update.php page. This way it will update particular table column data by using X-editable plugin.


$('#employee_data').editable({
  container: 'body',
  selector: 'td.name',
  url: "update.php",
  title: 'Employee Name',
  type: "POST",
  //dataType: 'json',
  validate: function(value){
   if($.trim(value) == '')
   {
    return 'This field is required';
   }
  }
 });
 
 $('#employee_data').editable({
  container: 'body',
  selector: 'td.gender',
  url: "update.php",
  title: 'Gender',
  type: "POST",
  dataType: 'json',
  source: [{value: "Male", text: "Male"}, {value: "Female", text: "Female"}],
  validate: function(value){
   if($.trim(value) == '')
   {
    return 'This field is required';
   }
  }
 });
 
 $('#employee_data').editable({
  container: 'body',
  selector: 'td.designation',
  url: "update.php",
  title: 'Designation',
  type: "POST",
  dataType: 'json',
  validate: function(value){
   if($.trim(value) == '')
   {
    return 'This field is required';
   }
  }
 });
 
 $('#employee_data').editable({
  container: 'body',
  selector: 'td.age',
  url: "update.php",
  title: 'Age',
  type: "POST",
  dataType: 'json',
  validate: function(value){
   if($.trim(value) == '')
   {
    return 'This field is required';
   }
   var regex = /^[0-9]+$/;
   if(! expression.test(value))
   {
    return 'Numbers only!';
   }
  }
 });


Lastly, this PHP code will update particular table column data as per it received ajax request with data.


<?php
//update.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = "
 UPDATE employee SET ".$_POST["name"]." = '".$_POST["value"]."' 
 WHERE id = '".$_POST["pk"]."'";
mysqli_query($connect, $query);
?>


Source Code


index.php



<html>
 <head>
  <title>Live Inline Update data using X-editable with PHP and Mysql</title>
  <link href="//netdna.bootstrapcdn.com/bootstrap/3.0.0/css/bootstrap.min.css" rel="stylesheet">
  <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>
  <link href="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/css/bootstrap-editable.css" rel="stylesheet">
  <script src="https://cdnjs.cloudflare.com/ajax/libs/x-editable/1.5.1/bootstrap3-editable/js/bootstrap-editable.js"></script>
  
 </head>
 <body>
  <div class="container">
   <h1 align="center">Live Inline Update data using X-editable with PHP and Mysql</h1>
   <br />
   <table class="table table-bordered table-striped">
    <thead>
     <tr>
      <th width="10%">ID</th>
      <th width="40%">Name</th>
      <th width="10%">Gender</th>
      <th width="30%">Designation</th>
      <th width="10%">Age</th>
     </tr>
    </thead>
    <tbody id="employee_data">
    </tbody>
   </table>
 </body>
</html>



<script type="text/javascript" language="javascript" >
$(document).ready(function(){
 
 function fetch_employee_data()
 {
  $.ajax({
   url:"fetch.php",
   method:"POST",
   dataType:"json",
   success:function(data)
   {
    for(var count=0; count<data.length; count++)
    {
     var html_data = '<tr><td>'+data[count].id+'</td>';
     html_data += '<td data-name="name" class="name" data-type="text" data-pk="'+data[count].id+'">'+data[count].name+'</td>';
     html_data += '<td data-name="gender" class="gender" data-type="select" data-pk="'+data[count].id+'">'+data[count].gender+'</td>';
     html_data += '<td data-name="designation" class="designation" data-type="text" data-pk="'+data[count].id+'">'+data[count].designation+'</td>';
     html_data += '<td data-name="age" class="age" data-type="text" data-pk="'+data[count].id+'">'+data[count].age+'</td></tr>';
     $('#employee_data').append(html_data);
    }
   }
  })
 }
 
 fetch_employee_data();
 
 $('#employee_data').editable({
  container: 'body',
  selector: 'td.name',
  url: "update.php",
  title: 'Employee Name',
  type: "POST",
  //dataType: 'json',
  validate: function(value){
   if($.trim(value) == '')
   {
    return 'This field is required';
   }
  }
 });
 
 $('#employee_data').editable({
  container: 'body',
  selector: 'td.gender',
  url: "update.php",
  title: 'Gender',
  type: "POST",
  dataType: 'json',
  source: [{value: "Male", text: "Male"}, {value: "Female", text: "Female"}],
  validate: function(value){
   if($.trim(value) == '')
   {
    return 'This field is required';
   }
  }
 });
 
 $('#employee_data').editable({
  container: 'body',
  selector: 'td.designation',
  url: "update.php",
  title: 'Designation',
  type: "POST",
  dataType: 'json',
  validate: function(value){
   if($.trim(value) == '')
   {
    return 'This field is required';
   }
  }
 });
 
 $('#employee_data').editable({
  container: 'body',
  selector: 'td.age',
  url: "update.php",
  title: 'Age',
  type: "POST",
  dataType: 'json',
  validate: function(value){
   if($.trim(value) == '')
   {
    return 'This field is required';
   }
   var regex = /^[0-9]+$/;
   if(! expression.test(value))
   {
    return 'Numbers only!';
   }
  }
 });
 
 
 
});
</script>


fetch.php



<?php
//fetch.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = "SELECT * FROM employee";
$result = mysqli_query($connect, $query);
$output = array();
while($row = mysqli_fetch_assoc($result))
{
 $output[] = $row;
}
echo json_encode($output);
?>


update.php



<?php
//update.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$query = "
 UPDATE employee SET ".$_POST["name"]." = '".$_POST["value"]."' 
 WHERE id = '".$_POST["pk"]."'";
mysqli_query($connect, $query);
?>

27 comments:

  1. Hi
    thank you for this tutorial, and i want to ask you about how can i includ this Source Code in my theme wordpress

    ReplyDelete
  2. Hello, thank you for tutorial
    How I can input size?

    ReplyDelete
  3. Unicamente falto la query de la BAse de DAtos

    ReplyDelete
  4. Very good tutorial, thanks web lesson

    ReplyDelete
  5. Sir, This is awesome. Can you also create a tutorial using Jqx Grid from jqwidgets its has some features compare to x-editable. X-editable is great but I found it hard to import data from excel.

    ReplyDelete
  6. Hi

    How can I make it to sortable, searchable and pagination?

    Thank you

    ReplyDelete
  7. https://editor.datatables.net/examples/extensions/keyTable.html
    how to use this

    ReplyDelete
  8. Sir Age field is not working indemo and my project?

    ReplyDelete
  9. I realy love this tutorial. It helped me a lot.

    However, I noticed a small error.

    Change this: var regex = /^[0-9]+$/;

    To: var expression = /^[0-9]+$/;

    ReplyDelete
  10. Thank You for this solution, it's just what I need but I have trouble fetching data from my own database and table. It works perfectly when I'm using same database and table as You mentioned. In my table I have couple more columns (id, status, lname, fname, email, groups) that I need to be able to edit but I can't get it to display any data. Any suggestions?
    Thank You in advance!

    P.S. You have a little mistake in Your code for editing Age, it's not the same variable.

    ReplyDelete
  11. regex number only not working at "age".

    ReplyDelete
  12. There is no filter option and searching option

    ReplyDelete
  13. thank you so much...
    i have one issues occur, it is not working with in bootstrap table pagination

    ReplyDelete
  14. It is not showing the data. Please help me!

    ReplyDelete
  15. How to show image from sql in this table

    ReplyDelete
    Replies
    1. if you have a path to the image is simple, just use like:
      html_data += td img src=data[count].imagepath td

      Delete
  16. How to show image from sql in this table

    ReplyDelete
  17. بسیار خوب بود ممنون

    ReplyDelete
  18. how to add gender with select box

    ReplyDelete
  19. Hi,

    Make a tutorial how to do it like this in PDO

    ReplyDelete
  20. update is not working beacuse of name and value is not known

    ReplyDelete