PHP - Live CRUD Operation In HTML Table

In this tutorial we will create a Simple Live CRUD Operation In HTML Table Using PHP/jQuery. jQuery is a cross-platform JavaScript library designed to simplify the client-side scripting of HTML. It is designed to make it easier to navigate a document, via classes and id's attribute. So let's now do the coding... Before we started: First you have to download & install WAMPserver or any local server that run PHP scripts. Here's the link for WAMP server http://www.wampserver.com/en/. Creating the database Connection This is the where the database connection, just simple copy/paste the provided code below.
  1. <?php
  2. $conn = new mysqli('localhost', 'root', '', 'phptut');
  3. if(!$conn){
  4. die("Fatal Error: Connection Error!");
  5. }
  6. ?>
The Main Interface This is the where the database connection, just simple copy/paste the provided code below.
  1. <!DOCTYPE html>
  2. <html lang = "en">
  3. <head>
  4. <title>PHP - Live CRUD Operation In HTML Table</title>
  5. <link rel = "stylesheet" type = "text/css" href = "css/bootstrap.css" />
  6. <meta charset = "UTF-8" name = "viewport" content = "width=device-width, initial-scale=1"/>
  7. </head>
  8. <body>
  9. <nav class = "navbar navbar-default">
  10. <div class = "container-fluid">
  11. <a class = "navbar-brand" href = "https://sourcecodester.com">Sourcecodester</a>
  12. </div>
  13. </nav>
  14. <div class = "col-md-3"></div>
  15. <div class = "col-md-6 well">
  16. <h3 class = "text-primary">PHP - Live CRUD Operation In HTML Table</h3>
  17. <hr style = "border-top:1px solid #000;" />
  18. <div id = "data"></data>
  19. </div>
  20. </body>
  21. <script src = "js/jquery-3.2.1.js"></script>
  22.  
  23. <script src = "js/script.js"></script>
  24. </html>
The Read Script This is where the code display all the data. To do that just copy/paste the code below.
  1. <?php
  2. require 'connect.php';
  3. $result = '';
  4. $query = $conn->query("SELECT * FROM `member`");
  5.  
  6. $result .= '
  7. <table class = "table table-bordered">
  8. <thead>
  9. <tr>
  10. <th>Firstname</th>
  11. <th>Lastname</th>
  12. <th>Address</th>
  13. <th>Action</th>
  14. </tr>
  15. </thead>
  16. <tbody>
  17. ';
  18. if($query->num_rows > 0){
  19. while($fetch = $query->fetch_array()){
  20. $result .='
  21.  
  22. <tr>
  23. <td class = "firstname" data-firstname = '.$fetch['mem_id'].' contenteditable >'.$fetch['firstname'].'</td>
  24. <td class = "lastname" data-lastname = '.$fetch['mem_id'].' contenteditable>'.$fetch['lastname'].'</td>
  25. <td class = "address" data-address = '.$fetch['mem_id'].' contenteditable>'.$fetch['address'].'</td>
  26. <td><center><button class = "btn btn-danger btn_delete" name = "'.$fetch['mem_id'].'"><span class = "glyphicon glyphicon-remove"></span></button></center></td>
  27. </tr>
  28.  
  29. ';
  30.  
  31. }
  32.  
  33. $result .='
  34. <tr>
  35. <td id = "firstname" contenteditable></td>
  36. <td id = "lastname" contenteditable></td>
  37. <td id = "address" contenteditable></td>
  38. <td><center><button class = "btn btn-success" id = "btn_add"><span class = "glyphicon glyphicon-plus"></span></button></center></td>
  39. </tr>
  40.  
  41. ';
  42.  
  43. }else{
  44. $result .='
  45. <tr>
  46. <td id = "firstname" contenteditable></td>
  47. <td id = "lastname" contenteditable></td>
  48. <td id = "address" contenteditable></td>
  49. <td><center><button class = "btn btn-success" id = "btn_add"><span class = "glyphicon glyphicon-plus"></span></button></center></td>
  50. </tr>
  51. ';
  52. }
  53.  
  54. $result .= '
  55. </tbody>
  56. </table>
  57. ';
  58.  
  59. echo $result;
  60.  
  61. ?>
