Ajax CRUD Operation using PDO with Bootstrap/Modal

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

Getting Started

I've used bootstrap and jQuery in this tutorial to enable bootstrap modals. These files are included in the downloadable of this tutorial but, if you want, you may download them using the links below:

Bootstrap
jQuery

Creating our Database

Next, we create our MySQL database.

I've included a .sql file in the downloadable of this tutorial which is a mysql database file. All you have to do is import the said file. If you have no idea on how to do this, please refer to my tutorial, How import .sql file to restore MySQL database.

You should be able to create a database with tables named mydatabase.

Creating our Connection

Next, we are going to create our connection to our database by creating a Class named Connection. We can do this by creating a new file, name it as connection.php and paste the code below.

  1. <?php
  2.  
  3. Class Connection{
  4.  
  5. private $server = "mysql:host=localhost;dbname=mydatabase";
  6. private $username = "root";
  7. private $password = "";
  8. private $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,);
  9. protected $conn;
  10.  
  11. public function open(){
  12. try{
  13. $this->conn = new PDO($this->server, $this->username, $this->password, $this->options);
  14. return $this->conn;
  15. }
  16. catch (PDOException $e){
  17. echo "There is some problem in connection: " . $e->getMessage();
  18. }
  19.  
  20. }
  21.  
  22. public function close(){
  23. $this->conn = null;
  24. }
  25.  
  26. }
  27.  
  28. ?>

Creating our Main Page

Next, we are going to create our main page where we show our table data and we are going to name this file as index.php.

  1. <!DOCTYPE html>
  2. <html>
  3. <head>
  4. <meta charset="utf-8">
  5. <title>Ajax CRUD Operation using PDO with Bootstrap/Modal</title>
  6. <link rel="stylesheet" type="text/css" href="bootstrap/css/bootstrap.min.css">
  7. </head>
  8. <body>
  9. <div class="container">
  10. <h1 class="page-header text-center">Ajax CRUD Operation using PDO</h1>
  11. <div class="row">
  12. <div class="col-sm-8 col-sm-offset-2">
  13. <button id="addnew" class="btn btn-primary"><span class="glyphicon glyphicon-plus"></span> New</button>
  14. <div id="alert" class="alert alert-info text-center" style="margin-top:20px; display:none;">
  15. <button class="close"><span aria-hidden="true">&times;</span></button>
  16. <span id="alert_message"></span>
  17. </div>
  18. <table class="table table-bordered table-striped" style="margin-top:20px;">
  19. <thead>
  20. <th>ID</th>
  21. <th>Firstname</th>
  22. <th>Lastname</th>
  23. <th>Address</th>
  24. <th>Action</th>
  25. </thead>
  26. <tbody id="tbody"></tbody>
  27. </table>
  28. </div>
  29. </div>
  30. </div>
  31. <!-- Modals -->
  32. <?php include('modal.html'); ?>
  33. <script src="jquery.min.js"></script>
  34. <script src="bootstrap/js/bootstrap.min.js"></script>
  35. <script src="app.js"></script>
  36. </body>
  37. </html>

Creating our Modal

