CRUD Operation using PHP/MySQLi and AJAX/jQuery

Language

In this tutorial, I'm going to show you how to create a simple CRUD Operation using AJAX/JQuery. CRUD means Create, Update and Delete. Also, the purpose of using AJAX/JQuery is that the page won't reload after an operation. I've used bootstrap in this tutorial to slightly improved the visuals.

Creating our Database

First and most important step in to create our database. This will serve as storage for our data. 1. Open phpMyAdmin. 2. Click databases, create a database and name it as "crud_bootstrap". 3. After creating a database, click the SQL and paste the below code. See image below for detailed instruction.
  1. CREATE TABLE `user` (
  2. `userid` INT(11) NOT NULL AUTO_INCREMENT,
  3. `firstname` VARCHAR(30) NOT NULL,
  4. `lastname` VARCHAR(30) NOT NULL,
  5. PRIMARY KEY(`userid`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
crud

Creating our Connection

Next step is to create a database connection and save it as "conn.php". This file will serve as our bridge between our form and our database. To create the file, open your HTML code editor and paste the code below after the tag.
  1. <?php
  2.  
  3. //MySQLi Procedural
  4. $conn = mysqli_connect("localhost","root","","crud_bootstrap");
  5. if (!$conn) {
  6. die("Connection failed: " . mysqli_connect_error());
  7. }
  8.  
  9. ?>

Creating our Table

Next, we create our sample table. In this tutorial, we're going to create member table. We name this as "index.php". Also included in this page is our jquery and ajax codes in the script tag below.
  1. <?php
  2. include('conn.php');
  3. ?>
  4. <!DOCTYPE html>
  5. <html lang = "en">
  6. <head>
  7. <meta charset = "UTF-8" name = "viewport" content = "width-device=width, initial-scale=1" />
  8. <link rel = "stylesheet" type = "text/css" href = "css/bootstrap.css" />
  9. <title>PHP CRUD Operation using AJAX/JQuery</title>
  10. </head>
  11. <body>
  12. <div style="height:30px;"></div>
  13. <div class = "row">
  14. <div class = "col-md-3">
  15. </div>
  16. <div class = "col-md-6 well">
  17. <div class="row">
  18. <div class="col-lg-12">
  19. <center><h2 class = "text-primary">PHP - CRUD Operation using AJAX/JQuery</h2></center>
  20. <hr>
  21. <div>
  22. <form class = "form-inline">
  23. <div class = "form-group">
  24. <label>Firstname:</label>
  25. <input type = "text" id = "firstname" class = "form-control">
  26. </div>
  27. <div class = "form-group">
  28. <label>Lastname:</label>
  29. <input type = "text" id = "lastname" class = "form-control">
  30. </div>
  31. <div class = "form-group">
  32. <button type = "button" id="addnew" class = "btn btn-primary"><span class = "glyphicon glyphicon-plus"></span> Add</button>
  33. </div>
  34. </form>
  35. </div>
  36. </div>
  37. </div><br>
  38. <div class="row">
  39. <div id="userTable"></div>
  40. </div>
  41. </div>
  42. </div>
  43. </body>
  44. <script src = "js/jquery-3.1.1.js"></script>
  45. <script src = "js/bootstrap.js"></script>
  46. <script type = "text/javascript">
  47. $(document).ready(function(){
  48. showUser();
  49. //Add New
  50. $(document).on('click', '#addnew', function(){
  51. if ($('#firstname').val()=="" || $('#lastname').val()==""){
  52. alert('Please input data first');
  53. }
  54. else{
  55. $firstname=$('#firstname').val();
  56. $lastname=$('#lastname').val();
  57. $.ajax({
  58. type: "POST",
  59. url: "addnew.php",
  60. data: {
  61. firstname: $firstname,
  62. lastname: $lastname,
  63. add: 1,
  64. },
  65. success: function(){
  66. showUser();
  67. }
  68. });
  69. }
  70. });
  71. //Delete
  72. $(document).on('click', '.delete', function(){
  73. $id=$(this).val();
  74. $.ajax({
  75. type: "POST",
  76. url: "delete.php",
  77. data: {
  78. id: $id,
  79. del: 1,
  80. },
  81. success: function(){
  82. showUser();
  83. }
  84. });
  85. });
  86. //Update
  87. $(document).on('click', '.updateuser', function(){
  88. $uid=$(this).val();
  89. $('#edit'+$uid).modal('hide');
  90. $('body').removeClass('modal-open');
  91. $('.modal-backdrop').remove();
  92. $ufirstname=$('#ufirstname'+$uid).val();
  93. $ulastname=$('#ulastname'+$uid).val();
  94. $.ajax({
  95. type: "POST",
  96. url: "update.php",
  97. data: {
  98. id: $uid,
  99. firstname: $ufirstname,
  100. lastname: $ulastname,
  101. edit: 1,
  102. },
  103. success: function(){
  104. showUser();
  105. }
  106. });
  107. });
  108.  
  109. });
  110.  
  111. //Showing our Table
  112. function showUser(){
  113. $.ajax({
  114. url: 'show_user.php',
  115. type: 'POST',
  116. async: false,
  117. data:{
  118. show: 1
  119. },
  120. success: function(response){
  121. $('#userTable').html(response);
  122. }
  123. });
  124. }
  125.  
  126. </script>
  127. </html>

Creating our Show Code

Next step is to create our show code. This is the code that we will call after an operation to show to changes in our table. We name the code as "show_user.php".
  1. <?php
  2. include('conn.php');
  3. if(isset($_POST['show'])){
  4. ?>
  5. <table class = "table table-bordered alert-warning table-hover">
  6. <thead>
  7. <th>Firstname</th>
  8. <th>Lastname</th>
  9. <th>Action</th>
  10. </thead>
  11. <tbody>
  12. <?php
  13. $quser=mysqli_query($conn,"select * from `user`");
  14. while($urow=mysqli_fetch_array($quser)){
  15. ?>
  16. <tr>
  17. <td><?php echo $urow['firstname']; ?></td>
  18. <td><?php echo $urow['lastname']; ?></td>
  19. <td><button class="btn btn-success" data-toggle="modal" data-target="#edit<?php echo $urow['userid']; ?>"><span class = "glyphicon glyphicon-pencil"></span> Edit</button> | <button class="btn btn-danger delete" value="<?php echo $urow['userid']; ?>"><span class = "glyphicon glyphicon-trash"></span> Delete</button>
  20. <?php include('edit_modal.php'); ?>
  21. </td>
  22. </tr>
  23. <?php
  24. }
  25.  
  26. ?>
  27. </tbody>
  28. </table>
  29. <?php
  30. }
  31.  
  32. ?>

Creating our Add Code

Next, we create our add operaiton code which will add new data into our table. We name this as "addnew.php".
  1. <?php
  2. include('conn.php');
  3. if(isset($_POST['add'])){
  4. $firstname=$_POST['firstname'];
  5. $lastname=$_POST['lastname'];
  6.  
  7. mysqli_query($conn,"insert into `user` (firstname, lastname) values ('$firstname', '$lastname')");
  8. }
  9. ?>

Creating our Edit Modal

Next step is to create our edit modal which we have included in our index.php. We name this modal as "edit_modal.php".
  1. <div class="modal fade" id="edit<?php echo $urow['userid']; ?>" tabindex="-1" role="dialog" aria-labelledby="myModalLabel">
  2. <?php
  3. $n=mysqli_query($conn,"select * from `user` where userid='".$urow['userid']."'");
  4. $nrow=mysqli_fetch_array($n);
  5. ?>
  6. <div class="modal-dialog" role="document">
  7. <div class="modal-content">
  8. <div class = "modal-header">
  9. <button type="button" class="close" data-dismiss="modal" aria-label="Close"><span aria-hidden="true">&times;</span></button>
  10. <center><h3 class = "text-success modal-title">Update Member</h3></center>
  11. </div>
  12. <form class="form-inline">
  13. <div class="modal-body">
  14. Firstname: <input type="text" value="<?php echo $nrow['firstname']; ?>" id="ufirstname<?php echo $urow['userid']; ?>" class="form-control">
  15. Lastname: <input type="text" value="<?php echo $nrow['lastname']; ?>" id="ulastname<?php echo $urow['userid']; ?>" class="form-control">
  16. </div>
  17. <div class="modal-footer">
  18. <button type="button" class="btn btn-default" data-dismiss="modal"><span class = "glyphicon glyphicon-remove"></span> Cancel</button> | <button type="button" class="updateuser btn btn-success" value="<?php echo $urow['userid']; ?>"><span class = "glyphicon glyphicon-floppy-disk"></span> Save</button>
  19. </div>
  20. </form>
  21. </div>
  22. </div>
  23. </div>

Creating our Edit Code

Next, we create our edit code that will edit our selected row. We name this as "update.php".
  1. <?php
  2. include('conn.php');
  3. if(isset($_POST['edit'])){
  4. $id=$_POST['id'];
  5. $firstname=$_POST['firstname'];
  6. $lastname=$_POST['lastname'];
  7.  
  8. mysqli_query($conn,"update `user` set firstname='$firstname', lastname='$lastname' where userid='$id'");
  9. }
  10. ?>

Creating our Delete Code

Lastly, we create our delete code which will delete our selected row. We name this as "delete.php".
  1. <?php
  2. include('conn.php');
  3. if(isset($_POST['del'])){
  4. $id=$_POST['id'];
  5. mysqli_query($conn,"delete from `user` where userid='$id'");
  6. }
  7. ?>

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. After downloading it, you will need a program like Winzip to decompress it.

Virus note: All files are scanned once-a-day by SourceCodester.com for viruses, but new viruses come out every day, so no prevention program can catch 100% of them.

FOR YOUR OWN SAFETY, PLEASE:

1. Re-scan downloaded files using your personal virus checker before using it.
2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.

Comments

Submitted byNeylson (not verified)on Tue, 09/05/2017 - 17:20

Hello nurhodelta_17, please could i have your contact? Thanks

Hello, just pm me on my private contact info here in sourcecodester. You can use this link: https://www.sourcecodester.com/messages/new/224918?destination=user/224918
Submitted bykim seakliv (not verified)on Wed, 07/11/2018 - 14:08

it 's important for my studying
Submitted byBrano (not verified)on Mon, 08/20/2018 - 22:01

Hi, big thanks for awesome tutorial. Everything works as expected just updating user will reload whole page after success for some reason. Does that happen to you as well? If you have some idea why does that happen can you please let us know? Thanks, Brano
Submitted byBrano (not verified)on Tue, 08/21/2018 - 18:52

In reply to by Brano (not verified)

I realized that I was missing on my button element this code type="button". After I specified type of button it works correctly and page refreshes without reloading.

Add new comment