The Create Script This is where the code insert the data to database server. To do that copy/paste the code below.
  1. <?php
  2. require 'connect.php';
  3. $firstname = $_POST['firstname'];
  4. $lastname = $_POST['lastname'];
  5. $address = $_POST['address'];
  6.  
  7. $query = $conn->query("INSERT INTO `member` (firstname, lastname, address) VALUES('$firstname', '$lastname', '$address')");
  8.  
  9. echo 'Data Inserted';
  10. ?>
The Update Script This is where the code update all the data when the highlighted field is being edited. To do that just simply copy/paste the code below.
  1. <?php
  2. require 'connect.php';
  3.  
  4. $id = $_POST['id'];
  5. $text = $_POST['text'];
  6. $column = $_POST['column'];
  7.  
  8.  
  9. if($column == "firstname"){
  10. $query = $conn->query("UPDATE `member` SET `firstname` = '$text' WHERE `mem_id` = '$id'");
  11. }
  12.  
  13. if($column == "lastname"){
  14. $query = $conn->query("UPDATE `member` SET `lastname` = '$text' WHERE `mem_id` = '$id'");
  15. }
  16.  
  17. if($column == "address"){
  18. $query = $conn->query("UPDATE `member` SET `address` = '$text' WHERE `mem_id` = '$id'");
  19. }
  20.  
  21. echo "Data Updated";
  22. ?>
The Delete Script This is where the code delete the row of the data from database server. Just copy/paste the code below.
  1. <?php
  2. require "connect.php";
  3.  
  4. $mem_id = $_POST['mem_id'];
  5. $conn->query("DELETE FROM `member` WHERE `mem_id` = '$mem_id'");
  6.  
  7. echo "Data Deleted!";
  8. ?>
The jQuery Script This script handles all the necessary call function for all the PHP script. To make this happen just simple copy/paste the code below.
  1. $(document).ready(function(){
  2. ReadData();
  3.  
  4. function ReadData(){
  5. $.ajax({
  6. url: "select.php",
  7. method: "POST",
  8. success: function(data){
  9. $('#data').html(data);
  10. }
  11. })
  12.  
  13. }
  14.  
  15. $(document).on('click', '#btn_add', function(){
  16. var firstname = $('#firstname').text();
  17. var lastname = $('#lastname').text();
  18. var address = $('#address').text();
  19.  
  20.  
  21. $.ajax({
  22. url: "insert.php",
  23. method: "POST",
  24. dataType: "text",
  25. data:{
  26. firstname: firstname, lastname: lastname, address: address
  27. },
  28. success: function(data){
  29. alert(data);
  30. ReadData();
  31. }
  32. });
  33.  
  34. });
  35.  
  36.  
  37. $(document).on('blur', '.firstname', function(){
  38. var id = $(this).data("firstname");
  39. var firstname = $(this).text();
  40. UpdateData(id, firstname, 'firstname');
  41. });
  42.  
  43.  
  44. $(document).on('blur', '.lastname', function(){
  45. var id = $(this).data("lastname");
  46. var lastname = $(this).text();
  47. UpdateData(id, lastname, 'lastname');
  48. });
  49.  
  50.  
  51. $(document).on('blur', '.address', function(){
  52. var id = $(this).data("address");
  53. var address = $(this).text();
  54. UpdateData(id, address, 'address');
  55. });
  56.  
  57. $(document).on('click', '.btn_delete', function(){
  58. var id = $(this).attr('name');
  59.  
  60. $.ajax({
  61. url: 'delete.php',
  62. method: "POST",
  63. data:{
  64. mem_id: id
  65. },
  66. success: function(data){
  67. alert(data);
  68. ReadData();
  69. }
  70. });
  71. });
  72.  
  73. function UpdateData(id, text, column){
  74. $.ajax({
  75. url: "update.php",
  76. method: "POST",
  77. data: {
  78. id: id, text: text, column: column
  79. },
  80. success: function(data){
  81.  
  82. }
  83. });
  84. }
  85.  
  86.  
  87. });
There you have it we just create a Simple Live CRUD Operation In HTML Table. I hope that this simple tutorial help to your projects. For more updates and tutorials just kindly visit this site. Enjoy Coding!!!

Tags

Add new comment