Next, we create our modal that holds our add, edit and delete form. We do this by creating a new file, name it as modal.html and paste the code below.

  1. <!-- Add New -->
  2. <div class="modal fade" id="add" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  3. <div class="modal-dialog">
  4. <div class="modal-content">
  5. <div class="modal-header">
  6. <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
  7. <center><h4 class="modal-title" id="myModalLabel">Add New</h4></center>
  8. </div>
  9. <div class="modal-body">
  10. <div class="container-fluid">
  11. <form id="addForm">
  12. <div class="row form-group">
  13. <div class="col-sm-2">
  14. <label class="control-label" style="position:relative; top:7px;">Firstname:</label>
  15. </div>
  16. <div class="col-sm-10">
  17. <input type="text" class="form-control" name="firstname">
  18. </div>
  19. </div>
  20. <div class="row form-group">
  21. <div class="col-sm-2">
  22. <label class="control-label" style="position:relative; top:7px;">Lastname:</label>
  23. </div>
  24. <div class="col-sm-10">
  25. <input type="text" class="form-control" name="lastname">
  26. </div>
  27. </div>
  28. <div class="row form-group">
  29. <div class="col-sm-2">
  30. <label class="control-label" style="position:relative; top:7px;">Address:</label>
  31. </div>
  32. <div class="col-sm-10">
  33. <input type="text" class="form-control" name="address">
  34. </div>
  35. </div>
  36. </div>
  37. </div>
  38. <div class="modal-footer">
  39. <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
  40. <button type="submit" class="btn btn-primary"><span class="glyphicon glyphicon-floppy-disk"></span> Save</a>
  41. </form>
  42. </div>
  43.  
  44. </div>
  45. </div>
  46. </div>
  47.  
  48. <!-- Edit -->
  49. <div class="modal fade" id="edit" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  50. <div class="modal-dialog">
  51. <div class="modal-content">
  52. <div class="modal-header">
  53. <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
  54. <center><h4 class="modal-title" id="myModalLabel">Edit Member</h4></center>
  55. </div>
  56. <div class="modal-body">
  57. <div class="container-fluid">
  58. <form id="editForm">
  59. <input type="hidden" class="id" name="id">
  60. <div class="row form-group">
  61. <div class="col-sm-2">
  62. <label class="control-label" style="position:relative; top:7px;">Firstname:</label>
  63. </div>
  64. <div class="col-sm-10">
  65. <input type="text" class="form-control firstname" name="firstname">
  66. </div>
  67. </div>
  68. <div class="row form-group">
  69. <div class="col-sm-2">
  70. <label class="control-label" style="position:relative; top:7px;">Lastname:</label>
  71. </div>
  72. <div class="col-sm-10">
  73. <input type="text" class="form-control lastname" name="lastname">
  74. </div>
  75. </div>
  76. <div class="row form-group">
  77. <div class="col-sm-2">
  78. <label class="control-label" style="position:relative; top:7px;">Address:</label>
  79. </div>
  80. <div class="col-sm-10">
  81. <input type="text" class="form-control address" name="address">
  82. </div>
  83. </div>
  84. </div>
  85. </div>
  86. <div class="modal-footer">
  87. <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
  88. <button type="submit" class="btn btn-success"><span class="glyphicon glyphicon-check"></span> Update</a>
  89. </form>
  90. </div>
  91.  
  92. </div>
  93. </div>
  94. </div>
  95.  
  96. <!-- Delete -->
  97. <div class="modal fade" id="delete" tabindex="-1" role="dialog" aria-labelledby="myModalLabel" aria-hidden="true">
  98. <div class="modal-dialog">
  99. <div class="modal-content">
  100. <div class="modal-header">
  101. <button type="button" class="close" data-dismiss="modal" aria-hidden="true">&times;</button>
  102. <center><h4 class="modal-title" id="myModalLabel">Delete Member</h4></center>
  103. </div>
  104. <div class="modal-body">
  105. <p class="text-center">Are you sure you want to Delete</p>
  106. <h2 class="text-center fullname"></h2>
  107. </div>
  108. <div class="modal-footer">
  109. <button type="button" class="btn btn-default" data-dismiss="modal"><span class="glyphicon glyphicon-remove"></span> Cancel</button>
  110. <button type="button" class="btn btn-danger id"><span class="glyphicon glyphicon-trash"></span> Yes</button>
  111. </div>
  112.  
  113. </div>
  114. </div>
  115. </div>

Creating our Ajax Script

