1. 引言：动态年份过滤的需求
在管理大量时间序列数据时，用户通常需要按特定的时间范围进行筛选。例如，一个包含年份信息的数据库表，用户可能希望根据“2016-2020”或“2021-2025”这样的年份区间来查看数据。手动创建这些选项既耗时又不灵活，因此，构建一个能够根据数据库实际数据动态生成年份范围选项的下拉选择器变得尤为重要。本文将详细阐述如何使用php和mysql实现这一功能，并安全地处理用户输入进行数据过滤。
2. 构建动态年份范围选择器
要动态生成年份范围选项，我们首先需要确定数据库中存在的最小和最大年份。这为我们提供了生成所有可能年份区间的边界。
2.1 获取数据库中的最小和最大年份
通过一条简单的SQL查询，我们可以从目标表中获取Year列的最小值和最大值。
SELECT MIN(MPU_Ano) AS min_year, MAX(MPU_Ano) AS max_year FROM view_mpacompanhamentogeral;
在PHP中执行此查询并获取结果：
<?php
// 假设 $conn 是一个有效的 MySQLi 数据库连接
// $conn = new mysqli("localhost", "username", "password", "database");
// if ($conn->connect_error) {
// die("连接失败: " . $conn->connect_error);
// }
$minMaxYearSql = "SELECT MIN(MPU_Ano) AS min_year, MAX(MPU_Ano) AS max_year FROM view_mpacompanhamentogeral";
$result = $conn->query($minMaxYearSql);
$minYear = null;
$maxYear = null;
if ($result && $result->num_rows > 0) {
$row = $result->fetch_assoc();
$minYear = (int)$row['min_year'];
$maxYear = (int)$row['max_year'];
} else {
// 处理无数据的情况，或者设置默认年份范围
$minYear = date("Y") - 10; // 例如，当前年份前10年
$maxYear = date("Y"); // 当前年份
}
// 释放结果集
if ($result) {
$result->free();
}
?>2.2 生成年份范围选项
一旦获取了最小和最大年份，我们就可以循环生成以5年为间隔的年份范围。
立即学习“PHP免费学习笔记（深入）”；
<select id="selectFilterByYears" name="year_range">
<option value="">选择年份范围</option>
<?php
if ($minYear !== null && $maxYear !== null) {
// 确保起始年份是5的倍数，或者从实际最小年份开始
// 我们可以直接从最小年份开始，然后以5为步长递增
$currentStartYear = $minYear;
while ($currentStartYear <= $maxYear) {
$currentEndYear = $currentStartYear + 4; // 5年间隔 (例如 2016-2020)
// 如果计算出的结束年份超出了最大年份，则将结束年份设为最大年份
if ($currentEndYear > $maxYear) {
$currentEndYear = $maxYear;
}
$rangeValue = "{$currentStartYear}-{$currentEndYear}";
echo "<option value=\"{$rangeValue}\">{$rangeValue}</option>";
// 移动到下一个5年区间的起始年份
$currentStartYear += 5;
// 防止无限循环，如果下一个起始年份已经超过了最大年份且当前区间已覆盖最大年份
if ($currentStartYear > $maxYear && $currentEndYear == $maxYear) {
break;
}
}
}
?>
</select>3. 处理用户选择并过滤数据
当用户从下拉菜单中选择一个年份范围并提交表单时，我们需要解析这个范围并将其应用到SQL查询中。
3.1 解析年份范围
用户选择的值（例如 "2016-2021"）可以通过$_GET或$_POST获取。使用explode()函数可以轻松将其拆分为起始年份和结束年份。
<?php
$selectedYearRange = $_GET['year_range'] ?? ''; // 假设通过GET方法提交
$startYear = null;
$endYear = null;
if (!empty($selectedYearRange)) {
$years = explode('-', $selectedYearRange);
if (count($years) === 2) {
$startYear = (int)$years[0];
$endYear = (int)$years[1];
}
}
?>3.2 使用SQL BETWEEN 进行过滤
SQL的BETWEEN操作符非常适合按范围过滤数据。它包含起始值和结束值。
SELECT SUP_Numero ID, MPU_Ano `Year`, MPU_Programada `Status`, TAC_Nome `Action` FROM view_mpacompanhamentogeral WHERE MPU_Ano BETWEEN ? AND ?;
3.3 安全的数据过滤（预处理语句）
重要提示： 直接将用户输入的值拼接到SQL查询字符串中会导致SQL注入漏洞。务必使用预处理语句（Prepared Statements）来安全地绑定参数。
以下是使用MySQLi预处理语句进行数据过滤的示例：
<?php
// ... (之前的数据库连接 $conn 和年份范围解析代码) ...
$filteredData = [];
if ($startYear !== null && $endYear !== null) {
$sql = "SELECT SUP_Numero ID, MPU_Ano `Year`, MPU_Programada `Status`, TAC_Nome `Action`
FROM view_mpacompanhamentogeral
WHERE MPU_Ano BETWEEN ? AND ?";
// 准备语句
if ($stmt = $conn->prepare($sql)) {
// 绑定参数
$stmt->bind_param("ii", $startYear, $endYear); // "ii" 表示两个整数类型参数
// 执行语句
$stmt->execute();
// 获取结果
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$filteredData[] = $row;
}
}
// 关闭语句
$stmt->close();
} else {
echo "SQL 语句准备失败: " . $conn->error;
}
} else {
// 如果没有选择年份范围，可以显示所有数据或提示用户选择
// 例如：
$sql = "SELECT SUP_Numero ID, MPU_Ano `Year`, MPU_Programada `Status`, TAC_Nome `Action`
FROM view_mpacompanhamentogeral ORDER BY MPU_Ano DESC LIMIT 100"; // 示例：显示最新100条
$result = $conn->query($sql);
if ($result && $result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$filteredData[] = $row;
}
}
if ($result) {
$result->free();
}
}
// 现在 $filteredData 数组包含了根据选择的年份范围过滤后的数据
// 你可以在这里循环遍历 $filteredData 并将其展示在HTML表格中
// 示例：展示过滤后的数据
if (!empty($filteredData)) {
echo "<h3>过滤结果:</h3>";
echo "<table border='1'>";
echo "<tr><th>ID</th><th>年份</th><th>状态</th><th>操作</th></tr>";
foreach ($filteredData as $record) {
echo "<tr>";
echo "<td>" . htmlspecialchars($record['ID']) . "</td>";
echo "<td>" . htmlspecialchars($record['Year']) . "</td>";
echo "<td>" . htmlspecialchars($record['Status']) . "</td>";
echo "<td>" . htmlspecialchars($record['Action']) . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "<p>没有找到符合条件的记录。</p>";
}
// 关闭数据库连接
$conn->close();
?>4. 完整示例代码结构
将上述所有部分整合到一个PHP文件中，通常包括以下结构：
<!DOCTYPE html>
<html lang="zh-CN">
<head>
<meta charset="UTF-8">
<title>动态年份范围过滤器</title>
<style>
body { font-family: Arial, sans-serif; margin: 20px; }
select, button { padding: 8px; margin-right: 10px; }
table { width: 100%; border-collapse: collapse; margin-top: 20px; }
th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
th { background-color: #f2f2f2; }
</style>
</head>
<body>
<h1>数据年份过滤器</h1>
<?php
// 1. 数据库连接配置
$dbHost = 'localhost';
$dbUser = 'your_username'; // 替换为你的数据库用户名
$dbPass = 'your_password'; // 替换为你的数据库密码
$dbName = 'your_database'; // 替换为你的数据库名
$conn = new mysqli($dbHost, $dbUser, $dbPass, $dbName);
if ($conn->connect_error) {
die("数据库连接失败: " . $conn->connect_error);
}
// 2. 获取数据库中的最小和最大年份
$minMaxYearSql = "SELECT MIN(MPU_Ano) AS min_year, MAX(MPU_Ano) AS max_year FROM view_mpacompanhamentogeral";
$resultMinMax = $conn->query($minMaxYearSql);
$minYear = null;
$maxYear = null;
if ($resultMinMax && $resultMinMax->num_rows > 0) {
$rowMinMax = $resultMinMax->fetch_assoc();
$minYear = (int)$rowMinMax['min_year'];
$maxYear = (int)$rowMinMax['max_year'];
} else {
// 如果没有数据，设置一个默认范围或提示
$minYear = 2000;
$maxYear = date("Y");
echo "<p>数据库中没有找到年份数据，将使用默认范围。</p>";
}
if ($resultMinMax) {
$resultMinMax->free();
}
// 3. 构建年份范围选择表单
?>
<form action="" method="GET">
<label for="selectFilterByYears">按年份范围过滤:</label>
<select id="selectFilterByYears" name="year_range">
<option value="">所有年份</option>
<?php
if ($minYear !== null && $maxYear !== null) {
$currentStartYear = floor($minYear / 5) * 5; // 从最接近的5年倍数开始，或直接从minYear开始
if ($currentStartYear > $minYear) $currentStartYear -= 5; // 确保包含minYear
if ($currentStartYear < $minYear) $currentStartYear = $minYear; // 确保不会低于minYear
while ($currentStartYear <= $maxYear) {
$currentEndYear = $currentStartYear + 4;
if ($currentEndYear > $maxYear) {
$currentEndYear = $maxYear;
}
$rangeValue = "{$currentStartYear}-{$currentEndYear}";
$selected = (isset($_GET['year_range']) && $_GET['year_range'] === $rangeValue) ? 'selected' : '';
echo "<option value=\"{$rangeValue}\" {$selected}>{$currentStartYear}-{$currentEndYear}</option>";
$currentStartYear += 5;
}
}
?>
</select>
<button type="submit">应用过滤器</button>
</form>
<?php
// 4. 处理用户选择并执行数据过滤
$selectedYearRange = $_GET['year_range'] ?? '';
$startYearFilter = null;
$endYearFilter = null;
$filteredData = [];
if (!empty($selectedYearRange)) {
$years = explode('-', $selectedYearRange);
if (count($years) === 2) {
$startYearFilter = (int)$years[0];
$endYearFilter = (int)$years[1];
}
}
// 构建SQL查询
$sql = "SELECT SUP_Numero ID, MPU_Ano `Year`, MPU_Programada `Status`, TAC_Nome `Action`
FROM view_mpacompanhamentogeral";
if ($startYearFilter !== null && $endYearFilter !== null) {
$sql .= " WHERE MPU_Ano BETWEEN ? AND ?";
}
$sql .= " ORDER BY MPU_Ano DESC, ID ASC"; // 增加排序以便查看
// 使用预处理语句
if ($stmt = $conn->prepare($sql)) {
if ($startYearFilter !== null && $endYearFilter !== null) {
$stmt->bind_param("ii", $startYearFilter, $endYearFilter);
}
$stmt->execute();
$result = $stmt->get_result();
if ($result->num_rows > 0) {
while ($row = $result->fetch_assoc()) {
$filteredData[] = $row;
}
}
$stmt->close();
} else {
echo "<p>SQL 语句准备失败: " . $conn->error . "</p>";
}
// 5. 展示过滤后的数据
echo "<h2>数据列表</h2>";
if (!empty($filteredData)) {
echo "<table>";
echo "<tr><th>ID</th><th>年份</th><th>状态</th><th>操作</th></tr>";
foreach ($filteredData as $record) {
echo "<tr>";
echo "<td>" . htmlspecialchars($record['ID']) . "</td>";
echo "<td>" . htmlspecialchars($record['Year']) . "</td>";
echo "<td>" . htmlspecialchars($record['Status']) . "</td>";
echo "<td>" . htmlspecialchars($record['Action']) . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
echo "<p>没有找到符合当前过滤条件的记录。</p>";
}
// 6. 关闭数据库连接
$conn->close();
?>
</body>
</html>5. 注意事项与最佳实践
- 数据库连接： 示例中使用mysqli扩展，你也可以选择更现代、功能更强大的PDO（PHP Data Objects）来连接和操作数据库。
- 错误处理： 在实际生产环境中，需要更健壮的错误处理机制，例如使用try-catch块捕获数据库异常，并向用户显示友好的错误信息，而不是直接暴露系统错误。
-
用户体验：
- 在下拉菜单中添加一个“所有年份”或“请选择”的默认选项，以便用户可以清除过滤器。
- 在用户选择后，使相应的选项保持选中状态（如示例代码所示）。
- 考虑使用AJAX异步加载数据，以提供更流畅的用户体验，避免页面刷新。
- 性能优化： 对于非常大的数据集，确保MPU_Ano列有索引，这将显著提高MIN(), MAX(), 和 BETWEEN 查询的性能。
- 代码组织： 对于大型应用，将数据库操作、业务逻辑和视图层分离，例如使用MVC（Model-View-Controller）架构。
6. 总结
通过上述步骤，我们成功构建了一个动态的年份范围选择器，它能够根据数据库中的实际数据生成过滤选项，并安全有效地对数据进行筛选。核心在于获取数据范围、循环生成选项以及使用预处理语句执行带BETWEEN条件的SQL查询。遵循这些实践不仅能提高应用的灵活性和用户体验，还能确保数据的安全性和查询效率。
