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

Building a User Logs Table with Navigation and Search Using Bootstrap 5
Building a User Logs Table with Navigation and Search Using Bootstrap 5

In web applications, user activity logs are crucial for monitoring and analyzing user behavior, troubleshooting issues, and ensuring security. A well-designed user logs table enhances the user experience by making it easy to search, filter, and navigate through logs. In this article, we will walk through building a user logs table with navigation and search functionality using Bootstrap 5. Prerequisites Before we start, ensure you have the following: Basic understanding of HTML, CSS, and JavaScript. Bootstrap 5 library (can be included via CDN). Setting Up the HTML Structure   First, let's create the basic HTML structure for our user logs table. We'll use Bootstrap 5's grid system and table classes to make the table responsive and visually appealing. <!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 Search and Pagination Next, we'll write JavaScript to handle search and pagination. For simplicity, we'll use jQuery to manipulate the DOM. Sample Data We'll use some sample data to populate the table. // script.js const logs = [ { id: 1, userId: 'user123', action: 'Login', timestamp: '2024-06-14 10:00:00' }, { id: 2, userId: 'user456', action: 'Logout', timestamp: '2024-06-14 10:05:00' }, // Add more sample logs here ]; const logsPerPage = 10; let currentPage = 1; let filteredLogs = logs; $(document).ready(function () { renderTable(); renderPagination(); $('#searchInput').on('input', handleSearch); }); function handleSearch() { const searchQuery = $('#searchInput').val().toLowerCase(); filteredLogs = logs.filter(log => log.userId.toLowerCase().includes(searchQuery) || log.action.toLowerCase().includes(searchQuery) || log.timestamp.toLowerCase().includes(searchQuery) ); currentPage = 1; renderTable(); renderPagination(); } function renderTable() { const startIndex = (currentPage - 1) * logsPerPage; const endIndex = startIndex + logsPerPage; const logsToDisplay = filteredLogs.slice(startIndex, endIndex); $('#logsTableBody').empty(); logsToDisplay.forEach(log => { $('#logsTableBody').append(` <tr> <th scope="row">${log.id}</th> <td>${log.userId}</td> <td>${log.action}</td> <td>${log.timestamp}</td> </tr> `); }); } function renderPagination() { const totalPages = Math.ceil(filteredLogs.length / 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'); renderTable(); renderPagination(); }); } Explanation HTML Structure: The table is defined using Bootstrap's table classes. An input field for search functionality is placed above the table. A pagination component is placed below the table. JavaScript (script.js): We initialize an array of logs for demonstration purposes. The handleSearch function filters the logs based on the search query and updates the table and pagination. The renderTable function displays the logs for the current page. The renderPagination function creates pagination buttons and handles page switching.   < > GitHub

Creating an AMP Blog Page with PHP and MySQL
Creating an AMP Blog Page with PHP and MySQL

In today's fast-paced digital world, page speed and mobile friendliness are crucial for user engagement and SEO. Accelerated Mobile Pages (AMP) is an open-source initiative that aims to provide web pages that load quickly on mobile devices. This article will guide you through creating a simple AMP-powered blog page using PHP and MySQL. Prerequisites Before we begin, ensure you have the following installed on your development environment: PHP (version 7.0 or higher) MySQL (version 5.7 or higher) A web server like Apache or Nginx Composer (for managing PHP dependencies)   Step 1: Setting Up the MySQL Database First, we need to set up a MySQL database to store our blog posts. Create a Database: Open your MySQL command line or a tool like phpMyAdmin and run the following SQL command to create a database: CREATE DATABASE amp_blog; Create a Table: Next, create a table to store the blog posts. USE amp_blog; CREATE TABLE posts ( id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(255) NOT NULL, content TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); Step 2: Setting Up the PHP Project Project Structure: Create a project directory and structure it as follows: amp-blog/ ├── index.php ├── post.php ├── db.php └── vendor/ Database Connection (db.php): Create a file named db.php to handle the database connection. <?php $host = '127.0.0.1'; $db = 'amp_blog'; $user = 'root'; $pass = ''; $charset = 'utf8mb4'; $dsn = "mysql:host=$host;dbname=$db;charset=$charset"; $options = [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]; try { $pdo = new PDO($dsn, $user, $pass, $options); } catch (\PDOException $e) { throw new \PDOException($e->getMessage(), (int)$e->getCode()); } ?> Step 3: Creating the Blog Pages Homepage (index.php): This page will display a list of blog posts. <?php require 'db.php'; $stmt = $pdo->query('SELECT id, title, created_at FROM posts ORDER BY created_at DESC'); $posts = $stmt->fetchAll(); ?> <!doctype html> <html amp> <head> <meta charset="utf-8"> <title>AMP Blog</title> <link rel="canonical" href="index.php"> <meta name="viewport" content="width=device-width,minimum-scale=1,initial-scale=1"> <style amp-custom> body { font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f9f9f9; } .container { max-width: 800px; margin: 0 auto; padding: 20px; } h1 { text-align: center; color: #333; } .post { background: #fff; border: 1px solid #ddd; padding: 20px; margin-bottom: 20px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); } .post h2 { margin-top: 0; font-size: 1.5em; } .post time { display: block; font-size: 0.9em; color: #999; margin-bottom: 10px; } .post a { text-decoration: none; color: #333; } .post a:hover { text-decoration: underline; } </style> <script async src="https://cdn.ampproject.org/v0.js"></script> </head> <body> <div class="container"> <h1>My AMP Blog</h1> <?php foreach ($posts as $post): ?> <div class="post"> <h2><a href="post.php?id=<?= $post['id'] ?>"><?= htmlspecialchars($post['title']) ?></a></h2> <time datetime="<?= $post['created_at'] ?>"><?= $post['created_at'] ?></time> </div> <?php endforeach; ?> </div> </body> </html> Single Post Page (post.php): This page will display a single blog post. <?php require 'db.php'; $id = $_GET['id']; $stmt = $pdo->prepare('SELECT title, content, created_at FROM posts WHERE id = ?'); $stmt->execute([$id]); $post = $stmt->fetch(); if (!$post) { die('Post not found!'); } ?> <!doctype html> <html amp> <head> <meta charset="utf-8"> <title><?= htmlspecialchars($post['title']) ?></title> <link rel="canonical" href="post.php?id=<?= $id ?>"> <meta name="viewport" content="width=device-width,minimum-scale=1,initial-scale=1"> <style amp-custom> body { font-family: Arial, sans-serif; margin: 0; padding: 0; background-color: #f9f9f9; } .container { max-width: 800px; margin: 0 auto; padding: 20px; } h1 { text-align: center; color: #333; } time { display: block; font-size: 0.9em; color: #999; margin-bottom: 20px; text-align: center; } .content { background: #fff; border: 1px solid #ddd; padding: 20px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); } </style> <script async src="https://cdn.ampproject.org/v0.js"></script> </head> <body> <div class="container"> <h1><?= htmlspecialchars($post['title']) ?></h1> <time datetime="<?= $post['created_at'] ?>"><?= $post['created_at'] ?></time> <div class="content"> <?= nl2br(htmlspecialchars($post['content'])) ?> </div> </div> </body> </html> Step 4: Testing Your AMP Blog Run Your Server: Start your web server (Apache or Nginx) and navigate to http://localhost/amp-blog/index.php to view your blog. Validate AMP: Use the AMP Validator to ensure your pages comply with AMP standards. Simply paste the URL of your blog pages into the validator.   < > GitHub

