Live Table Insert Update Delete Using PHP and jQuery

By | September 14, 2014

In this tutorial we will see how to read insert update delete live table data from MySQL database using php , for this i am using Jquery .live() function and also ajax to call php scripts to perform  CRUD Operation from java script , This script helps you perform CRUD operation on MySQL database table.

Download            Live Demo

Database

Create a database members with table member having columns  id, fname , lname , email

CREATE TABLE IF NOT EXISTS member (
  id   int(100) PRIMARY KEY  AUTO_INCREMENT,
  fname  varchar(255) ,
  lname  varchar(255) ,
  email   varchar(255) ,

PHP Script

file : config.php

<?php
$mysql_hostname = “localhost”;
$mysql_user = “root”;
$mysql_password = “”;
$mysql_database = “members”;

$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) or die(“Opps some thing went wrong”);
mysql_select_db($mysql_database, $bd) or die(“Opps some thing went wrong”);
?>

Read and display table data from MySQL database .

file : index.php

<table border=’1′ cellpadding=’5′ cellspacing=’1′ id=’mytable’>
    <tr align=’center’>
        <th>FirstName</th>
        <th>LastName</th>
        <th>Email</th>
        <th>Action</th>
    </tr>
<?php
include(‘config.php’);
$sql=”select * from member”;
$result=mysql_query($sql);
    while ($row = mysql_fetch_array($result)){
    $id=$row[‘id’];
    $fname=$row[‘fname’];
    $lname=$row[‘lname’];
    $email=$row[’email’];

    echo “<tr  class=’edit_tr’ id=’$id’>
          <td class=’edit_td’>
            <span class=’text’ id=’one_$id’ >$fname</span>
            <input type=’text’ class=’editbox_search’ id=’one_input_$id’ value=’$fname’ >
          </td>

          <td class=’edit_td’>
            <span class=’text’ id=’two_$id’ >$lname</span>
            <input type=’text’ class=’editbox_search’ id=’two_input_$id’ value=’$lname’ >
          </td>
        
          <td class=’edit_td’>
            <span class=’text’ id=’three_$id’ >$email</span>
            <input type=’text’ class=’editbox_search’ id=’three_input_$id’ value=’$email’ >
          </td>     
         
            <td class=’edit_td’ >
              <a href=’#’  class=’edit_btn’>Edit</a>
              <a href=’#’  class=’update’ >Update</a>
              <a href=’#’  class=’cancel’ >Cancel</a>
              <a href=’#’ class=’delete’ > Delete </a>
           </td>
        </tr>”;   
     }                
?>
    <tr>
      <td><input type=’textid=’fnameplaceholder=’firstname‘/></td>
      <td><input type=’textid=’lnameplaceholder=’lastname/></td>
      <td><input type=’textid=’emailplaceholder=’email/></td>
      <td  ><a href=’# id=’add’ >ADD</a></td>
    </tr>
  </table>

file : insert.php

<?php
include(‘config.php’);
if($_POST[‘fname’] && $_POST[‘lname’] && $_POST[’email’]){
    $fname=$_POST[‘fname’];
    $lname=$_POST[‘lname’];
    $email=$_POST[’email’];
    $sql=”INSERT INTO member(id,fname,lname,email)VALUES(”,’$fname’,’$lname’,’$email’)”;
    $result = mysql_query($sql);  
    $id=mysql_insert_id();
    echo $id;
}
?>

file : update.php

<?php
include(‘config.php’);
if($_POST[‘fname’] && $_POST[‘lname’] && $_POST[’email’]){
    $id=$_POST[‘id’];
    $fname=$_POST[‘fname’];
    $lname=$_POST[‘lname’];
    $email=$_POST[’email’];
    $sql=”update member set fname=’$fname’,lname=’$lname’,email=’$email’ where id=’$id’ “;
    mysql_query($sql);
}
?>

file : delete.php

<?php
include(‘config.php’);
$id=$_POST[‘id’];
$sql=”delete from member where id=’$id'”;
mysql_query($sql);
?>

JavaScript

file : modify.php

$(document).ready(function()
{
/***********–DELETE–***************/
$(“.delete”).live(‘click’,function()
{
  var id=$(this).parent().parent().attr(‘id’);
  var b=$(this).parent().parent();
  var dataString = ‘id=’+ id;
    if(confirm(“Sure you want to delete this item? “))
    {
     $.ajax({
            type: “POST”,
            url: “delete.php”,
            data: dataString,
            cache: false,
            success: function(e)
            {
            b.hide();
            }
           });
    return false;
    }
});
                  
/***********–EDIT–***************/           
$(“.edit_btn”).live(‘click’,function()
{
    var ID=$(this).parent().parent().attr(‘id’);
    $(this).hide();
    $(“#”+ID).find(“a.update”).show();
    $(“#”+ID).find(“a.cancel”).show();
    $(“#”+ID).find(“a.delete”).hide();
    $(“#”+ID).find(“span”).hide()
    $(“#”+ID).find(“input.editbox_search”).show();
    $(“#”+ID).find(“input.editbox_search”).css(“border”,”1px solid red”);
});       

/***********–Cancel–***************/
  $(‘.cancel’).live(‘click’,function(){
   var ID=$(this).parent().parent().attr(‘id’);
  
   var one_val=$(“#one_”+ID).html();
   var two_val=$(“#two_”+ID).html();
   var three_val=$(“#three_”+ID).html();
  
   $(“#one_input_”+ID).val(one_val);
   $(“#two_input_”+ID).val(two_val);
   $(“#three_input_”+ID).val(three_val);
  
   $(“#”+ID).find(“span”).show()
   $(“#”+ID).find(“input.editbox_search”).hide();
  
   $(“#”+ID).find(“a.update”).hide();
   $(“#”+ID).find(“a.cancel”).hide();
   $(“#”+ID).find(“a.delete”).show();
   $(“#”+ID).find(“a.edit_btn”).show();   
});

/***********–UPDATE–***************/
$(“.update”).live(‘click’,function(){
    var ID=$(this).parent().parent().attr(‘id’);

    var one_val=$(“#one_input_”+ID).val();
    var two_val=$(“#two_input_”+ID).val();
    var three_val=$(“#three_input_”+ID).val();

    var dataString = ‘id=’+ ID+’&fname=’+one_val+’&lname=’+two_val+’&email=’+three_val;
    if(one_val.length>0 && two_val.length>0 && three_val.length>0){
        $.ajax({
        type: “POST”,
        url: “update.php”,
        data: dataString,
        cache: false,
        success: function(e)
        {
            $(“#one_”+ID).html(one_val);
            $(“#two_”+ID).html(two_val);
            $(“#three_”+ID).html(three_val);
            $(“#”+ID).find(“span”).show()
            $(“#”+ID).find(“input.editbox_search”).hide();

            $(“#”+ID).find(“a.update”).hide();
            $(“#”+ID).find(“a.cancel”).hide();

            $(“#”+ID).find(“a.delete”).show();
            $(“#”+ID).find(“a.edit_btn”).show();   
        }
        });
    }    
    else{
    alert(“field missing”);
    }
  });
 
/***********–ADD–***************/
$(“#add”).live(‘click’,function(){

<—Find the rest of the code in download —>   
 });

});