Building a Filterable and Paginated User Management System with PHP and Bootstrap 5
In modern web applications, displaying data in a user-friendly manner is crucial. Two key components of this are filtering and pagination, which allow users to efficiently navigate through large datasets. In this tutorial, we'll learn how to create a user management system using PHP for backend processing and Bootstrap 5 for frontend styling, complete with filtering and pagination functionalities.
Prerequisites:
Basic knowledge of PHP
Understanding of HTML and CSS
Familiarity with Bootstrap 5
Technologies Used:
PHP
MySQL (or any other relational database)
Bootstrap 5
Step 1: Setting Up the Database
First, let's create a MySQL database and a table to work with. For demonstration purposes, let's assume we have a table named users with columns id, name, email, and age.
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100),
age INT
);
Step 2: Connect to the Database
Create a PHP file (db.php) to establish a connection with the database.
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "your_database";
// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
?>
Step 3: Fetch and Display Data
Create a PHP file (index.php) to fetch data from the database and display it in a table.
<?php
include 'db.php';
// Fetch total number of records
$sql = "SELECT COUNT(id) AS total FROM users";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$total_records = $row["total"];
// Pagination variables
$limit = 10; // Number of records per page
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 1; // Current page number
$start = ($page - 1) * $limit; // Starting record number for the current page
// Fetch data with filtering
$nameFilter = $_GET['name'] ?? '';
$emailFilter = $_GET['email'] ?? '';
$ageFilter = $_GET['age'] ?? '';
$filterQuery = "";
if (!empty($nameFilter)) $filterQuery .= " AND name = '$nameFilter'";
if (!empty($emailFilter)) $filterQuery .= " AND email = '$emailFilter'";
if (!empty($ageFilter)) $filterQuery .= " AND age = '$ageFilter'";
$sql = "SELECT * FROM users WHERE 1 $filterQuery LIMIT $start, $limit";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td data-column='name'>" . $row['name'] . "</td>";
echo "<td data-column='email'>" . $row['email'] . "</td>";
echo "<td data-column='age'>" . $row['age'] . "</td>";
echo "</tr>";
}
} else {
echo "<tr><td colspan='3'>No records found</td></tr>";
}
// Calculate total pages for the filtered data
$total_filtered_records = $conn->query("SELECT COUNT(id) AS total FROM users WHERE 1 $filterQuery")->fetch_assoc()["total"];
$total_filtered_pages = ceil($total_filtered_records / $limit);
$conn->close();
?>
Step 4: Adding Bootstrap 5 Styling
Include Bootstrap 5 CSS and JavaScript files in your HTML file.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>User Management</title>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container mt-5">
<h1>User Management</h1>
<div class="mb-3">
<select class="form-select" id="nameFilter">
<option value="">Filter by Name</option>
</select>
</div>
<div class="mb-3">
<select class="form-select" id="emailFilter">
<option value="">Filter by Email</option>
</select>
</div>
<div class="mb-3">
<select class="form-select" id="ageFilter">
<option value="">Filter by Age</option>
</select>
</div>
<div class="table-responsive">
<table class="table table-striped" id="userTable">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Age</th>
</tr>
</thead>
<tbody>
<!-- PHP code to display table and pagination -->
</tbody>
</table>
</div>
<nav aria-label="Page navigation">
<ul class="pagination">
<?php if ($page > 1): ?>
<li class="page-item"><a class="page-link" href="?page=<?=($page-1)?>&name=<?=$nameFilter?>&email=<?=$emailFilter?>&age=<?=$ageFilter?>">Previous</a></li>
<?php endif; ?>
<?php for($i = max(1, $page - 2); $i <= min($page + 2, $total_filtered_pages); $i++): ?>
<li class="page-item <?= ($page == $i) ? 'active' : '' ?>"><a class="page-link" href="?page=<?=$i?>&name=<?=$nameFilter?>&email=<?=$emailFilter?>&age=<?=$ageFilter?>"><?=$i?></a></li>
<?php endfor; ?>
<?php if ($page < $total_filtered_pages): ?>
<li class="page-item"><a class="page-link" href="?page=<?=($page+1)?>&name=<?=$nameFilter?>&email=<?=$emailFilter?>&age=<?=$ageFilter?>">Next</a></li>
<?php endif; ?>
</ul>
</nav>
</div>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
</body>
</html>
Step 5: Implementing Column Filters (Optional)
To add column filters, you can use JavaScript/jQuery to dynamically filter the table based on user input.
<script>
$(document).ready(function(){
// Function to populate select options
function populateSelectOptions(columnName, selectElement) {
var uniqueValues = [];
$("#userTable tbody tr").each(function() {
var value = $(this).find('td[data-column="' + columnName + '"]').text().trim();
if (value !== '') {
if (!uniqueValues.includes(value)) {
uniqueValues.push(value);
selectElement.append("<option value='" + value.toLowerCase() + "'>" + value + "</option>");
}
}
});
}
// Populate select options for each column
populateSelectOptions("name", $("#nameFilter"));
populateSelectOptions("email", $("#emailFilter"));
populateSelectOptions("age", $("#ageFilter"));
// Event listeners for select elements
$("#nameFilter").on("change", function() {
applyFilters();
});
$("#emailFilter").on("change", function() {
applyFilters();
});
$("#ageFilter").on("change", function() {
applyFilters();
});
function applyFilters() {
var nameFilter = $("#nameFilter").val().toLowerCase();
var emailFilter = $("#emailFilter").val().toLowerCase();
var ageFilter = $("#ageFilter").val().toLowerCase();
var url = "index.php?page=1";
if (nameFilter !== '') url += "&name=" + nameFilter;
if (emailFilter !== '') url += "&email=" + emailFilter;
if (ageFilter !== '') url += "&age=" + ageFilter;
window.location.href = url;
}
});
</script>
By following this tutorial, you've learned how to create a robust user management system with PHP and Bootstrap 5. You've implemented filtering and pagination functionalities, providing users with an efficient way to search and navigate through large datasets. This project serves as a foundation for building more advanced web applications that require similar features.
At the end, index.php should look like this:
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>User Management</title>
<link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<div class="container mt-5">
<h1>User Management</h1>
<div class="mb-3">
<select class="form-select" id="nameFilter">
<option value="">Filter by Name</option>
</select>
</div>
<div class="mb-3">
<select class="form-select" id="emailFilter">
<option value="">Filter by Email</option>
</select>
</div>
<div class="mb-3">
<select class="form-select" id="ageFilter">
<option value="">Filter by Age</option>
</select>
</div>
<div class="table-responsive">
<table class="table table-striped" id="userTable">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Age</th>
</tr>
</thead>
<tbody>
<?php
include 'db.php';
// Fetch total number of records
$sql = "SELECT COUNT(id) AS total FROM users";
$result = $conn->query($sql);
$row = $result->fetch_assoc();
$total_records = $row["total"];
// Pagination variables
$limit = 10; // Number of records per page
$page = isset($_GET['page']) && is_numeric($_GET['page']) ? $_GET['page'] : 1; // Current page number
$start = ($page - 1) * $limit; // Starting record number for the current page
// Fetch data with filtering
$nameFilter = $_GET['name'] ?? '';
$emailFilter = $_GET['email'] ?? '';
$ageFilter = $_GET['age'] ?? '';
$filterQuery = "";
if (!empty($nameFilter)) $filterQuery .= " AND name = '$nameFilter'";
if (!empty($emailFilter)) $filterQuery .= " AND email = '$emailFilter'";
if (!empty($ageFilter)) $filterQuery .= " AND age = '$ageFilter'";
$sql = "SELECT * FROM users WHERE 1 $filterQuery LIMIT $start, $limit";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
echo "<tr>";
echo "<td data-column='name'>" . $row['name'] . "</td>";
echo "<td data-column='email'>" . $row['email'] . "</td>";
echo "<td data-column='age'>" . $row['age'] . "</td>";
echo "</tr>";
}
} else {
echo "<tr><td colspan='3'>No records found</td></tr>";
}
// Calculate total pages for the filtered data
$total_filtered_records = $conn->query("SELECT COUNT(id) AS total FROM users WHERE 1 $filterQuery")->fetch_assoc()["total"];
$total_filtered_pages = ceil($total_filtered_records / $limit);
$conn->close();
?>
</tbody>
</table>
</div>
<nav aria-label="Page navigation">
<ul class="pagination">
<?php if ($page > 1): ?>
<li class="page-item"><a class="page-link" href="?page=<?=($page-1)?>&name=<?=$nameFilter?>&email=<?=$emailFilter?>&age=<?=$ageFilter?>">Previous</a></li>
<?php endif; ?>
<?php for($i = max(1, $page - 2); $i <= min($page + 2, $total_filtered_pages); $i++): ?>
<li class="page-item <?= ($page == $i) ? 'active' : '' ?>"><a class="page-link" href="?page=<?=$i?>&name=<?=$nameFilter?>&email=<?=$emailFilter?>&age=<?=$ageFilter?>"><?=$i?></a></li>
<?php endfor; ?>
<?php if ($page < $total_filtered_pages): ?>
<li class="page-item"><a class="page-link" href="?page=<?=($page+1)?>&name=<?=$nameFilter?>&email=<?=$emailFilter?>&age=<?=$ageFilter?>">Next</a></li>
<?php endif; ?>
</ul>
</nav>
</div>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script>
$(document).ready(function(){
// Function to populate select options
function populateSelectOptions(columnName, selectElement) {
var uniqueValues = [];
$("#userTable tbody tr").each(function() {
var value = $(this).find('td[data-column="' + columnName + '"]').text().trim();
if (value !== '') {
if (!uniqueValues.includes(value)) {
uniqueValues.push(value);
selectElement.append("<option value='" + value.toLowerCase() + "'>" + value + "</option>");
}
}
});
}
// Populate select options for each column
populateSelectOptions("name", $("#nameFilter"));
populateSelectOptions("email", $("#emailFilter"));
populateSelectOptions("age", $("#ageFilter"));
// Event listeners for select elements
$("#nameFilter").on("change", function() {
applyFilters();
});
$("#emailFilter").on("change", function() {
applyFilters();
});
$("#ageFilter").on("change", function() {
applyFilters();
});
function applyFilters() {
var nameFilter = $("#nameFilter").val().toLowerCase();
var emailFilter = $("#emailFilter").val().toLowerCase();
var ageFilter = $("#ageFilter").val().toLowerCase();
var url = "index.php?page=1";
if (nameFilter !== '') url += "&name=" + nameFilter;
if (emailFilter !== '') url += "&email=" + emailFilter;
if (ageFilter !== '') url += "&age=" + ageFilter;
window.location.href = url;
}
});
</script>
</body>
</html>
Happy Coding!
Feel free to customize this tutorial further to suit your audience's needs and provide additional explanations or code examples where necessary.
To engage in discussions and post comments, kindly log in or register to create an account.
Nicky77
This tutorial effectively demonstrates how to create a robust user management system using PHP and Bootstrap 5. The combination of filtering and pagination enhances usability, allowing efficient navigation through large datasets. Clear explanations and well-structured code make it accessible for developers of varying skill levels. ***://storysaver.page/ Overall, an invaluable resource for anyone looking to implement a feature-rich user management system in their web applications.
Tauma
Amazing article.I like this post and this link ***://pmkisanyojanastatus.com/
.jpg)
.jpg)
© borntobrowse.com