How to Create a Dynamic Sitemap with PHP and a Database
How to Create a Dynamic Sitemap with PHP and a Database

Creating a dynamic sitemap for your website is crucial for search engine optimization (SEO) and improving the discoverability of your content. In this tutorial, we'll walk through the process of generating a sitemap using PHP and a MySQL database. This guide assumes you have a basic understanding of PHP and MySQL. Step 1: Set Up Your Database First, ensure your database is properly set up with the necessary tables. For this example, we'll assume you have a table named pages with columns id, title, url, and last_modified.   CREATE TABLE `pages` ( `id` INT AUTO_INCREMENT PRIMARY KEY, `title` VARCHAR(255) NOT NULL, `url` VARCHAR(255) NOT NULL, `last_modified` DATETIME NOT NULL );   Step 2: Connect to Your Database Create a PHP script to connect to your MySQL database. Save this file as db_connect.php.   <?php $servername = "localhost"; $username = "username"; $password = "password"; $dbname = "database_name"; $conn = new mysqli($servername, $username, $password, $dbname); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } ?> Replace localhost, username, password, and database_name with your actual database credentials. Step 3: Fetch Data from the Database Next, we'll write a script to fetch the data from the pages table. Save this file as fetch_pages.php.   <?php include 'db_connect.php'; $sql = "SELECT url, last_modified FROM pages"; $result = $conn->query($sql); $pages = array(); if ($result->num_rows > 0) { while($row = $result->fetch_assoc()) { $pages[] = $row; } } else { echo "0 results"; } $conn->close(); ?> Step 4: Generate the Sitemap Now we'll create the sitemap. Save this file as sitemap.php.   <?php include 'fetch_pages.php'; header("Content-Type: application/xml; charset=utf-8"); echo '<?xml version="1.0" encoding="UTF-8"?>'; echo '<urlset xmlns="http://www.sitemaps.org/schemas/sitemap/0.9">'; foreach ($pages as $page) { echo '<url>'; echo '<loc>' . htmlspecialchars($page['url']) . '</loc>'; echo '<lastmod>' . date('c', strtotime($page['last_modified'])) . '</lastmod>'; echo '<changefreq>weekly</changefreq>'; echo '<priority>0.8</priority>'; echo '</url>'; } echo '</urlset>'; ?>   This script will generate an XML sitemap in the proper format for search engines to read. The date('c', strtotime($page['last_modified'])) function formats the last modified date in the correct ISO 8601 format. Step 5: Access Your Sitemap Once everything is set up, you can access your sitemap by navigating to sitemap.php in your browser. For example, if your website is hosted at http://example.com, go to http://example.com/sitemap.php to see your dynamically generated sitemap.   By following these steps, you can create a dynamic sitemap using PHP and a MySQL database. This sitemap will help search engines crawl and index your website more effectively, ultimately improving your site's SEO. Regularly updating your database with new or modified content will ensure your sitemap stays current, providing search engines with the latest information about your website.   Further Enhancements Error Handling: Improve the scripts with better error handling for database connections and queries. Pagination: If your website has a large number of pages, consider implementing pagination in your sitemap to adhere to the maximum URL limits. SEO Optimization: Adjust the changefreq and priority values based on the importance and update frequency of your pages.   By maintaining an up-to-date and dynamic sitemap, you can significantly enhance your website’s visibility and performance in search engine rankings. < > GitHub


© vladoivankovic.com

VladoIvankovic