Next, we are going to create the file where we put all our ajax scripts and request. We name this file as app.js.

  1. $(document).ready(function(){
  2. fetch();
  3. //add
  4. $('#addnew').click(function(){
  5. $('#add').modal('show');
  6. });
  7. $('#addForm').submit(function(e){
  8. e.preventDefault();
  9. var addform = $(this).serialize();
  10. //console.log(addform);
  11. $.ajax({
  12. method: 'POST',
  13. url: 'add.php',
  14. data: addform,
  15. dataType: 'json',
  16. success: function(response){
  17. $('#add').modal('hide');
  18. if(response.error){
  19. $('#alert').show();
  20. $('#alert_message').html(response.message);
  21. }
  22. else{
  23. $('#alert').show();
  24. $('#alert_message').html(response.message);
  25. fetch();
  26. }
  27. }
  28. });
  29. });
  30. //
  31.  
  32. //edit
  33. $(document).on('click', '.edit', function(){
  34. var id = $(this).data('id');
  35. getDetails(id);
  36. $('#edit').modal('show');
  37. });
  38. $('#editForm').submit(function(e){
  39. e.preventDefault();
  40. var editform = $(this).serialize();
  41. $.ajax({
  42. method: 'POST',
  43. url: 'edit.php',
  44. data: editform,
  45. dataType: 'json',
  46. success: function(response){
  47. if(response.error){
  48. $('#alert').show();
  49. $('#alert_message').html(response.message);
  50. }
  51. else{
  52. $('#alert').show();
  53. $('#alert_message').html(response.message);
  54. fetch();
  55. }
  56.  
  57. $('#edit').modal('hide');
  58. }
  59. });
  60. });
  61. //
  62.  
  63. //delete
  64. $(document).on('click', '.delete', function(){
  65. var id = $(this).data('id');
  66. getDetails(id);
  67. $('#delete').modal('show');
  68. });
  69.  
  70. $('.id').click(function(){
  71. var id = $(this).val();
  72. $.ajax({
  73. method: 'POST',
  74. url: 'delete.php',
  75. data: {id:id},
  76. dataType: 'json',
  77. success: function(response){
  78. if(response.error){
  79. $('#alert').show();
  80. $('#alert_message').html(response.message);
  81. }
  82. else{
  83. $('#alert').show();
  84. $('#alert_message').html(response.message);
  85. fetch();
  86. }
  87.  
  88. $('#delete').modal('hide');
  89. }
  90. });
  91. });
  92. //
  93.  
  94. //hide message
  95. $(document).on('click', '.close', function(){
  96. $('#alert').hide();
  97. });
  98.  
  99. });
  100.  
  101. function fetch(){
  102. $.ajax({
  103. method: 'POST',
  104. url: 'fetch.php',
  105. success: function(response){
  106. $('#tbody').html(response);
  107. }
  108. });
  109. }
  110.  
  111. function getDetails(id){
  112. $.ajax({
  113. method: 'POST',
  114. url: 'fetch_row.php',
  115. data: {id:id},
  116. dataType: 'json',
  117. success: function(response){
  118. if(response.error){
  119. $('#edit').modal('hide');
  120. $('#delete').modal('hide');
  121. $('#alert').show();
  122. $('#alert_message').html(response.message);
  123. }
  124. else{
  125. $('.id').val(response.data.id);
  126. $('.firstname').val(response.data.firstname);
  127. $('.lastname').val(response.data.lastname);
  128. $('.address').val(response.data.address);
  129. $('.fullname').html(response.data.firstname + ' ' + response.data.lastname);
  130. }
  131. }
  132. });
  133. }

Fetching our Table Data

Next, we fetch our table data that we will be going to display. This file is accessed via ajax request. We name this as fetch.php.

  1. <?php
  2. include_once('connection.php');
  3.  
  4. $database = new Connection();
  5. $db = $database->open();
  6.  
  7. try{
  8. $sql = 'SELECT * FROM members';
  9. foreach ($db->query($sql) as $row) {
  10. ?>
  11. <tr>
  12. <td><?php echo $row['id']; ?></td>
  13. <td><?php echo $row['firstname']; ?></td>
  14. <td><?php echo $row['lastname']; ?></td>
  15. <td><?php echo $row['address']; ?></td>
  16. <td>
  17. <button class="btn btn-success btn-sm edit" data-id="<?php echo $row['id']; ?>"><span class="glyphicon glyphicon-edit"></span> Edit</button>
  18. <button class="btn btn-danger btn-sm delete" data-id="<?php echo $row['id']; ?>"><span class="glyphicon glyphicon-trash"></span> Delete</button>
  19. </td>
  20. </tr>
  21. <?php
  22. }
  23. }
  24. catch(PDOException $e){
  25. echo "There is some problem in connection: " . $e->getMessage();
  26. }
  27.  
  28. //close connection
  29. $database->close();
  30.  
  31. ?>

Creating our Add Script

