Perekodan

Sistem Pendaftaran

Post Top Ad


In this tutorial, you will learn how to create or develop a PHP CRUD Operations w/o Pagre Refresh/Reloading using Ajax and DataTables. Here, we will enlist the data from the database using the server-side processing of DataTables. This tutorial is helpful especially for optimizing the page load of your future PHP Project.

The main goal of the simple web app that we will be creating is to display the data/records from the database into a table using DataTables which means it has a sort, filter, limit, and pagination functions. We will be using Ajax to process the new, updated, and delete data. Lastly, in each process, the table must update its data without leaving the current page/reloading/refreshing.

Getting Started

In this tutorial, I will be using the following

  • XAMPP as my local web-server to run my PHP scripts.
  • Bootstrap 5 for the user interface or the design of the web app
  • jQuery to use Ajax
  • DataTables andd please include the Button Extention

Compile the plugins/libraries files in a single foleder in your XAMPP's htdocs directory. Please open your XAMPP's Control Panel and start the Apache and MySQL.

Creating the Database

In your web browser, browse the PHPMyAdmin i.e. http://localhost/phpmyadmin and create a new database naming dummy_db. Then navigate to the database SQL Tab and paster the SQL Script below to create our database table and columns.

  1. CREATE TABLE `authors` (
  2.   `id` int(11) NOT NULL,
  3.   `first_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  4.   `last_name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  5.   `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
  6.   `birthdate` date NOT NULL,
  7.   `added` timestamp NOT NULL DEFAULT current_timestamp()
  8. ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  9.  
  10. ALTER TABLE `authors`
  11.   ADD PRIMARY KEY (`id`);
  12.  
  13. ALTER TABLE `authors`
  14.   MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=102;
Creating our Database Connection

In you source code folder, create a new PHP file naming connect.php. Copy/Paste the script below. The script will create a connection to our database.

  1. <?php
  2. $host = "localhost";
  3. $username = "root";
  4. $password = "";
  5. $database = "dummy_db";
  6.  
  7. $conn = new mysqli($host, $username, $password, $database);
  8. if(!$conn){
  9.     echo "Database connection failed. Error:".$conn->error;
  10.     exit;
  11. }
  12. ?>

Creating the Interface

Create a new PHP file naming index.php. Paste the following script, to create our web app's interface. Please make sure that plugins/libraries path are correct in your end for both CSS Links and External JavaScript.

  1. <!DOCTYPE html>
  2. <html lang="en">
  3.  
  4. <head>
  5.     <meta charset="UTF-8">
  6.     <meta http-equiv="X-UA-Compatible" content="IE=edge">
  7.     <meta name="viewport" content="width=device-width, initial-scale=1.0">
  8.     <title>PHP CRUD using Ajax and DataTables</title>
  9.     <link rel="stylesheet" href="css/bootstrap.css">
  10.     <link rel="stylesheet" href="DataTables/datatables.min.css">
  11.     <link rel="stylesheet" href="https://cdn.datatables.net/buttons/2.0.0/css/buttons.dataTables.min.css">
  12.     <script src="js/jquery-3.6.0.min.js"></script>
  13.     <script src="js/bootstrap.js"></script>
  14.     <script src="DataTables/datatables.min.js"></script>
  15.     <script src="https://cdn.datatables.net/buttons/2.0.0/js/dataTables.buttons.min.js"></script>
  16.     <script src="js/script.js"></script>
  17.     <style>
  18.         button.dt-button.btn-primary{
  19.             background:var(--bs-primary)!important;
  20.             color:white;
  21.         }
  22.     </style>
  23. </head>
  24.  
  25. <body class="">
  26. <nav class="navbar navbar-expand-lg navbar-light bg-dark bg-gradient">
  27.   <div class="container">
  28.     <a class="navbar-brand text-light text-shadow" href="//www.sourcecodester" target="_blank">SourceCodester</a>
  29.   </div>
  30. </nav>
  31.  
  32.     <div class="container py-5 h-100">
  33.         <div class="row">
  34.             <div class="col-lg-12">
  35.                 <h3 class="text-center"><b>PHP CRUD w/o Refresh using Ajax and DataTable</b></h3>
  36.             </div>
  37.         </div>
  38.         <hr>
  39.         <div class="row">
  40.             <div class="col-md-12" id="msg"></div>
  41.         </div>
  42.         <div class="row">
  43.             <div class="col-lg-12">
  44.                 <table class="table table-hover table-bordered table-striped" id="authors-tbl">
  45.                     <thead>
  46.                         <tr class="bg-dark text-light bg-gradient bg-opacity-150">
  47.                             <th class="px-1 py-1 text-center">#</th>
  48.                             <th class="px-1 py-1 text-center">First Name</th>
  49.                             <th class="px-1 py-1 text-center">Last Name</th>
  50.                             <th class="px-1 py-1 text-center">Email</th>
  51.                             <th class="px-1 py-1 text-center">Birth Date</th>
  52.                             <th class="px-1 py-1 text-center">Action</th>
  53.                         </tr>
  54.                     </thead>
  55.                     <tfoot>
  56.                         <tr class="bg-dark text-light bg-gradient bg-opacity-150">
  57.                             <th class="px-1 py-1 text-center">#</th>
  58.                             <th class="px-1 py-1 text-center">First Name</th>
  59.                             <th class="px-1 py-1 text-center">Last Name</th>
  60.                             <th class="px-1 py-1 text-center">Email</th>
  61.                             <th class="px-1 py-1 text-center">Birth Date</th>
  62.                             <th class="px-1 py-1 text-center">Action</th>
  63.                         </tr>
  64.                     </tfoot>
  65.                 </table>
  66.             </div>
  67.         </div>
  68.     </div>
  69.     <!-- Add Modal -->
  70.     <div class="modal fade" id="add_modal" data-bs-backdrop="static">
  71.         <div class="modal-dialog">
  72.             <div class="modal-content">
  73.                 <div class="modal-header">
  74.                     <h5 class="modal-title">Add Author</h5>
  75.                     <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
  76.                 </div>
  77.                 <div class="modal-body">
  78.                     <div class="container-fluid">
  79.                         <form action="" id="new-author-frm">
  80.                             <div class="form-group">
  81.                                 <label for="first_name" class="control-label">First Name</label>
  82.                                 <input type="text" class="form-control rounded-0" id="first_name" name="first_name" required>
  83.                             </div>
  84.                             <div class="form-group">
  85.                                 <label for="last_name" class="control-label">Last Name</label>
  86.                                 <input type="text" class="form-control rounded-0" id="last_name" name="last_name" required>
  87.                             </div>
  88.                             <div class="form-group">
  89.                                 <label for="email" class="control-label">Email</label>
  90.                                 <input type="text" class="form-control rounded-0" id="email" name="email" required>
  91.                             </div>
  92.                             <div class="form-group">
  93.                                 <label for="birthdate" class="control-label">Date of Birth</label>
  94.                                 <input type="date" class="form-control rounded-0" id="birthdate" name="birthdate" required>
  95.                             </div>
  96.                         </form>
  97.                     </div>
  98.                 </div>
  99.                 <div class="modal-footer">
  100.                     <button type="submit" class="btn btn-primary" form="new-author-frm">Save</button>
  101.                     <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
  102.                 </div>
  103.             </div>
  104.         </div>
  105.     </div>
  106.     <!-- /Add Modal -->
  107.     <!-- Edit Modal -->
  108.     <div class="modal fade" id="edit_modal" data-bs-backdrop="static">
  109.         <div class="modal-dialog">
  110.             <div class="modal-content">
  111.                 <div class="modal-header">
  112.                     <h5 class="modal-title">Edit Author's Details</h5>
  113.                     <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button>
  114.                 </div>
  115.                 <div class="modal-body">
  116.                     <div class="container-fluid">
  117.                         <form action="" id="edit-author-frm">
  118.                             <input type="hidden" name="id">
  119.                             <div class="form-group">
  120.                                 <label for="first_name" class="control-label">First Name</label>
  121.                                 <input type="text" class="form-control rounded-0" id="first_name" name="first_name" required>
  122.                             </div>
  123.                             <div class="form-group">
  124.                                 <label for="last_name" class="control-label">Last Name</label>
  125.                                 <input type="text" class="form-control rounded-0" id="last_name" name="last_name" required>
  126.                             </div>
  127.                             <div class="form-group">
  128.                                 <label for="email" class="control-label">Email</label>
  129.                                 <input type="text" class="form-control rounded-0" id="email" name="email" required>
  130.                             </div>
  131.                             <div class="form-group">
  132.                                 <label for="birthdate" class="control-label">Date of Birth</label>
  133.                                 <input type="date" class="form-control rounded-0" id="birthdate" name="birthdate" required>
  134.                             </div>
  135.                         </form>
  136.                     </div>
  137.                 </div>
  138.                 <div class="modal-footer">
  139.                     <button type="submit" class="btn btn-primary" form="edit-author-frm">Save</button>
  140.                     <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Close</button>
  141.                 </div>
  142.             </div>
  143.         </div>
  144.     </div>
  145.     <!-- /Edit Modal -->
  146.     <!-- Delete Modal -->
  147.     <div class="modal fade" id="delete_modal" data-bs-backdrop="static">
  148.         <div class="modal-dialog modal-dialog-centered">
  149.             <div class="modal-content">
  150.                 <div class="modal-header">
  151.                     <h5 class="modal-title">Confirm</h5>
  152.                 </div>
  153.                 <div class="modal-body">
  154.                     <div class="container-fluid">
  155.                         <form action="" id="delete-author-frm">
  156.                             <input type="hidden" name="id">
  157.                             <p>Are you sure to delete <b><span id="name"></span></b> from the list?</p>
  158.                         </form>
  159.                     </div>
  160.                 </div>
  161.                 <div class="modal-footer">
  162.                     <button type="submit" class="btn btn-danger" form="delete-author-frm">Yes</button>
  163.                     <button type="button" class="btn btn-primary" data-bs-dismiss="modal">No</button>
  164.                 </div>
  165.             </div>
  166.         </div>
  167.     </div>
  168.     <!-- /Delete Modal -->
  169. </body>
  170. </html>
Creating our JavaScript Functions

The script below contains all of the scripts and functions that initiating the processes of the CRUD Operations. It also includes the DataTables initiation. Save the file as script.js. In my case, the file is located inside the js directory inside my source code folder.

  1. var authorsTbl = '';
  2. $(function() {
  3.     // draw function [called if the database updates]
  4.     function draw_data() {
  5.         if ($.fn.dataTable.isDataTable('#authors-tbl') && authorsTbl != '') {
  6.             authorsTbl.draw(true)
  7.         } else {
  8.             load_data();
  9.         }
  10.     }
  11.  
  12.     function load_data() {
  13.         authorsTbl = $('#authors-tbl').DataTable({
  14.             dom: '<"row"B>flr<"py-2 my-2"t>ip',
  15.             "processing": true,
  16.             "serverSide": true,
  17.             "ajax": {
  18.                 url: "./get_authors.php",
  19.                 method: 'POST'
  20.             },
  21.             columns: [{
  22.                     data: 'id',
  23.                     className: 'py-0 px-1'
  24.                 },
  25.                 {
  26.                     data: 'first_name',
  27.                     className: 'py-0 px-1'
  28.                 },
  29.                 {
  30.                     data: 'last_name',
  31.                     className: 'py-0 px-1'
  32.                 },
  33.                 {
  34.                     data: 'email',
  35.                     className: 'py-0 px-1'
  36.                 },
  37.                 {
  38.                     data: 'birthdate',
  39.                     className: 'py-0 px-1'
  40.                 },
  41.                 {
  42.                     data: null,
  43.                     orderable: false,
  44.                     className: 'text-center py-0 px-1',
  45.                     render: function(data, type, row, meta) {
  46.                         console.log()
  47.                         return '<a class="me-2 btn btn-sm rounded-0 py-0 edit_data btn-primary" href="javascript:void(0)" data-id="' + (row.id) + '">Edit</a><a class="btn btn-sm rounded-0 py-0 delete_data btn-danger" href="javascript:void(0)" data-id="' + (row.id) + '">Delete</a>';
  48.                     }
  49.                 }
  50.             ],
  51.             drawCallback: function(settings) {
  52.                 $('.edit_data').click(function() {
  53.                     $.ajax({
  54.                         url: 'get_single.php',
  55.                         data: { id: $(this).attr('data-id') },
  56.                         method: 'POST',
  57.                         dataType: 'json',
  58.                         error: err => {
  59.                             alert("An error occured while fetching single data")
  60.                         },
  61.                         success: function(resp) {
  62.                             if (!!resp.status) {
  63.                                 Object.keys(resp.data).map(k => {
  64.                                     if ($('#edit_modal').find('input[name="' + k + '"]').length > 0)
  65.                                         $('#edit_modal').find('input[name="' + k + '"]').val(resp.data[k])
  66.                                 })
  67.                                 $('#edit_modal').modal('show')
  68.                             } else {
  69.                                 alert("An error occured while fetching single data")
  70.                             }
  71.                         }
  72.                     })
  73.                 })
  74.                 $('.delete_data').click(function() {
  75.                     $.ajax({
  76.                         url: 'get_single.php',
  77.                         data: { id: $(this).attr('data-id') },
  78.                         method: 'POST',
  79.                         dataType: 'json',
  80.                         error: err => {
  81.                             alert("An error occured while fetching single data")
  82.                         },
  83.                         success: function(resp) {
  84.                             if (!!resp.status) {
  85.                                 $('#delete_modal').find('input[name="id"]').val(resp.data['id'])
  86.                                 $('#delete_modal').modal('show')
  87.                             } else {
  88.                                 alert("An error occured while fetching single data")
  89.                             }
  90.                         }
  91.                     })
  92.                 })
  93.             },
  94.             buttons: [{
  95.                 text: "Add New",
  96.                 className: "btn btn-primary py-0",
  97.                 action: function(e, dt, node, config) {
  98.                     $('#add_modal').modal('show')
  99.                 }
  100.             }],
  101.             "order": [
  102.                 [1, "asc"]
  103.             ],
  104.             initComplete: function(settings) {
  105.                 $('.paginate_button').addClass('p-1')
  106.             }
  107.         });
  108.     }
  109.     //Load Data
  110.     load_data()
  111.         //Saving new Data
  112.     $('#new-author-frm').submit(function(e) {
  113.             e.preventDefault()
  114.             $('#add_modal button').attr('disabled', true)
  115.             $('#add_modal button[form="new-author-frm"]').text("saving ...")
  116.             $.ajax({
  117.                 url: 'save_data.php',
  118.                 data: $(this).serialize(),
  119.                 method: 'POST',
  120.                 dataType: "json",
  121.                 error: err => {
  122.                     alert("An error occured. Please chech the source code and try again")
  123.                 },
  124.                 success: function(resp) {
  125.                     if (!!resp.status) {
  126.                         if (resp.status == 'success') {
  127.                             var _el = $('<div>')
  128.                             _el.hide()
  129.                             _el.addClass('alert alert-primary alert_msg')
  130.                             _el.text("Data successfulle saved");
  131.                             $('#new-author-frm').get(0).reset()
  132.                             $('.modal').modal('hide')
  133.                             $('#msg').append(_el)
  134.                             _el.show('slow')
  135.                             draw_data();
  136.                             setTimeout(() => {
  137.                                 _el.hide('slow')
  138.                                     .remove()
  139.                             }, 2500)
  140.                         } else if (resp.status == 'success' && !!resp.msg) {
  141.                             var _el = $('<div>')
  142.                             _el.hide()
  143.                             _el.addClass('alert alert-danger alert_msg form-group')
  144.                             _el.text(resp.msg);
  145.                             $('#new-author-frm').append(_el)
  146.                             _el.show('slow')
  147.                         } else {
  148.                             alert("An error occured. Please chech the source code and try again")
  149.                         }
  150.                     } else {
  151.                         alert("An error occurred. Please check the source code and try again")
  152.                     }
  153.  
  154.                     $('#add_modal button').attr('disabled', false)
  155.                     $('#add_modal button[form="new-author-frm"]').text("Save")
  156.                 }
  157.             })
  158.         })
  159.         // Update Data
  160.     $('#edit-author-frm').submit(function(e) {
  161.             e.preventDefault()
  162.             $('#edit_modal button').attr('disabled', true)
  163.             $('#edit_modal button[form="edit-author-frm"]').text("saving ...")
  164.             $.ajax({
  165.                 url: 'update_data.php',
  166.                 data: $(this).serialize(),
  167.                 method: 'POST',
  168.                 dataType: "json",
  169.                 error: err => {
  170.                     alert("An error occured. Please chech the source code and try again")
  171.                 },
  172.                 success: function(resp) {
  173.                     if (!!resp.status) {
  174.                         if (resp.status == 'success') {
  175.                             var _el = $('<div>')
  176.                             _el.hide()
  177.                             _el.addClass('alert alert-primary alert_msg')
  178.                             _el.text("Data successfulle updated");
  179.                             $('#edit-author-frm').get(0).reset()
  180.                             $('.modal').modal('hide')
  181.                             $('#msg').append(_el)
  182.                             _el.show('slow')
  183.                             draw_data();
  184.                             setTimeout(() => {
  185.                                 _el.hide('slow')
  186.                                     .remove()
  187.                             }, 2500)
  188.                         } else if (resp.status == 'success' && !!resp.msg) {
  189.                             var _el = $('<div>')
  190.                             _el.hide()
  191.                             _el.addClass('alert alert-danger alert_msg form-group')
  192.                             _el.text(resp.msg);
  193.                             $('#edit-author-frm').append(_el)
  194.                             _el.show('slow')
  195.                         } else {
  196.                             alert("An error occured. Please chech the source code and try again")
  197.                         }
  198.                     } else {
  199.                         alert("An error occurred. Please check the source code and try again")
  200.                     }
  201.  
  202.                     $('#edit_modal button').attr('disabled', false)
  203.                     $('#edit_modal button[form="edit-author-frm"]').text("Save")
  204.                 }
  205.             })
  206.         })
  207.         // DELETE Data
  208.     $('#delete-author-frm').submit(function(e) {
  209.         e.preventDefault()
  210.         $('#delete_modal button').attr('disabled', true)
  211.         $('#delete_modal button[form="delete-author-frm"]').text("deleting data ...")
  212.         $.ajax({
  213.             url: 'delete_data.php',
  214.             data: $(this).serialize(),
  215.             method: 'POST',
  216.             dataType: "json",
  217.             error: err => {
  218.                 alert("An error occured. Please chech the source code and try again")
  219.             },
  220.             success: function(resp) {
  221.                 if (!!resp.status) {
  222.                     if (resp.status == 'success') {
  223.                         var _el = $('<div>')
  224.                         _el.hide()
  225.                         _el.addClass('alert alert-primary alert_msg')
  226.                         _el.text("Data successfulle updated");
  227.                         $('#delete-author-frm').get(0).reset()
  228.                         $('.modal').modal('hide')
  229.                         $('#msg').append(_el)
  230.                         _el.show('slow')
  231.                         draw_data();
  232.                         setTimeout(() => {
  233.                             _el.hide('slow')
  234.                                 .remove()
  235.                         }, 2500)
  236.                     } else if (resp.status == 'success' && !!resp.msg) {
  237.                         var _el = $('<div>')
  238.                         _el.hide()
  239.                         _el.addClass('alert alert-danger alert_msg form-group')
  240.                         _el.text(resp.msg);
  241.                         $('#delete-author-frm').append(_el)
  242.                         _el.show('slow')
  243.                     } else {
  244.                         alert("An error occured. Please chech the source code and try again")
  245.                     }
  246.                 } else {
  247.                     alert("An error occurred. Please check the source code and try again")
  248.                 }
  249.  
  250.                 $('#delete_modal button').attr('disabled', false)
  251.                 $('#delete_modal button[form="delete-author-frm"]').text("YEs")
  252.             }
  253.         })
  254.     })
  255. });

Creating Our PHP API's

The following PHP files are the codes that queries in our database. These are for fetching, adding, and deleting data in oue database. Save the files as the filename above each script.

get_authors.php

  1. <?php 
  2. require_once("./connect.php");
  3. extract($_POST);
  4.  
  5. $totalCount = $conn->query("SELECT * FROM `authors` ")->num_rows;
  6. $search_where = "";
  7. if(!empty($search)){
  8.     $search_where = " where ";
  9.     $search_where .= " first_name LIKE '%{$search['value']}%' ";
  10.     $search_where .= " OR last_name LIKE '%{$search['value']}%' ";
  11.     $search_where .= " OR email LIKE '%{$search['value']}%' ";
  12.     $search_where .= " OR date_format(birthdate,'%M %d, %Y') LIKE '%{$search['value']}%' ";
  13. }
  14. $columns_arr = array("id",
  15.                      "first_name",
  16.                      "last_name",
  17.                      "email",
  18.                      "unix_timestamp(birthdate)");
  19. $query = $conn->query("SELECT * FROM `authors` {$search_where} ORDER BY {$columns_arr[$order[0]['column']]} {$order[0]['dir']} limit {$length} offset {$start} ");
  20. $recordsFilterCount = $conn->query("SELECT * FROM `authors` {$search_where} ")->num_rows;
  21.  
  22. $recordsTotal= $totalCount;
  23. $recordsFiltered= $recordsFilterCount;
  24. $data = array();
  25. $i= 1 + $start;
  26. while($row = $query->fetch_assoc()){
  27.     $row['no'] = $i++;
  28.     $row['birthdate'] = date("F d, Y",strtotime($row['birthdate']));
  29.     $data[] = $row;
  30. }
  31. echo json_encode(array('draw'=>$draw,
  32.                        'recordsTotal'=>$recordsTotal,
  33.                        'recordsFiltered'=>$recordsFiltered,
  34.                        'data'=>$data
  35.                        )
  36. );

save_data.php

  1. <?php 
  2. require_once('connect.php');
  3. extract($_POST);
  4.  
  5. $query = $conn->query("INSERT INTO `authors` (`first_name`,`last_name`,`email`,`birthdate`) VALUE ('{$first_name}','{$last_name}','{$email}','{$birthdate}')");
  6. if($query){
  7.     $resp['status'] = 'success';
  8. }else{
  9.     $resp['status'] = 'failed';
  10.     $resp['msg'] = 'An error occured while saving the data. Error: '.$conn->error;
  11. }
  12.  
  13. echo json_encode($resp);

get_single.php

  1. <?php
  2. require_once('connect.php');
  3. extract($_POST);
  4. $query = $conn->query("SELECT * FROM `authors` where id = '{$id}'");
  5. if($query){
  6.     $resp['status'] = 'success';
  7.     $resp['data'] = $query->fetch_array();
  8. }else{
  9.     $resp['status'] = 'success';
  10.     $resp['error'] = 'An error occured while fetching the data. Error: '.$conn->error;
  11. }
  12. echo json_encode($resp);

update_data.php

  1. <?php 
  2. require_once('connect.php');
  3. extract($_POST);
  4.  
  5. $update = $conn->query("UPDATE `authors` set `first_name` = '{$first_name}', `last_name` = '{$last_name}', `email` = '{$email}',`birthdate` = '{$birthdate}' where id = '{$id}'");
  6. if($update){
  7.     $resp['status'] = 'success';
  8. }else{
  9.     $resp['status'] = 'failed';
  10.     $resp['msg'] = 'An error occured while saving the data. Error: '.$conn->error;
  11. }
  12.  
  13. echo json_encode($resp);

delete_data.php

  1. <?php 
  2. require_once('connect.php');
  3. extract($_POST);
  4.  
  5. $delete = $conn->query("DELETE FROM `authors` where id = '{$id}'");
  6. if($delete){
  7.     $resp['status'] = 'success';
  8. }else{
  9.     $resp['status'] = 'failed';
  10.     $resp['msg'] = 'An error occured while saving the data. Error: '.$conn->error;
  11. }
  12.  
  13. echo json_encode($resp);

There you go. You can now test the web application that we've created on your end. If there's an error occurred on your end. Please review the scripts above. You can also download the working source code I created. The download button is located below.

DEMO

That's the end of this tutorial. Leave a comment below for any questions. Explore more on this website for more Tutorials and Free Source Codes.

Happy Coding :)

Tags: ,

Post Top Ad