Live table edit with php, jquery and ajax

Live table edit with PHP, jquery and ajax – How to do it?

If you want to edit table data on the fly OR exactly saying, doing Live Table edit –  just by clicking on a table cell, then follow below steps to implement Live table edit functionality. Live table edit with PHP, jquery and ajax simplifies the editing of table data on the same page. We can edit table data just like changing it by simply typing.

Step 1.
Connect to the database table from where you want an editable table and fetch data to table rows using query.

<?php error_reporting(0);
$host_name = "localhost";
$username = "root";
$password = "";
$database_name = "company";
$database = mysql_connect($host_name,$username,$password) or die("Sorry database not connected.");
mysql_select_db($database_name, $database) or die("Sorry database not selected.");

$query = "select id, first_name, email from employees limit 5";
$sql = mysql_query($query);


?>

Step 2.
Loop out the query result and create a table on your page, with certain attributes in table rows and their data as below.

<div>
    <div>Table edit using jquery and ajax on page</div>
 <br>
 <div>
  <table>
   <tr>
    <th>Sr. No.</th>
    <th>Name</th>
    <th>Email</th>
   </tr>
   <?php $i = O;
   while($row = mysql_fetch_array($sql))
   {
   ?>
   <tr class="dataRows" id="<?php echo $row['id'];?>">

    <td><?php echo $i++?></td>
    <td>
     <div id="div_name_<?php echo $row['id']; ?>"><?php echo $row['first_name'];?></div>
     <input type="text" value="<?php echo $row['first_name'];?>" id="input_name_<?php echo $row['id']; ?>" class="inputs" />
    </td>
    <td>
     <div id="div_email_<?php echo $row['id']; ?>"><?php echo $row['email'];?></div>
     <input type="text" value="<?php echo $row['email'];?>" id="input_email_<?php echo $row['id']; ?>" class="inputs" />
    </td>
   </tr>
   <?php }
   ?>
  </table>
 </div>
</div>

Step 3.
We have given certain ids and classes to the divs, tr and other fields and applied a stylesheet to those HTML tags as below.

<style>
.inputs
{
 display:none;
}
table
{
 border: 1px solid;
}
th
{
background-color:#de5145;
color:white;
}
td
{
 border: 1px solid #dfdfdf;
 width:170px;
font-family:arial;
font-size:14px;
}
</style>

So in above steps, we have created basic table structure with attributes and classes and ids. These classes and ids will now use in our jquery code for live table modification.
You will see the result like below image.

Now follow below steps to edit table cells using jquery.
Steps 4.
In this step, we create jquery for the table edit. This jquery contains code for getting data from the row you have clicked to edit and then calling ajax function to the PHP script for updating data in database table. Have a look at below code.

<script>
$(document).ready(function(){
 var editData;
 var id;
 $('.dataRows').click(function(){
  //alert($(this).attr('id'));
  id = $(this).attr('id');
  $('#div_name_'+id).hide();
  $('#input_name_'+id).show();
  $('#input_name_'+id).css({'background':'#c1ea4f','font-weight':'bold'});
  
  $('#div_email_'+id).hide();
  $('#input_email_'+id).show();
  $('#input_email_'+id).css({'background':'#c1ea4f','font-weight':'bold'});
  
  //$('#id').css({'background':'#c1ea4f'});
 });
 
 $('.inputs').change(function(){
  //alert(id);
  var name = $('#input_name_'+id).val();
  var email = $('#input_email_'+id).val();
  var editData = "id="+id+"&name="+name+"&email="+email;
  //alert(name);
  $.ajax({
    type:"POST",
    url:"table_ajax.php",
    data:editData,
    success:function(result){
     //alert(result);
     $('#div_name_'+id).html(name);
     $('#div_email_'+id).html(email);
  
    }
  });
 });
 
 $(document).mouseup(function(){
  $('#input_name_'+id).hide();
  $('#div_name_'+id).show();
  
  $('#input_email_'+id).hide();
  $('#div_email_'+id).show();
 });
});
</script>

As you have seen in above code we have called ajax request to “table_ajax.php” and sent data for update. So lets create “table_ajax.php” and complete this tutorial.

Step 5.
table_ajax.php

<?php 
error_reporting(0);
$host_name = "localhost";
$username = "root";
$password = "";
$database_name = "company";
$database = mysql_connect($host_name,$username,$password) or die("Sorry database not connected.");
mysql_select_db($database_name, $database) or die("Sorry database not selected.");

if(isset($_POST['id']))
{
 $id  = $_POST['id'];
 $name  = $_POST['name'];
 $email  = $_POST['email'];
 
 $sql = "update employees set first_name = '$name', email = '$email' where id='$id'";
 mysql_query($sql);
 
}

?>

Now you can edit table data on the page itself and update records in the database just by editing table cell data. Just click on the table cell and that cell will become editable. Edit the data and click on the page anywhere. This process will update your data in the database.
From:

To:

Hope you have found this tutorial useful. If you found any mistake, improvement or have any query please comment here. Thanks!