Building a User Logs Table with Navigation and Search Using Bootstrap 5 and MySQL
Incorporating a database into our user logs table allows for more dynamic and scalable data management. In this tutorial, we will extend the previous example by fetching user logs from a MySQL database.
Prerequisites
Before we start, ensure you have the following:
Basic understanding of HTML, CSS, JavaScript, and PHP.
Bootstrap 5 library (can be included via CDN).
MySQL installed and running.
PHP installed and running.
Setting Up the MySQL Database
First, let's set up a MySQL database and create a table for user logs.
Creating the Database and Table
Open your MySQL command line or a MySQL client like phpMyAdmin.
Create a new database and a table for user logs:
CREATE DATABASE user_logs_db;
USE user_logs_db;
CREATE TABLE user_logs (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id VARCHAR(255) NOT NULL,
action VARCHAR(255) NOT NULL,
timestamp DATETIME NOT NULL
);
Insert some sample data into the table:
INSERT INTO user_logs (user_id, action, timestamp) VALUES
('user123', 'Login', '2024-06-14 10:00:00'),
('user456', 'Logout', '2024-06-14 10:05:00'),
('user789', 'Login', '2024-06-14 10:15:00');
Setting Up the Server-Side Code
We'll use PHP to fetch data from the MySQL database and serve it to our front-end. Create a new file named fetch_logs.php to handle data fetching.
PHP Script (fetch_logs.php)
<?php
$servername = "localhost";
$username = "root";
$password = "root"; // your MySQL password
$dbname = "user_logs_db";
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
$search = $_GET['search'] ?? '';
$page = $_GET['page'] ?? 1;
$logsPerPage = 10;
$offset = ($page - 1) * $logsPerPage;
$sqlCount = "SELECT COUNT(*) as count FROM user_logs WHERE user_id LIKE ? OR action LIKE ? OR timestamp LIKE ?";
$stmtCount = $conn->prepare($sqlCount);
$searchParam = "%$search%";
$stmtCount->bind_param("sss", $searchParam, $searchParam, $searchParam);
$stmtCount->execute();
$resultCount = $stmtCount->get_result();
$totalLogs = $resultCount->fetch_assoc()['count'];
$sql = "SELECT * FROM user_logs WHERE user_id LIKE ? OR action LIKE ? OR timestamp LIKE ? LIMIT ? OFFSET ?";
$stmt = $conn->prepare($sql);
$stmt->bind_param("sssii", $searchParam, $searchParam, $searchParam, $logsPerPage, $offset);
$stmt->execute();
$result = $stmt->get_result();
$logs = [];
while($row = $result->fetch_assoc()) {
$logs[] = $row;
}
$response = [
'logs' => $logs,
'totalLogs' => $totalLogs
];
header('Content-Type: application/json');
echo json_encode($response);
$conn->close();
?>
Setting Up the Front-End
Next, we need to modify our front-end to fetch data from the server-side script and display it dynamically.
HTML Structure
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>User Logs Table</title>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
</head>
<body>
<div class="container mt-5">
<h1 class="mb-4">User Logs</h1>
<div class="row mb-3">
<div class="col-md-6">
<input type="text" id="searchInput" class="form-control" placeholder="Search logs...">
</div>
</div>
<table class="table table-striped">
<thead>
<tr>
<th scope="col">#</th>
<th scope="col">User ID</th>
<th scope="col">Action</th>
<th scope="col">Timestamp</th>
</tr>
</thead>
<tbody id="logsTableBody">
<!-- Log entries will be inserted here dynamically -->
</tbody>
</table>
<nav>
<ul class="pagination justify-content-center" id="pagination">
<!-- Pagination buttons will be inserted here dynamically -->
</ul>
</nav>
</div>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
<script src="script.js"></script>
</body>
</html>
JavaScript for Fetching Data and Rendering
Update the JavaScript file (script.js) to fetch data from the PHP script and render it dynamically.
// script.js
let currentPage = 1;
const logsPerPage = 10;
$(document).ready(function () {
fetchLogs();
$('#searchInput').on('input', fetchLogs);
});
function fetchLogs() {
const searchQuery = $('#searchInput').val();
$.get('fetch_logs.php', { search: searchQuery, page: currentPage }, function (data) {
renderTable(data.logs);
renderPagination(data.totalLogs);
}, 'json');
}
function renderTable(logs) {
$('#logsTableBody').empty();
logs.forEach(log => {
$('#logsTableBody').append(`
<tr>
<th scope="row">${log.id}</th>
<td>${log.user_id}</td>
<td>${log.action}</td>
<td>${log.timestamp}</td>
</tr>
`);
});
}
function renderPagination(totalLogs) {
const totalPages = Math.ceil(totalLogs / logsPerPage);
$('#pagination').empty();
for (let i = 1; i <= totalPages; i++) {
$('#pagination').append(`
<li class="page-item ${i === currentPage ? 'active' : ''}">
<a class="page-link" href="#" data-page="${i}">${i}</a>
</li>
`);
}
$('.page-link').on('click', function (e) {
e.preventDefault();
currentPage = $(this).data('page');
fetchLogs();
});
}
Explanation
MySQL Database:
Created a database user_logs_db and a table user_logs to store the logs.
Inserted sample data into the table.
PHP Script (fetch_logs.php):
Connects to the MySQL database.
Fetches logs based on search query and pagination parameters.
Returns the logs and total count as a JSON response.
HTML Structure:
The table and search input remain the same as before.
Uses Bootstrap for styling and structure.
JavaScript (script.js):
Fetches data from the PHP script based on search query and pagination.
Renders the table and pagination dynamically.
Stay Safe Online—Get 3 Months of NordVPN Free!
Secure your internet connection instantly with NordVPN and enjoy 3 extra months free on any plan. Whether you’re browsing, streaming, or gaming, NordVPN keeps your data safe from prying eyes. Don’t wait—protect your privacy and save big today!
© vladoivankovic.com
