如何在单页网站中实现日期区间数据筛选功能

首先假设你的poll数据表中存在记录数据提交时间的字段（比如created_at，如果没有这个字段，需要先给表添加该字段），以下是具体的修改步骤和完整代码：

1. 添加日期筛选表单 #

在表格上方添加用于输入开始、结束日期的筛选表单：

<form method="POST" class="w3-container w3-padding">
  <label>开始日期:</label>
  <input type="date" name="start_date" class="w3-input w3-border" required>
  <label>结束日期:</label>
  <input type="date" name="end_date" class="w3-input w3-border" required>
  <button type="submit" name="filter" class="w3-btn w3-green w3-margin-top">筛选数据</button>
</form>

2. 修改PHP查询逻辑 #

更新SQL查询逻辑，根据提交的日期范围过滤数据，同时使用预处理语句避免SQL注入：

<?php 
session_start();
require 'config.php';
if (isset($_SESSION['login_user'])) {
    $userLoggedIn = $_SESSION['login_user'];
    
    // 初始化基础查询语句
    $query = "SELECT * FROM poll";
    $params = [];
    $types = "";
    
    // 如果用户提交了筛选请求，添加日期过滤条件
    if (isset($_POST['filter'])) {
        $start_date = $_POST['start_date'];
        $end_date = $_POST['end_date'];
        $query .= " WHERE created_at BETWEEN ? AND ?";
        $params = [$start_date, $end_date];
        $types = "ss"; // 标记两个参数为字符串类型
    }
    
    // 执行预处理查询
    $stmt = mysqli_prepare($con, $query);
    if (!empty($params)) {
        mysqli_stmt_bind_param($stmt, $types, ...$params);
    }
    mysqli_stmt_execute($stmt);
    $result = mysqli_stmt_get_result($stmt);
    
    // 输出表格（新增提交时间列）
    echo "<table border='1' id='customers'>
    <tr>
    <th>Name</th>
    <th>Email</th>
    <th>Phone</th>
    <th>feedback</th>
    <th>feedback1</th>
    <th>feedback2</th>
    <th>Suggestions</th>
    <th>提交时间</th>
    </tr>";

    while($row = mysqli_fetch_array($result))
    {
    echo "<tr>";
    echo "<td>" . $row['name'] . "</td>";
    echo "<td>" . $row['email'] . "</td>";
    echo "<td>" . $row['phone'] . "</td>";
    echo "<td>" . $row['feedback'] . "</td>";
    echo "<td>" . $row['feedback1'] . "</td>";
    echo "<td>" . $row['feedback2'] . "</td>";    
    echo "<td>" . $row['suggestions'] . "</td>";
    echo "<td>" . $row['created_at'] . "</td>";
    echo "</tr>";
    }
    echo "</table>";                                                                    
}
else {
    header("Location: index.php");
    exit;
}
?>

完整修改后的代码 #

<!DOCTYPE html>
<html lang="en" >

<head>
  <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css">
  <style>
#customers {
  font-family: "Trebuchet MS", Arial, Helvetica, sans-serif;
  border-collapse: collapse;
  width: 100%;
}

#customers td, #customers th {
  border: 1px solid #ddd;
  padding: 8px;
}

#customers tr:nth-child(even){background-color: #f2f2f2;}
#customers tr:nth-child(odd){background-color: #f2f2f2;}
#customers tr:hover {background-color: #ddd;}

#customers th {
  padding-top: 12px;
  padding-bottom: 12px;
  text-align: left;
  background-color: #4CAF50;
  color: white;
}
.block {
  display: block;
  width: 100%;
  border: none;
  background-color: #4CAF50;
  color: white;
  padding: 14px 28px;
  font-size: 16px;
  cursor: pointer;
  text-align: center;
}

.block:hover {
  background-color: #ddd;
  color: black;
}
</style>
  <meta charset="UTF-8">
  <title>Feedback</title>
  <script src="https://cdnjs.cloudflare.com/ajax/libs/modernizr/2.8.3/modernizr.min.js" type="text/javascript"></script>


    <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/normalize/5.0.0/normalize.min.css">

  
      <link rel="stylesheet" href="css/style.css">

  
</head>

<body>
 <form action = "#" method="POST"> 
          <div class="w3-show-inline-block">
          <div class="w3-bar">
            <center>
              <input type="submit" value="Download as PDF" name="logout" class="w3-btn w3-black">
            </center>
        </div>
        </div>  
  </form>
   <form action = "logout.php" method="POST"> 
          <div class="w3-show-inline-block">
          <div class="w3-bar">
            <center>
              <input type="submit" value="LogOut" name="logout" class="w3-btn w3-black">
            </center>
        </div>
        </div>  
  </form>

<!-- 日期筛选表单 -->
<form method="POST" class="w3-container w3-padding">
  <label>开始日期:</label>
  <input type="date" name="start_date" class="w3-input w3-border" required>
  <label>结束日期:</label>
  <input type="date" name="end_date" class="w3-input w3-border" required>
  <button type="submit" name="filter" class="w3-btn w3-green w3-margin-top">筛选数据</button>
</form>

  <?php 
  session_start();
  require 'config.php';
   if (isset($_SESSION['login_user'])) {
          $userLoggedIn = $_SESSION['login_user'];
          
          $query = "SELECT * FROM poll";
          $params = [];
          $types = "";
          
          if (isset($_POST['filter'])) {
              $start_date = $_POST['start_date'];
              $end_date = $_POST['end_date'];
              $query .= " WHERE created_at BETWEEN ? AND ?";
              $params = [$start_date, $end_date];
              $types = "ss";
          }
          
          $stmt = mysqli_prepare($con, $query);
          if (!empty($params)) {
              mysqli_stmt_bind_param($stmt, $types, ...$params);
          }
          mysqli_stmt_execute($stmt);
          $result = mysqli_stmt_get_result($stmt);
            
    
            echo "<table border='1' id='customers'>
            <tr>
            <th>Name</th>
            <th>Email</th>
            <th>Phone</th>
            <th>feedback</th>
            <th>feedback1</th>
            <th>feedback2</th>
            <th>Suggestions</th>
            <th>提交时间</th>
            </tr>";

            while($row = mysqli_fetch_array($result))
            {
            echo "<tr>";
            echo "<td>" . $row['name'] . "</td>";
            echo "<td>" . $row['email'] . "</td>";
            echo "<td>" . $row['phone'] . "</td>";
            echo "<td>" . $row['feedback'] . "</td>";
            echo "<td>" . $row['feedback1'] . "</td>";
            echo "<td>" . $row['feedback2'] . "</td>";    
            echo "<td>" . $row['suggestions'] . "</td>";
            echo "<td>" . $row['created_at'] . "</td>";
            echo "</tr>";
            }
            echo "</table>";                                                                    
}
  else {
  header("Location: index.php");
  exit;
}
   ?>
    
  <script src='http://cdnjs.cloudflare.com/ajax/libs/jquery/2.1.3/jquery.min.js'></script>
  
  
</body>

</html>

关键注意事项 #

• 如果你的数据表中日期字段不是created_at，请将代码中所有created_at替换为实际字段名。
• 预处理语句的使用是为了避免SQL注入风险，这是处理用户输入的安全规范。
• 表单的required属性确保用户必须填写两个日期才能提交筛选请求。

内容的提问来源于stack exchange，提问作者Nurul Nadira