Sunday, 14 February 2016

Live Table Add Edit Delete using Ajax Jquery in PHP Mysql



Hello Friends, In this tutorial we are going to learn how to live table Insert, Update, Delete and Fetch data from mysql database using Ajax Jquery in PHP programming language. For this feature I am using html5 contenteditable attribute. With the help of this contenteditable attribute table column will be editable and user can change live table data on single click. I am using Ajax call function for Insert data, update data, delete data and Select data from database. This all things are done on user end without page refresh. Ajax get request from user on front-end and send request to database using php language and done operation at back end and send back request to user without page refresh. This feature is very helpful. I hope this tutorial will help to all.


index.php


 <html>  
      <head>  
           <title>Live Table Data Edit</title>  
           <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />  
           <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/js/bootstrap.min.js"></script>  
           <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.0/jquery.min.js"></script>  
      </head>  
      <body>  
           <div class="container">  
                <br />  
                <br />  
                <br />  
                <div class="table-responsive">  
                     <h3 align="center">Live Table Add Edit Delete using Ajax Jquery in PHP Mysql</h3><br />  
                     <div id="live_data"></div>                 
                </div>  
           </div>  
      </body>  
 </html>  
 <script>  
 $(document).ready(function(){  
      function fetch_data()  
      {  
           $.ajax({  
                url:"select.php",  
                method:"POST",  
                success:function(data){  
                     $('#live_data').html(data);  
                }  
           });  
      }  
      fetch_data();  
      $(document).on('click', '#btn_add', function(){  
           var first_name = $('#first_name').text();  
           var last_name = $('#last_name').text();  
           if(first_name == '')  
           {  
                alert("Enter First Name");  
                return false;  
           }  
           if(last_name == '')  
           {  
                alert("Enter Last Name");  
                return false;  
           }  
           $.ajax({  
                url:"insert.php",  
                method:"POST",  
                data:{first_name:first_name, last_name:last_name},  
                dataType:"text",  
                success:function(data)  
                {  
                     alert(data);  
                     fetch_data();  
                }  
           })  
      });  
      function edit_data(id, text, column_name)  
      {  
           $.ajax({  
                url:"edit.php",  
                method:"POST",  
                data:{id:id, text:text, column_name:column_name},  
                dataType:"text",  
                success:function(data){  
                     alert(data);  
                }  
           });  
      }  
      $(document).on('blur', '.first_name', function(){  
           var id = $(this).data("id1");  
           var first_name = $(this).text();  
           edit_data(id, first_name, "first_name");  
      });  
      $(document).on('blur', '.last_name', function(){  
           var id = $(this).data("id2");  
           var last_name = $(this).text();  
           edit_data(id,last_name, "last_name");  
      });  
      $(document).on('click', '.btn_delete', function(){  
           var id=$(this).data("id3");  
           if(confirm("Are you sure you want to delete this?"))  
           {  
                $.ajax({  
                     url:"delete.php",  
                     method:"POST",  
                     data:{id:id},  
                     dataType:"text",  
                     success:function(data){  
                          alert(data);  
                          fetch_data();  
                     }  
                });  
           }  
      });  
 });  
 </script>  

select.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "test_db");  
 $output = '';  
 $sql = "SELECT * FROM tbl_sample ORDER BY id DESC";  
 $result = mysqli_query($connect, $sql);  
 $output .= '  
      <div class="table-responsive">  
           <table class="table table-bordered">  
                <tr>  
                     <th width="10%">Id</th>  
                     <th width="40%">First Name</th>  
                     <th width="40%">Last Name</th>  
                     <th width="10%">Delete</th>  
                </tr>';  
 if(mysqli_num_rows($result) > 0)  
 {  
      while($row = mysqli_fetch_array($result))  
      {  
           $output .= '  
                <tr>  
                     <td>'.$row["id"].'</td>  
                     <td class="first_name" data-id1="'.$row["id"].'" contenteditable>'.$row["first_name"].'</td>  
                     <td class="last_name" data-id2="'.$row["id"].'" contenteditable>'.$row["last_name"].'</td>  
                     <td><button type="button" name="delete_btn" data-id3="'.$row["id"].'" class="btn btn-xs btn-danger btn_delete">x</button></td>  
                </tr>  
           ';  
      }  
      $output .= '  
           <tr>  
                <td></td>  
                <td id="first_name" contenteditable></td>  
                <td id="last_name" contenteditable></td>  
                <td><button type="button" name="btn_add" id="btn_add" class="btn btn-xs btn-success">+</button></td>  
           </tr>  
      ';  
 }  
 else  
 {  
      $output .= '<tr>  
                          <td colspan="4">Data not Found</td>  
                     </tr>';  
 }  
 $output .= '</table>  
      </div>';  
 echo $output;  
 ?>  

insert.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "test_db");  
 $sql = "INSERT INTO tbl_sample(first_name, last_name) VALUES('".$_POST["first_name"]."', '".$_POST["last_name"]."')";  
 if(mysqli_query($connect, $sql))  
 {  
      echo 'Data Inserted';  
 }  
 ?>  

edit.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "test_db");  
 $id = $_POST["id"];  
 $text = $_POST["text"];  
 $column_name = $_POST["column_name"];  
 $sql = "UPDATE tbl_sample SET ".$column_name."='".$text."' WHERE id='".$id."'";  
 if(mysqli_query($connect, $sql))  
 {  
      echo 'Data Updated';  
 }  
 ?>  

delete.php


 <?php  
 $connect = mysqli_connect("localhost", "root", "", "test_db");  
 $sql = "DELETE FROM tbl_sample WHERE id = '".$_POST["id"]."'";  
 if(mysqli_query($connect, $sql))  
 {  
      echo 'Data Deleted';  
 }  
 ?>  

