Creating An Audit Trail/Logs in a Web App using PHP/OOP and Ajax Tutorial

Submitted by oretnom23 on Thursday, October 7, 2021 - 14:28.

In this tutorial, we will tackle about how to create an Audit Trail/Audit Log feature in a web application using PHP/OOP and jQuery Ajax. This feature is useful and a common feature for software or web applications. This is usually included in the project for an application that can be managed by multiple users. With this feature in your PHP Project, you can easily track or monitor the changes or actions made by the user.

Getting Started

In this tutorial, I will be using Bootrstrap for the design of the web app. Also, download jQuery for our ajax functions. I'll be using XAMPP as my local web server and database server.

Before we continue, please make sure that your Apache and MySQL.

Creating the Database

Open your PHPMyAdmin in browser and create new database naming audit_trailing. Next, navigate your database page into the SQL Tab and copy/paste the SQL Script below. Then, click the Go Button to execute the script.

  1. 	CREATE TABLE `logs` (
  2. 	  `id` int(30) NOT NULL,
  3. 	  `user_id` int(30) NOT NULL,
  4. 	  `action_made` text NOT NULL,
  5. 	  `date_created` datetime NOT NULL DEFAULT current_timestamp()
  6. 	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  7.  
  8. 	CREATE TABLE `members` (
  9. 	  `id` int(30) NOT NULL,
  10. 	  `firstname` varchar(250) NOT NULL,
  11. 	  `lastname` varchar(250) NOT NULL,
  12. 	  `contact` varchar(50) NOT NULL,
  13. 	  `address` text NOT NULL,
  14. 	  `date_created` datetime NOT NULL DEFAULT current_timestamp()
  15. 	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  16.  
  17.  
  18. 	INSERT INTO `members` (`id`, `firstname`, `lastname`, `contact`, `address`, `date_created`) VALUES
  19. 	(1, 'Clairy', 'Blake', '09123456789', 'Sample Address', '2021-10-07 11:51:27'),
  20. 	(2, 'John', 'Smith', '09123456789', 'Sample Address', '2021-10-07 11:55:00'),
  21. 	(5, 'Mikee', 'Williams', '09112655889', '23rd St. Here City', '2021-10-07 13:17:46');
  22.  
  23. 	CREATE TABLE `users` (
  24. 	  `id` int(11) NOT NULL,
  25. 	  `name` text NOT NULL,
  26. 	  `username` text NOT NULL,
  27. 	  `password` text NOT NULL,
  28. 	  `date_created` datetime DEFAULT NULL
  29. 	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
  30.  
  31. 	INSERT INTO `users` (`id`, `name`, `username`, `password`, `date_created`) VALUES
  32. 	(1, 'Administrator', 'admin', '0192023a7bbd73250516f069df18b500', '2021-10-07 03:59:25'),
  33. 	(2, 'john Smitn', 'jsmith', '1254737c076cf867dc53d60a0364f38e', '2021-10-07 03:59:25');
  34.  
  35. 	ALTER TABLE `logs`
  36. 	  ADD PRIMARY KEY (`id`),
  37. 	  ADD KEY `user_id` (`user_id`);
  38.  
  39. 	ALTER TABLE `members`
  40. 	  ADD PRIMARY KEY (`id`);
  41.  
  42. 	ALTER TABLE `users`
  43. 	  ADD PRIMARY KEY (`id`);
  44.  
  45. 	ALTER TABLE `members`
  46. 	  MODIFY `id` int(30) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;
  47.  
  48. 	ALTER TABLE `users`
  49. 	  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
  50.  
  51.        ALTER TABLE `logs`
  52. 	  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
  53.  
  54. 	ALTER TABLE `logs`
  55. 	  ADD CONSTRAINT `logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;

Creating Our Database Connection

Create a new PHP File and copy/paste the script below. The script contains our PHP Class for database connection. Save the file as DBConnection.php.

  1. 	<?php
  2. 	if(!defined('host')) define('host',"localhost");
  3. 	if(!defined('username')) define('username',"root");
  4. 	if(!defined('password')) define('password',"");
  5. 	if(!defined('db_tbl')) define('db_tbl',"audit_trailing");
  6.  
  7. 	Class DBConnection{
  8. 	    public $conn;
  9. 	    function __construct(){
  10. 	        $this->conn = new mysqli(host,username,password,db_tbl);
  11. 	        if(!$this->conn){
  12. 	            die("Database Connection Error. ".$this->conn->error);
  13. 	        }
  14. 	    }
  15. 	    function __destruct(){
  16. 	         $this->conn->close();
  17. 	    }
  18. 	}
  19.  
  20. 	$db = new DBConnection();
  21. 	$conn= $db->conn;

Creating the Interface

The below codes are the scripts for our pages interfaces. Copy/Paste the scripts below and save the files according to the file name stated before the scripts.

login.php

This file contains the html scripts for our login page interface. The Ajax Script for the form submission is also included in this file.

  1. 	<?php
  2. 	session_start();
  3. 	if(isset($_SESSION['id']) && $_SESSION['id'] > 0){
  4. 	    header("Location:./");
  5. 	    exit;
  6. 	}
  7. 	require_once('./DBConnection.php');
  8. 	?>
  9. 	<!DOCTYPE html>
  10. 	<html lang="en">
  11. 	<head>
  12. 	    <meta charset="UTF-8">
  13. 	    <meta http-equiv="X-UA-Compatible" content="IE=edge">
  14. 	    <meta name="viewport" content="width=device-width, initial-scale=1.0">
  15. 	    <title>LOGIN | Audit Trailing</title>
  16. 	    <link rel="stylesheet" href="./css/bootstrap.min.css">
  17. 	    <script src="./js/jquery-3.6.0.min.js"></script>
  18. 	    <script src="./js/popper.min.js"></script>
  19. 	    <script src="./js/bootstrap.min.js"></script>
  20. 	    <script src="./js/script.js"></script>
  21. 	    <style>
  22. 	        html, body{
  23. 	            height:100%;
  24. 	        }
  25. 	    </style>
  26. 	</head>
  27. 	<body class="bg-dark bg-gradient">
  28. 	   <div class="h-100 d-flex jsutify-content-center align-items-center">
  29. 	       <div class='w-100'>
  30. 	        <h3 class="py-5 text-center text-light">Audit Trailing/Audit Log</h3>
  31. 	        <div class="card my-3 col-md-4 offset-md-4">
  32. 	            <div class="card-body">
  33. 	                <form action="" id="login-form">
  34. 	                    <center><small>Please Enter your system credential.</small></center>
  35. 	                    <div class="form-group">
  36. 	                        <label for="username" class="control-label">Username</label>
  37. 	                        <input type="text" id="username" autofocus name="username" class="form-control form-control-sm rounded-0" required>
  38. 	                    </div>
  39. 	                    <div class="form-group">
  40. 	                        <label for="password" class="control-label">Password</label>
  41. 	                        <input type="password" id="password" autofocus name="password" class="form-control form-control-sm rounded-0" required>
  42. 	                    </div>
  43. 	                    <div class="form-group d-flex w-100 justify-content-end">
  44. 	                        <button class="btn btn-sm btn-primary rounded-0 my-1">Login</button>
  45. 	                    </div>
  46. 	                </form>
  47. 	            </div>
  48. 	        </div>
  49. 	       </div>
  50. 	   </div>
  51. 	</body>
  52. 	<script>
  53. 	    $(function(){
  54. 	        $('#login-form').submit(function(e){
  55. 	            e.preventDefault();
  56. 	            $('.pop_msg').remove()
  57. 	            var _this = $(this)
  58. 	            var _el = $('<div>')
  59. 	                _el.addClass('pop_msg')
  60. 	            _this.find('button').attr('disabled',true)
  61. 	            _this.find('button[type="submit"]').text('Loging in...')
  62. 	            $.ajax({
  63. 	                url:'./Actions.php?a=login',
  64. 	                method:'POST',
  65. 	                data:$(this).serialize(),
  66. 	                dataType:'JSON',
  67. 	                error:err=>{
  68. 	                    console.log(err)
  69. 	                    _el.addClass('alert alert-danger')
  70. 	                    _el.text("An error occurred.")
  71. 	                    _this.prepend(_el)
  72. 	                    _el.show('slow')
  73. 	                    _this.find('button').attr('disabled',false)
  74. 	                    _this.find('button[type="submit"]').text('Save')
  75. 	                },
  76. 	                success:function(resp){
  77. 	                    if(resp.status == 'success'){
  78. 	                        _el.addClass('alert alert-success')
  79. 	                        setTimeout(() => {
  80. 	                            location.replace('./');
  81. 	                        }, 2000);
  82. 	                    }else{
  83. 	                        _el.addClass('alert alert-danger')
  84. 	                    }
  85. 	                    _el.text(resp.msg)
  86.  
  87. 	                    _el.hide()
  88. 	                    _this.prepend(_el)
  89. 	                    _el.show('slow')
  90. 	                    _this.find('button').attr('disabled',false)
  91. 	                    _this.find('button[type="submit"]').text('Save')
  92. 	                }
  93. 	            })
  94. 	        })
  95. 	    })
  96. 	</script>
  97. 	</html>
index.php

The code below is the script for our page template. It contains the top bar navigation, dynamic modal, and confirmation scripts.

  1. 	<?php
  2. 	session_start();
  3. 	if(!isset($_SESSION['id']) || (isset($_SESSION['id']) && $_SESSION['id'] <= 0)){
  4. 	    header("Location:./login.php");
  5. 	    exit;
  6. 	}
  7. 	require_once('./DBConnection.php');
  8. 	$page = isset($_GET['page']) ? $_GET['page'] : 'home';
  9. 	?>
  10. 	<!DOCTYPE html>
  11. 	<html lang="en">
  12. 	<head>
  13. 	    <meta charset="UTF-8">
  14. 	    <meta http-equiv="X-UA-Compatible" content="IE=edge">
  15. 	    <meta name="viewport" content="width=device-width, initial-scale=1.0">
  16. 	    <title>Audit Trailing</title>
  17. 	    <link rel="stylesheet" href="./fontawesome/css/all.min.css">
  18. 	    <link rel="stylesheet" href="./css/bootstrap.min.css">
  19. 	    <script src="./js/jquery-3.6.0.min.js"></script>
  20. 	    <script src="./js/popper.min.js"></script>
  21. 	    <script src="./js/bootstrap.min.js"></script>
  22. 	    <link rel="stylesheet" href="./DataTables/datatables.min.css">
  23. 	    <script src="./DataTables/datatables.min.js"></script>
  24. 	    <script src="./fontawesome/js/all.min.js"></script>
  25. 	    <script src="./js/script.js"></script>
  26. 	    <style>
  27. 	    </style>
  28. 	</head>
  29. 	<body class="bg-light">
  30. 	    <main>
  31. 	    <nav class="navbar navbar-expand-lg navbar-dark bg-primary bg-gradient" id="topNavBar">
  32. 	        <div class="container">
  33. 	            <a class="navbar-brand" href="./">
  34. 	                Audit Trailing
  35. 	            </a>
  36. 	            <button class="navbar-toggler" type="button" data-bs-toggle="collapse" data-bs-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
  37. 	            <span class="navbar-toggler-icon"></span>
  38. 	            </button>
  39. 	            <div class="collapse navbar-collapse" id="navbarNav">
  40. 	                <ul class="navbar-nav">
  41. 	                    <li class="nav-item">
  42. 	                        <a class="nav-link <?php echo ($page == 'home')? 'active' : '' ?>" aria-current="page" href="./"><i class="fa fa-home"></i> Home</a>
  43. 	                    </li>
  44. 	                    <li class="nav-item">
  45. 	                        <a class="nav-link <?php echo ($page == 'logs')? 'active' : '' ?>" aria-current="page" href="./?page=logs"><i class="fa fa-th-list"></i> Audit Logs</a>
  46. 	                    </li>
  47. 	                </ul>
  48. 	            </div>
  49. 	            <div>
  50. 	            <?php if(isset($_SESSION['id'])): ?>
  51. 	                <div class="dropdown">
  52. 	                    <button class="btn btn-secondary dropdown-toggle bg-transparent  text-light border-0" type="button" id="dropdownMenuButton1" data-bs-toggle="dropdown" aria-expanded="false">
  53. 	                        Hello <?php echo $_SESSION['name'] ?>
  54. 	                    </button>
  55. 	                    <ul class="dropdown-menu" aria-labelledby="dropdownMenuButton1">
  56. 	                        <li><a class="dropdown-item" href="././Actions.php?a=logout">Logout</a></li>
  57. 	                    </ul>
  58. 	                </div>
  59. 	            <?php endif; ?>
  60. 	            </div>
  61. 	        </div>
  62. 	    </nav>
  63. 	    <div class="container py-3" id="page-container">
  64. 	        <?php 
  65. 	            if(isset($_SESSION['flashdata'])):
  66. 	        ?>
  67. 	        <div class="dynamic_alert alert alert-<?php echo $_SESSION['flashdata']['type'] ?>">
  68. 	        <div class="float-end"><a href="javascript:void(0)" class="text-dark text-decoration-none" onclick="$(this).closest('.dynamic_alert').hide('slow').remove()">x</a></div>
  69. 	            <?php echo $_SESSION['flashdata']['msg'] ?>
  70. 	        </div>
  71. 	        <?php unset($_SESSION['flashdata']) ?>
  72. 	        <?php endif; ?>
  73. 	        <?php
  74. 	            include $page.'.php';
  75. 	        ?>
  76. 	    </div>
  77. 	    </main>
  78. 	    <div class="modal fade" id="uni_modal" role='dialog' data-bs-backdrop="static" data-bs-keyboard="true">
  79. 	        <div class="modal-dialog modal-md modal-dialog-centered" role="document">
  80. 	        <div class="modal-content">
  81. 	            <div class="modal-header py-2">
  82. 	            <h5 class="modal-title"></h5>
  83. 	        </div>
  84. 	        <div class="modal-body">
  85. 	        </div>
  86. 	        <div class="modal-footer py-1">
  87. 	            <button type="button" class="btn btn-sm rounded-0 btn-primary" id='submit' onclick="$('#uni_modal form').submit()">Save</button>
  88. 	            <button type="button" class="btn btn-sm rounded-0 btn-secondary" data-bs-dismiss="modal">Close</button>
  89. 	        </div>
  90. 	        </div>
  91. 	        </div>
  92. 	    </div>
  93. 	    <div class="modal fade" id="uni_modal_secondary" role='dialog' data-bs-backdrop="static" data-bs-keyboard="true">
  94. 	        <div class="modal-dialog modal-md modal-dialog-centered" role="document">
  95. 	        <div class="modal-content">
  96. 	            <div class="modal-header py-2">
  97. 	            <h5 class="modal-title"></h5>
  98. 	        </div>
  99. 	        <div class="modal-body">
  100. 	        </div>
  101. 	        <div class="modal-footer py-1">
  102. 	            <button type="button" class="btn btn-sm rounded-0 btn-primary" id='submit' onclick="$('#uni_modal_secondary form').submit()">Save</button>
  103. 	            <button type="button" class="btn btn-sm rounded-0 btn-secondary" data-bs-dismiss="modal">Close</button>
  104. 	        </div>
  105. 	        </div>
  106. 	        </div>
  107. 	    </div>
  108. 	    <div class="modal fade" id="confirm_modal" role='dialog'>
  109. 	        <div class="modal-dialog modal-md modal-dialog-centered" role="document">
  110. 	        <div class="modal-content rounded-0">
  111. 	            <div class="modal-header py-2">
  112. 	            <h5 class="modal-title">Confirmation</h5>
  113. 	        </div>
  114. 	        <div class="modal-body">
  115. 	            <div id="delete_content"></div>
  116. 	        </div>
  117. 	        <div class="modal-footer py-1">
  118. 	            <button type="button" class="btn btn-primary btn-sm rounded-0" id='confirm' onclick="">Continue</button>
  119. 	            <button type="button" class="btn btn-secondary btn-sm rounded-0" data-bs-dismiss="modal">Close</button>
  120. 	        </div>
  121. 	        </div>
  122. 	        </div>
  123. 	    </div>
  124. 	</body>
  125. 	</html>
home.php

This project is the script for our home page content. It contains our dummy data table.

  1. 	<div class="container py-5">
  2. 	    <h3><b>Members List</b></h3>
  3. 	    <div class="card">
  4. 	        <div class="card-body">
  5. 	            <div class="col-12 my-2 d-flex justify-content-end">
  6. 	                <button class="btn btn-sm btn-primary rounded-0" id="add_new"><i class="fa fa-plus"></i> Add New</button>
  7. 	            </div>
  8. 	            <table class="table table-bordered table-striped table-hover">
  9. 	                <thead>
  10. 	                    <tr>
  11. 	                        <th class="py-1 px-2">#</th>
  12. 	                        <th class="py-1 px-2">First Name</th>
  13. 	                        <th class="py-1 px-2">Last Name</th>
  14. 	                        <th class="py-1 px-2">Contact #</th>
  15. 	                        <th class="py-1 px-2">Address</th>
  16. 	                        <th class="py-1 px-2">Action</th>
  17. 	                    </tr>
  18. 	                </thead>
  19. 	                <tbody>
  20. 	                    <?php 
  21. 	                    $qry = $conn->query("SELECT * FROM members order by firstname asc, lastname asc");
  22. 	                    $i = 1;
  23. 	                    while($row=$qry->fetch_assoc()):
  24. 	                    ?>
  25. 	                    <tr>
  26. 	                        <td class="py-1 px-2"><?php echo $i++ ?></td>
  27. 	                        <td class="py-1 px-2"><?php echo $row['firstname'] ?></td>
  28. 	                        <td class="py-1 px-2"><?php echo $row['firstname'] ?></td>
  29. 	                        <td class="py-1 px-2"><?php echo $row['contact'] ?></td>
  30. 	                        <td class="py-1 px-2"><?php echo $row['address'] ?></td>
  31. 	                        <td class="py-1 px-2 text-center">
  32. 	                            <div class="btn-group" role="group">
  33. 	                                <button id="btnGroupDrop1" type="button" class="btn btn-primary dropdown-toggle btn-sm rounded-0 py-0" data-bs-toggle='dropdown' aria-expanded="false" >
  34. 	                                Action
  35. 	                                </button>
  36. 	                                <ul class="dropdown-menu" aria-labelledby="btnGroupDrop1">
  37. 	                                <li><a class="dropdown-item view_data" href="javascript:void(0)" data-id="<?php echo $row['id'] ?>">View</a></li>
  38. 	                                <li><a class="dropdown-item edit_data" href="javascript:void(0)" data-id="<?php echo $row['id'] ?>">Edit</a></li>
  39. 	                                <li><a class="dropdown-item delete_data" href="javascript:void(0)" data-id="<?php echo $row['id'] ?>" data-name="<?php echo "[id={$row['id']}] ".$row['firstname'].' '.$row['lastname'] ?>">Delete</a></li>
  40. 	                                </ul>
  41. 	                            </div>
  42. 	                        </td>
  43. 	                    </tr>
  44. 	                    <?php endwhile; ?>
  45. 	                    <?php if($qry->num_rows <=0): ?>
  46. 	                        <tr>
  47. 	                            <th class="tex-center" colspan="6">No data to display.</th>
  48. 	                        </tr>
  49. 	                    <?php endif; ?>
  50. 	                </tbody>
  51. 	            </table>
  52. 	        </div>
  53. 	    </div>
  54. 	</div>
  55.  
  56. 	<script>
  57. 	    $(function(){
  58. 	        $('#add_new').click(function(){
  59. 	            uni_modal('New Member',"manage_member.php");
  60. 	        })
  61. 	        $('.edit_data').click(function(){
  62. 	            uni_modal('Edit Member Details',"manage_member.php?id="+$(this).attr('data-id'));
  63. 	        })
  64. 	        $('.delete_data').click(function(){
  65. 	            _conf("Ar you sure to delete <b>"+$(this).attr('data-name')+"</b> from member list?","delete_data",[$(this).attr('data-id')])
  66. 	        })
  67. 	        $('.view_data').click(function(){
  68. 	            uni_modal('View Member Details',"view_member.php?id="+$(this).attr('data-id'));
  69. 	        })
  70. 	    })
  71. 	    function delete_data($id){
  72. 	        $('#confirm_modal button').attr('disabled',true)
  73. 	        $.ajax({
  74. 	            url:'./Actions.php?a=delete_member',
  75. 	            method:'POST',
  76. 	            data:{id:$id},
  77. 	            dataType:'JSON',
  78. 	            error:err=>{
  79. 	                console.log(err)
  80. 	                alert("An error occurred.")
  81. 	                $('#confirm_modal button').attr('disabled',false)
  82. 	            },
  83. 	            success:function(resp){
  84. 	                if(resp.status == 'success'){
  85. 	                    location.reload()
  86. 	                }else{
  87. 	                    alert("An error occurred.")
  88. 	                    $('#confirm_modal button').attr('disabled',false)
  89. 	                }
  90. 	            }
  91. 	        })
  92. 	    }
  93. 	</script>
logs.php

The code below is the script which displays all the logs from database.

  1. 	<div class="container py-5">
  2. 	    <div class="d-flex w-100">
  3. 	        <h3 class="col-auto flex-grow-1"><b>Audit Trail</b></h3>
  4. 	        <button class="btn btn-sm btn-primary rounded-0" type="button" onclick="location.reload()"><i class="fa fa-retweet"></i> Refresh List</button>
  5. 	    </div>
  6. 	    <hr>
  7. 	    <div class="card">
  8. 	        <div class="card-body">
  9. 	            <table class="table table-bordered table-striped table-hover">
  10. 	                <thead>
  11. 	                    <tr>
  12. 	                        <th class="py-1 px-2">#</th>
  13. 	                        <th class="py-1 px-2">DateTime</th>
  14. 	                        <th class="py-1 px-2">Username</th>
  15. 	                        <th class="py-1 px-2">Action Made</th>
  16. 	                    </tr>
  17. 	                </thead>
  18. 	                <tbody>
  19. 	                    <?php 
  20. 	                    $qry = $conn->query("SELECT l.*,u.username FROM `logs` l inner join users u on l.user_id = u.id order by  unix_timestamp(l.`date_created`) asc");
  21. 	                    $i = 1;
  22. 	                    while($row=$qry->fetch_assoc()):
  23. 	                    ?>
  24. 	                    <tr>
  25. 	                        <td class="py-1 px-2"><?php echo $i++ ?></td>
  26. 	                        <td class="py-1 px-2"><?php echo date("M d, Y H:i",strtotime($row['date_created'])) ?></td>
  27. 	                        <td class="py-1 px-2"><?php echo $row['username'] ?></td>
  28. 	                        <td class="py-1 px-2"><?php echo $row['action_made'] ?></td>
  29. 	                    </tr>
  30. 	                    <?php endwhile; ?>
  31. 	                    <?php if($qry->num_rows <=0): ?>
  32. 	                        <tr>
  33. 	                            <th class="tex-center"  colspan="4">No data to display.</th>
  34. 	                        </tr>
  35. 	                    <?php endif; ?>
  36. 	                </tbody>
  37. 	            </table>
  38. 	        </div>
  39. 	    </div>
  40. 	</div>
manage_member.php

This is the script for our member form which will be used in both adding and updating member's details. This will be shown in a modal.

  1. 	<?php 
  2. 	require_once('./DBConnection.php');
  3. 	if(isset($_GET['id'])){
  4. 	    $qry= $conn->query("SELECT * FROM members where id = '{$_GET['id']}'");
  5. 	    if($qry->num_rows > 0){
  6. 	        foreach($qry->fetch_array() as $k => $v){
  7. 	            if(!is_numeric($k))
  8. 	            $$k=$v;
  9. 	        }
  10. 	    }
  11. 	}
  12. 	?>
  13. 	<div class="container-fluid">
  14. 	    <form action="" id="member-form">
  15. 	        <input type="hidden" name="id" value="<?php echo isset($id) ? $id : '' ?>">
  16. 	        <div class="form-group">
  17. 	            <label for="firstname" class="control-label">First Name</label>
  18. 	            <input type="text" name="firstname" class="form-control form-control-sm rounded-0" value="<?php echo isset($firstname) ? $firstname : "" ?>" required>
  19. 	        </div>
  20. 	        <div class="form-group">
  21. 	            <label for="lastname" class="control-label">Last Name</label>
  22. 	            <input type="text" name="lastname" class="form-control form-control-sm rounded-0" value="<?php echo isset($lastname) ? $lastname : "" ?>" required>
  23. 	        </div>
  24. 	        <div class="form-group">
  25. 	            <label for="contact" class="control-label">Contact #</label>
  26. 	            <input type="text" name="contact" class="form-control form-control-sm rounded-0" value="<?php echo isset($contact) ? $contact : "" ?>" required>
  27. 	        </div>
  28. 	        <div class="form-group">
  29. 	            <label for="address" class="control-label">Address</label>
  30. 	            <textarea rows="3" name="address" class="form-control form-control-sm rounded-0" required><?php echo isset($address) ? $address : "" ?></textarea>
  31. 	        </div>
  32. 	    </form>
  33. 	</div>
  34. 	<script>
  35. 	$(function(){
  36. 	    $('#member-form').submit(function(e){
  37. 	        e.preventDefault();
  38. 	        $('.pop_msg').remove()
  39. 	        var _this = $(this)
  40. 	        var _el = $('<div>')
  41. 	            _el.addClass('pop_msg')
  42. 	        $('#uni_modal button').attr('disabled',true)
  43. 	        $('#uni_modal button[type="submit"]').text('submitting form...')
  44. 	        $.ajax({
  45. 	            url:'./Actions.php?a=save_member',
  46. 	            data: new FormData($(this)[0]),
  47. 	            cache: false,
  48. 	            contentType: false,
  49. 	            processData: false,
  50. 	            method: 'POST',
  51. 	            type: 'POST',
  52. 	            dataType: 'json',
  53. 	            error:err=>{
  54. 	                console.log(err)
  55. 	                _el.addClass('alert alert-danger')
  56. 	                _el.text("An error occurred.")
  57. 	                _this.prepend(_el)
  58. 	                _el.show('slow')
  59. 	                    $('#uni_modal button').attr('disabled',false)
  60. 	                    $('#uni_modal button[type="submit"]').text('Save')
  61. 	            },
  62. 	            success:function(resp){
  63. 	                if(resp.status == 'success'){
  64. 	                    _el.addClass('alert alert-success')
  65. 	                    $('#uni_modal').on('hide.bs.modal',function(){
  66. 	                        location.reload()
  67. 	                    })
  68. 	                    if("<?php echo isset($enrollee_id) ?>" != 1)
  69. 	                    _this.get(0).reset();
  70. 	                }else{
  71. 	                    _el.addClass('alert alert-danger')
  72. 	                }
  73. 	                _el.text(resp.msg)
  74.  
  75. 	                _el.hide()
  76. 	                _this.prepend(_el)
  77. 	                _el.show('slow')
  78. 	                    $('#uni_modal button').attr('disabled',false)
  79. 	                    $('#uni_modal button[type="submit"]').text('Save')
  80. 	            }
  81. 	        })
  82. 	    })
  83. 	})
  84. 	</script>
view_member.php

This is the script which displays the details of selected member. This will be shown in a modal.

  1. 	<?php 
  2. 	require_once('./DBConnection.php');
  3. 	$qry= $conn->query("SELECT * FROM members where id = '{$_GET['id']}'");
  4. 	if($qry->num_rows > 0){
  5. 	    foreach($qry->fetch_array() as $k => $v){
  6. 	        if(!is_numeric($k))
  7. 	        $$k=$v;
  8. 	    }
  9. 	}
  10. 	?>
  11. 	<style>
  12. 	    #uni_modal .modal-footer{
  13. 	        display:none !important;
  14. 	    }
  15. 	</style>
  16. 	<div class="container-fluid">
  17. 	    <dl>
  18. 	        <dt class="fs-6 text-muted">ID</dt>
  19. 	        <dd class="fs-5 fw-bold"><?php echo $id ?></dd>
  20. 	        <dt class="fs-6 text-muted">Name</dt>
  21. 	        <dd class="fs-5 fw-bold"><?php echo $firstname." " .$lastname ?></dd>
  22. 	        <dt class="fs-6 text-muted">Contact #</dt>
  23. 	        <dd class="fs-5 fw-bold"><?php echo $contact ?></dd>
  24. 	        <dt class="fs-6 text-muted">Address</dt>
  25. 	        <dd class="fs-5 fw-bold"><?php echo $address ?></dd>
  26. 	    </dl>
  27. 	    <div class="col-12">
  28. 	        <div class="w-100 d-flex justify-content-end">
  29. 	            <button class="btn btn-sm btn-dark rounded-0" type="button" data-bs-dismiss="modal">Close</button>
  30. 	        </div>
  31. 	    </div>
  32. 	</div>
  33.  
  34. 	<script>
  35. 	$(function(){
  36. 	    $.ajax({
  37. 	        url:'./Actions.php?a=save_log',
  38. 	        method:'POST',
  39. 	        data:{action_made:" viewed the data of <?php echo "[id={$id}]". $firstname.' '.$lastname ?>"},
  40. 	        dataType:'json',
  41. 	        error:err=>{
  42. 	            console.log(err)
  43. 	        },
  44. 	        succuess:function(resp){
  45. 	            if(resp == 1){
  46. 	                console.log("Log successfully saved")
  47. 	            }else{
  48. 	                console.log("Log has failed to save.")
  49. 	            }
  50. 	        }
  51. 	    })
  52. 	})
  53. 	</script>

Creating Our Custom Javascript Functions

The code below contains the scripts for our custome js/jquery functions of our dynamic modal and confirmation modal. In my case, I saved this file inside the js directory naming script.js. This file is included in the index.php file.

  1. 	window.uni_modal = function($title = '', $url = '', $size = "") {
  2. 	    $.ajax({
  3. 	        url: $url,
  4. 	        error: err => {
  5. 	            console.log()
  6. 	            alert("An error occured")
  7. 	        },
  8. 	        success: function(resp) {
  9. 	            if (resp) {
  10. 	                $('#uni_modal .modal-title').html($title)
  11. 	                $('#uni_modal .modal-body').html(resp)
  12. 	                $('#uni_modal .modal-dialog').removeClass('large')
  13. 	                $('#uni_modal .modal-dialog').removeClass('mid-large')
  14. 	                $('#uni_modal .modal-dialog').removeClass('modal-md')
  15. 	                if ($size == '') {
  16. 	                    $('#uni_modal .modal-dialog').addClass('modal-md')
  17. 	                } else {
  18. 	                    $('#uni_modal .modal-dialog').addClass($size)
  19. 	                }
  20. 	                $('#uni_modal').modal({
  21. 	                    backdrop: 'static',
  22. 	                    keyboard: true,
  23. 	                    focus: true
  24. 	                })
  25. 	                $('#uni_modal').modal('show')
  26. 	            }
  27. 	        }
  28. 	    })
  29. 	}
  30. 	window.uni_modal_secondary = function($title = '', $url = '', $size = "") {
  31. 	    $.ajax({
  32. 	        url: $url,
  33. 	        error: err => {
  34. 	            console.log()
  35. 	            alert("An error occured")
  36. 	        },
  37. 	        success: function(resp) {
  38. 	            if (resp) {
  39. 	                $('#uni_modal_secondary .modal-title').html($title)
  40. 	                $('#uni_modal_secondary .modal-body').html(resp)
  41. 	                $('#uni_modal_secondary .modal-dialog').removeClass('large')
  42. 	                $('#uni_modal_secondary .modal-dialog').removeClass('mid-large')
  43. 	                $('#uni_modal_secondary .modal-dialog').removeClass('modal-md')
  44. 	                if ($size == '') {
  45. 	                    $('#uni_modal_secondary .modal-dialog').addClass('modal-md')
  46. 	                } else {
  47. 	                    $('#uni_modal_secondary .modal-dialog').addClass($size)
  48. 	                }
  49. 	                $('#uni_modal_secondary').modal({
  50. 	                    backdrop: 'static',
  51. 	                    keyboard: true,
  52. 	                    focus: true
  53. 	                })
  54. 	                $('#uni_modal_secondary').modal('show')
  55. 	            }
  56. 	        }
  57. 	    })
  58. 	}
  59. 	window._conf = function($msg = '', $func = '', $params = []) {
  60. 	    $('#confirm_modal #confirm').attr('onclick', $func + "(" + $params.join(',') + ")")
  61. 	    $('#confirm_modal .modal-body').html($msg)
  62. 	    $('#confirm_modal').modal('show')
  63. 	}

Creating the Main PHP Class

Lastly, copy/paste the source code below andd save it as Actions.php. This file is our main PHP Class which contains the action queries and the audit trail/log saving function.

  1. 	<?php 
  2. 	session_start();
  3. 	require_once('DBConnection.php');
  4.  
  5. 	Class Actions extends DBConnection{
  6. 	    function __construct(){
  7. 	        parent::__construct();
  8. 	    }
  9. 	    function __destruct(){
  10. 	        parent::__destruct();
  11. 	    }
  12. 	    function save_log($data=array()){
  13. 	        // Data array paramateres
  14. 	            // user_id = user unique id
  15. 	            // action_made = action made by the user
  16.  
  17. 	        if(count($data) > 0){
  18. 	            extract($data);
  19. 	            $sql = "INSERT INTO `logs` (`user_id`,`action_made`) VALUES ('{$user_id}','{$action_made}')";
  20. 	            $save = $this->conn->query($sql);
  21. 	            if(!$save){
  22. 	                die($sql." <br> ERROR:".$this->conn->error);
  23. 	            }
  24. 	        }
  25. 	        return true;
  26. 	    }
  27. 	    function login(){
  28. 	        extract($_POST);
  29. 	        $sql = "SELECT * FROM users where username = '{$username}' and `password` = '".md5($password)."' ";
  30. 	        @$qry = $this->conn->query($sql)->fetch_array();
  31. 	        if(!$qry){
  32. 	            $resp['status'] = "failed";
  33. 	            $resp['msg'] = "Invalid username or password.";
  34. 	        }else{
  35. 	            $resp['status'] = "success";
  36. 	            $resp['msg'] = "Login successfully.";
  37. 	            foreach($qry as $k => $v){
  38. 	                if(!is_numeric($k))
  39. 	                $_SESSION[$k] = $v;
  40. 	            }
  41. 	            $log['user_id'] = $qry['id'];
  42. 	            $log['action_made'] = "Logged in the system.";
  43. 	            // audit log
  44. 	            $this->save_log($log);
  45. 	        }
  46. 	        return json_encode($resp);
  47. 	    }
  48. 	    function logout(){
  49. 	        $log['user_id'] = $_SESSION['id'];
  50. 	        $log['action_made'] = "Logged out.";
  51. 	        session_destroy();
  52. 	        // audit log
  53. 	        $this->save_log($log);
  54. 	        header("location:./");
  55. 	    }
  56. 	    function save_member(){
  57. 	        extract($_POST);
  58. 	        $data = "";
  59. 	        foreach($_POST as $k => $v){
  60. 	            if(!in_array($k,array('id'))){
  61. 	                if(!empty($data)) $data .=", ";
  62. 	                $data .= " `{$k}` = '{$v}' ";
  63. 	            }
  64. 	        }
  65. 	        if(empty($id)){
  66. 	            $sql = "INSERT INTO `members` set {$data}";
  67. 	        }else{
  68. 	            $sql = "UPDATE `members` set {$data} where id = '{$id}'";
  69. 	        }
  70. 	        $save = $this->conn->query($sql);
  71. 	        if($save){
  72. 	            $resp['status'] = 'success';
  73. 	            $log['user_id'] = $_SESSION['id'];
  74. 	            $member_id = empty($id) ? $this->conn->insert_id : $id ;
  75. 	            if(empty($id)){
  76. 	                $resp['msg'] = "New Member successfully added.";
  77. 	                $log['action_made'] = " added [id={$member_id}] {$firstname} {$lastname} into the member list.";
  78. 	            }else{
  79. 	                $resp['msg'] = "Member successfully updated.";
  80. 	                $log['action_made'] = " updated the details of [id={$member_id}] member.";
  81. 	            }
  82. 	            // audit log
  83. 	            $this->save_log($log);
  84. 	        }else{
  85. 	            $resp['status'] = 'failed';
  86. 	            $resp['msg'] = "Error saving member details. Error: ".$this->conn->error;
  87. 	            $resp['sql'] = $sql;
  88. 	        }
  89. 	        return json_encode($resp);
  90. 	    }
  91. 	    function delete_member(){
  92. 	        extract($_POST);
  93. 	        $mem = $this->conn->query("SELECT * FROM members where id = '{$id}'")->fetch_array();
  94. 	        $delete = $this->conn->query("DELETE FROM members where id = '{$id}'");
  95. 	        if($delete){
  96. 	            $resp['status'] = 'success';
  97. 	            $resp['msg'] = 'Member successfully deleted.';
  98. 	            $log['user_id'] = $_SESSION['id'];
  99. 	            $log['action_made'] = " deleted [id={$mem['id']}] {$mem['firstname']} {$mem['lastname']} from member list.";
  100. 	            $_SESSION['flashdata']['type'] = 'success';
  101. 	            $_SESSION['flashdata']['msg'] = $resp['msg'];
  102.  
  103. 	            // audit log
  104. 	            $this->save_log($log);
  105. 	        }else{
  106. 	            $resp['status']='failed';
  107. 	            $resp['msg']='Failed to delete member.';
  108. 	            $resp['error']=$this->conn->error;
  109. 	        }
  110. 	        return json_encode($resp);
  111. 	    }
  112. 	}
  113. 	$a = isset($_GET['a']) ?$_GET['a'] : '';
  114. 	$action = new Actions();
  115. 	switch($a){
  116. 	    case 'login':
  117. 	        echo $action->login();
  118. 	    break;
  119. 	    case 'logout':
  120. 	        echo $action->logout();
  121. 	    break;
  122. 	    case 'save_member':
  123. 	        echo $action->save_member();
  124. 	    break;
  125. 	    case 'delete_member':
  126. 	        echo $action->delete_member();
  127. 	    break;
  128. 	    case 'save_log':
  129. 	        $log['user_id'] = $_SESSION['id'];
  130. 	        $log['action_made'] = $_POST['action_made'];
  131. 	        echo $action->save_log($log);
  132. 	    break;
  133. 	    default:
  134. 	    // default action here
  135. 	    echo "No Action given";
  136. 	    break;
  137. 	}

DEMO VIDEO

Sample Access

Username: admin/jsmith
Password: admin123/jsmith123

That's it! You can now test the simple web application that has an Audit Trail/Logs Feature that we created on your end. If you have encountered any errors, please review the scripts above. You can also download the working source code I created for this tutorial. Download button is located below this content.

I hope this PHP/OOP and jQuery Ajax Tutorial will help you with what you are looking for and you'll find this userful for your future PHP Projects.

Happy Coding :)

Tags

Comments

Submitted byORETNOMGFAN (not verified)on Thu, 06/09/2022 - 16:36

How to show search table on the data tables?

Add new comment

About text formats