Next, we are going to create our add script which is also accessed via ajax request. We name this file as add.php.

  1. <?php
  2. include_once('connection.php');
  3.  
  4. $output = array('error' => false);
  5.  
  6. $database = new Connection();
  7. $db = $database->open();
  8. try{
  9. //make use of prepared statement to prevent sql injection
  10. $stmt = $db->prepare("INSERT INTO members (firstname, lastname, address) VALUES (:firstname, :lastname, :address)");
  11. //if-else statement in executing our prepared statement
  12. if ($stmt->execute(array(':firstname' => $_POST['firstname'] , ':lastname' => $_POST['lastname'] , ':address' => $_POST['address'])) ){
  13. $output['message'] = 'Member added successfully';
  14. }
  15. else{
  16. $output['error'] = true;
  17. $output['message'] = 'Something went wrong. Cannot add member';
  18. }
  19.  
  20. }
  21. catch(PDOException $e){
  22. $output['error'] = true;
  23. $output['message'] = $e->getMessage();
  24. }
  25.  
  26. //close connection
  27. $database->close();
  28.  
  29. echo json_encode($output);
  30.  
  31. ?>

Fetching Single Row

Next, we fetch a single row of data from our database also via ajax request which will be used in our edit and delete form. Create a new file and name it as fetch_row.php.

  1. <?php
  2. include_once('connection.php');
  3.  
  4. $output = array('error' => false);
  5.  
  6. $database = new Connection();
  7. $db = $database->open();
  8.  
  9. try{
  10. $id = $_POST['id'];
  11. $stmt = $db->prepare("SELECT * FROM members WHERE id = :id");
  12. $stmt->bindParam(':id', $id);
  13. $stmt->execute();
  14. $output['data'] = $stmt->fetch();
  15. }
  16. catch(PDOException $e){
  17. $output['error'] = true;
  18. $output['message'] = $e->getMessage();
  19. }
  20.  
  21. //close connection
  22. $database->close();
  23.  
  24. echo json_encode($output);
  25.  
  26. ?>

Creating our Edit Script

Next, we are going to create our edit script and also accessed via ajax request by creating a new file, name it as edit.php and paste the code below.

  1. <?php
  2. include_once('connection.php');
  3.  
  4. $output = array('error' => false);
  5.  
  6. $database = new Connection();
  7. $db = $database->open();
  8. try{
  9. $id = $_POST['id'];
  10. $firstname = $_POST['firstname'];
  11. $lastname = $_POST['lastname'];
  12. $address = $_POST['address'];
  13.  
  14. $sql = "UPDATE members SET firstname = '$firstname', lastname = '$lastname', address = '$address' WHERE id = '$id'";
  15. //if-else statement in executing our query
  16. if($db->exec($sql)){
  17. $output['message'] = 'Member updated successfully';
  18. }
  19. else{
  20. $output['error'] = true;
  21. $output['message'] = 'Something went wrong. Cannot update member';
  22. }
  23.  
  24. }
  25. catch(PDOException $e){
  26. $output['error'] = true;
  27. $output['message'] = $e->getMessage();
  28. }
  29.  
  30. //close connection
  31. $database->close();
  32.  
  33. echo json_encode($output);
  34.  
  35. ?>

Creating our Delete Script

Lastly, we create our delete script which is also accessed via ajax request. Create a new file and name it as delete.php.

  1. <?php
  2. include_once('connection.php');
  3.  
  4. $output = array('error' => false);
  5.  
  6. $database = new Connection();
  7. $db = $database->open();
  8. try{
  9. $sql = "DELETE FROM members WHERE id = '".$_POST['id']."'";
  10. //if-else statement in executing our query
  11. if($db->exec($sql)){
  12. $output['message'] = 'Member deleted successfully';
  13. }
  14. else{
  15. $output['error'] = true;
  16. $output['message'] = 'Something went wrong. Cannot delete member';
  17. }
  18. }
  19. catch(PDOException $e){
  20. $output['error'] = true;
  21. $output['message'] = $e->getMessage();;
  22. }
  23.  
  24. //close connection
  25. $database->close();
  26.  
  27. echo json_encode($output);
  28.  
  29. ?>

That ends this tutorial. Happy Coding :)


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.

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.