29 comments:

  1. Esta es la BD pero no funciona, puede decir porque?


    -- phpMyAdmin SQL Dump
    -- version 4.5.1
    -- http://www.phpmyadmin.net
    --
    -- Servidor: 127.0.0.1
    -- Tiempo de generación: 20-03-2016 a las 22:46:05
    -- Versión del servidor: 10.1.9-MariaDB
    -- Versión de PHP: 5.6.15

    SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
    SET time_zone = "+00:00";


    /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
    /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
    /*!40101 SET NAMES utf8mb4 */;

    --
    -- Base de datos: `test_db`
    --

    -- --------------------------------------------------------

    --
    -- Estructura de tabla para la tabla `tbl_sample`
    --

    CREATE TABLE `tbl_sample` (
    `id` int(11) NOT NULL,
    `first_name` varchar(255) NOT NULL,
    `last_name` varchar(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    --
    -- Volcado de datos para la tabla `tbl_sample`
    --

    INSERT INTO `tbl_sample` (`id`, `first_name`, `last_name`) VALUES
    (3, 'aa', 'aa'),
    (4, 'bb', 'bb');

    --
    -- Índices para tablas volcadas
    --

    --
    -- Indices de la tabla `tbl_sample`
    --
    ALTER TABLE `tbl_sample`
    ADD PRIMARY KEY (`id`);

    --
    -- AUTO_INCREMENT de las tablas volcadas
    --

    --
    -- AUTO_INCREMENT de la tabla `tbl_sample`
    --
    ALTER TABLE `tbl_sample`
    MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
    /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
    /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

    ReplyDelete
    Replies
    1. ебать ты мутант, с херали на испанском пишешь? :)

      Delete
    2. Он крутой. Так всё просто объяснил и опубликовал.

      Delete
  2. once i click insert new contact botton it freezes , its not inserting data into db

    ReplyDelete
  3. function edit_data(uid, pc, s, name, idd, rmrk)
    {
    $.ajax({
    url:"edit.php",
    method:"POST",
    data:{uid:uid, pc:pc, s:s, name:name, idd:idd, rmrk:rmrk},
    dataType:"text",
    success:function(data){
    alert(data);
    }
    });
    }
    $(document).on('blur', '.pc_name', function(){
    var uid = $(this).data("id1");
    var pc_name = $(this).text();
    edit_data(uid, pc_name, "pc_name");
    });
    $(document).on('blur', '.s_n', function(){
    var uid = $(this).data("id2");
    var s_n = $(this).text();
    edit_data(uid, s_n, "s_n");
    });
    $(document).on('blur', '.uname', function(){
    var uid = $(this).data("id3");
    var uname = $(this).text();
    edit_data(uid,uname, "uname");
    });
    $(document).on('blur', '.id', function(){
    var uid = $(this).data("id4");
    var id = $(this).text();
    edit_data(uid,id, "id");
    });
    $(document).on('blur', '.remarque', function(){
    var uid = $(this).data("id5");
    var remarque = $(this).text();
    edit_data(uid,remarque, "remarque");
    });

    ce code ça marche pas, pourquoi ???

    ReplyDelete
    Replies
    1. Le même probème !

      tu as trouvé une solution ?

      Delete
    2. function edit_data(id, text, column_name)....
      data:{id:id, text:text, column_name:column_name}, ...

      this are the lines (change yours) .

      If you find a solution, please tell us. I have a similar problem with index.php / edit.php: UNDEFINED variable id...

      Delete
    3. All fine... I forgot a double quote in select.php

      Delete
    4. Working sample, with ADD, EDIT, DELETE, INSERT datas:

      http://www.valoryempresa.com/archives/Fixed-Header-Table-master-2.zip

      Delete
  4. Hola tengo un error que me tiene al borde. He hecho este ejemplo y funciona bien. Al hacerlo en otro proyecto me daun error:Error:Unknown column 'nombres' in 'field list'. He hecho de todo. No he cambiado nada y sigue igual. De hecho, hice un select * from donde el id = '"$id"' y guarde la consulta en un array y arroja cada valor. Gracias por tu ayuda y este es el cod:

    ReplyDelete
  5. Is there anyway to change the table border colors?

    ReplyDelete
  6. Thanks guys!

    Your concept helps me to complete my project. Thumbs up for your good work!

    ReplyDelete
  7. Thanks.This tutorial helped me a lot.

    ReplyDelete
  8. Great work. However edits/inserts only work in Firefox and Chrome. IE is not working.

    ReplyDelete
  9. Great tutorial, it works just fine.

    ReplyDelete
  10. so how can i import tools imported in head target so this can work locally

    ReplyDelete
  11. Hi, is it possible combine blur function and click function?

    ReplyDelete
  12. Hi, how can i edit data with 4 columns using your codes?

    ReplyDelete
  13. Hi ,excellent post. Please, advise how to handle situation on "insert" when not all dataType is text but if there were two types : text and date . Your reply would be greatly appreciated.

    ReplyDelete
  14. Hi ,excellent post. Please, advise how to handle situation on "insert" when not all dataType is text but if there were two types : text and date . Your reply would be greatly appreciated.

    ReplyDelete
  15. Hellow, how i can add page in select ?

    ReplyDelete
  16. after three row , new row dose not create , what will be the solution

    ReplyDelete
  17. after create three row , new row dose n0t generate , what will be the solution

    ReplyDelete
  18. I have a problem after editing files. Im on Laravel framework and when i run project live, index file is showing only title and headline. Script is not showing, but it's working, because when i run project from directory, script is showing table and info is updating fine.

    ReplyDelete