Building a User Logs Table with Navigation and Search Using Bootstrap 5 and MySQL
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.

 

< > GitHub




To engage in discussions and post comments, kindly log in or register to create an account.

© vladoivankovic.com