CRUD Operation using PHP/MySQLi and AJAX/jQuery

Submitted by: 
Language: 
Visitors have accessed this post 17985 times.

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

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

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.

it 's important for my studying

good tutorial ajax php mysql crud

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

Hi,
i just send you my message

Add new comment

Filtered HTML

  • Web page addresses and e-mail addresses turn into links automatically.
  • You may insert videos with [video:URL]
  • Allowed HTML tags: <a> <em> <strong> <cite> <blockquote> <code> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <th> <img> <h1> <h2> <h3> <iframe> [video]
  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <asp>, <c>, <cpp>, <csharp>, <css>, <html4strict>, <java>, <javascript>, <mysql>, <php>, <python>, <sql>, <vb>, <vbnet>. The supported tag styles are: <foo>, [foo].
  • Lines and paragraphs break automatically.

Plain text

  • No HTML tags allowed.
  • Lines and paragraphs break automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.