如何通过HTML下拉框过滤PHP生成的交易表格？

开发者阿泽

2026-8-21

问题描述

我想用PHP从数据库生成的交易表格添加下拉筛选器，根据Regular、Dealer、Refill选项过滤内容，但查到的资料多是纯HTML实现，没思路。现有代码如下：

<select id="mylist" class="dropdown-filter">
  <option>Any</option>
  <option>Regular</option>
  <option>Dealer</option>
  <option>Refill</option>
</select>

<table class="table datatable text-center" id="transaction-table">
  <thead>
    <tr>
      <th class="text-center">Customer</th>
      <th class="text-center" style="pointer-events: none;">Transaction </th>                      
      <th class="text-center">Time</th>
      <th class="text-center">Item</th>
      <th class="text-center">Quantity</th> 
      <th class="text-center">Total</th>
      <th class="text-center">Returned</th>  
      <th class="text-center">Damaged</th>
      <th class="text-center" style="pointer-events: none;">Action</th>
    </tr>
  </thead>
  <tbody>  
  <?php
    $result = mysqli_query($connections, "SELECT * FROM transac_tbl ORDER BY transac_time DESC");
    if ($result) {
      while ($row = mysqli_fetch_assoc($result)) {
        $TransactionID = $row['transac_id'];
        $Customer = $row['customer'];
        $Transaction = $row['transac_type'];
        $Time = $row['transac_time'];
        $Item = $row['item'];
        $Quantity = $row['quantity'];
        $TotalPrice = $row['total_price'];
        $Returns = $row['gal_return'];
        $Damaged = $row['damaged'];
        $Status = $row['status'];
    
        if ($Transaction == 0) {
          $Transaction1 = "Dealer";
        } elseif ($Transaction == 1) {
          $Transaction1 = "Regular";
        } else {
          $Transaction1 = "Refill";
        }
    
        if ($Item == 0) {
          $Item1 =  "Slim";
        } else {
          $Item1 = "Round";
        }
    
        echo '<tr>
              <td>'.$Customer.'</td>
              <td>'.$Transaction1.'</td>
              <td>'.date_format(new DateTime($Time), 'F d, Y | h:i A').'</td>
              <td>'.$Item1.'</td>
              <td>'.$Quantity.'</td>
              <td>'.$TotalPrice.'</td>
              <td>'.$Returns.'</td>';
    
        if ($Damaged != 0) {
          echo '<td><span class="badge bg-danger">'.$Damaged.'</span></td>';
        } else {
          echo '<td>None</td>';
        }
    
        if ($Status == 0) {
          echo '<td><button class="btn btn-dark" type="button" data-bs-toggle="modal" data-bs-target="#verticalycentered" style="margin: 3px; font-size: 13px; padding: 3px 5px; min-width: 50px;">Void</button></td>';
        } else {
          echo '<td><span class="badge bg-success">Voided</span></td>';
        }
      }
    }
  ?>
  </tbody>
</table>

解决方案

提供两种常用实现方案，按需选择：

方案一：前端JavaScript实时过滤（无需页面刷新）

适合数据量不大的场景，直接在前端控制行的显示/隐藏，不用重新请求数据库。

修改步骤

给交易类型对应的<td>标签添加自定义属性data-transaction，值为交易类型文本，方便JS识别。
编写JS监听下拉框选择事件，根据选中值过滤表格行。

修改后完整代码

<select id="mylist" class="dropdown-filter">
  <option>Any</option>
  <option>Regular</option>
  <option>Dealer</option>
  <option>Refill</option>
</select>

<table class="table datatable text-center" id="transaction-table">
  <thead>
    <tr>
      <th class="text-center">Customer</th>
      <th class="text-center" style="pointer-events: none;">Transaction </th>                      
      <th class="text-center">Time</th>
      <th class="text-center">Item</th>
      <th class="text-center">Quantity</th> 
      <th class="text-center">Total</th>
      <th class="text-center">Returned</th>  
      <th class="text-center">Damaged</th>
      <th class="text-center" style="pointer-events: none;">Action</th>
    </tr>
  </thead>
  <tbody>  
  <?php
    $result = mysqli_query($connections, "SELECT * FROM transac_tbl ORDER BY transac_time DESC");
    if ($result) {
      while ($row = mysqli_fetch_assoc($result)) {
        $TransactionID = $row['transac_id'];
        $Customer = $row['customer'];
        $Transaction = $row['transac_type'];
        $Time = $row['transac_time'];
        $Item = $row['item'];
        $Quantity = $row['quantity'];
        $TotalPrice = $row['total_price'];
        $Returns = $row['gal_return'];
        $Damaged = $row['damaged'];
        $Status = $row['status'];
    
        if ($Transaction == 0) {
          $Transaction1 = "Dealer";
        } elseif ($Transaction == 1) {
          $Transaction1 = "Regular";
        } else {
          $Transaction1 = "Refill";
        }
    
        if ($Item == 0) {
          $Item1 =  "Slim";
        } else {
          $Item1 = "Round";
        }
    
        // 给交易类型td添加自定义属性
        echo '<tr>
              <td>'.$Customer.'</td>
              <td data-transaction="'.$Transaction1.'">'.$Transaction1.'</td>
              <td>'.date_format(new DateTime($Time), 'F d, Y | h:i A').'</td>
              <td>'.$Item1.'</td>
              <td>'.$Quantity.'</td>
              <td>'.$TotalPrice.'</td>
              <td>'.$Returns.'</td>';
    
        if ($Damaged != 0) {
          echo '<td><span class="badge bg-danger">'.$Damaged.'</span></td>';
        } else {
          echo '<td>None</td>';
        }
    
        if ($Status == 0) {
          echo '<td><button class="btn btn-dark" type="button" data-bs-toggle="modal" data-bs-target="#verticalycentered" style="margin: 3px; font-size: 13px; padding: 3px 5px; min-width: 50px;">Void</button></td>';
        } else {
          echo '<td><span class="badge bg-success">Voided</span></td>';
        }
      }
    }
  ?>
  </tbody>
</table>

<script>
// 获取下拉框和所有表格行
const filterSelect = document.getElementById('mylist');
const tableRows = document.querySelectorAll('#transaction-table tbody tr');

// 监听下拉框选择变化
filterSelect.addEventListener('change', function() {
  const selectedType = this.value;
  
  tableRows.forEach(row => {
    const rowType = row.querySelector('[data-transaction]').textContent.trim();
    // 根据选中值控制行的显示
    row.style.display = (selectedType === 'Any' || rowType === selectedType) ? '' : 'none';
  });
});
</script>

方案二：后端PHP结合GET请求（从数据库筛选，页面刷新）

适合数据量大的场景，直接从数据库获取符合条件的数据，更高效。

修改步骤

用表单包裹下拉框，选择后自动提交表单传递筛选参数。
在PHP中获取筛选参数，用预处理语句动态拼接SQL（防止SQL注入）。

修改后完整代码

<!-- 表单包裹下拉框，选择后自动提交 -->
<form method="get" action="">
  <select id="mylist" class="dropdown-filter" name="transaction_filter" onchange="this.form.submit()">
    <option value="Any" <?php echo isset($_GET['transaction_filter']) && $_GET['transaction_filter'] == 'Any' ? 'selected' : ''; ?>>Any</option>
    <option value="Regular" <?php echo isset($_GET['transaction_filter']) && $_GET['transaction_filter'] == 'Regular' ? 'selected' : ''; ?>>Regular</option>
    <option value="Dealer" <?php echo isset($_GET['transaction_filter']) && $_GET['transaction_filter'] == 'Dealer' ? 'selected' : ''; ?>>Dealer</option>
    <option value="Refill" <?php echo isset($_GET['transaction_filter']) && $_GET['transaction_filter'] == 'Refill' ? 'selected' : ''; ?>>Refill</option>
  </select>
</form>

<table class="table datatable text-center" id="transaction-table">
  <thead>
    <tr>
      <th class="text-center">Customer</th>
      <th class="text-center" style="pointer-events: none;">Transaction </th>                      
      <th class="text-center">Time</th>
      <th class="text-center">Item</th>
      <th class="text-center">Quantity</th> 
      <th class="text-center">Total</th>
      <th class="text-center">Returned</th>  
      <th class="text-center">Damaged</th>
      <th class="text-center" style="pointer-events: none;">Action</th>
    </tr>
  </thead>
  <tbody>  
  <?php
    // 获取筛选参数，默认显示全部
    $filter = isset($_GET['transaction_filter']) ? $_GET['transaction_filter'] : 'Any';
    
    // 映射前端文本到数据库的transac_type数值
    $typeMap = [
      'Dealer' => 0,
      'Regular' => 1,
      'Refill' => 2
    ];
    
    // 构建SQL查询，使用预处理防止注入
    if ($filter !== 'Any' && isset($typeMap[$filter])) {
      $sql = "SELECT * FROM transac_tbl WHERE transac_type = ? ORDER BY transac_time DESC";
      $stmt = mysqli_prepare($connections, $sql);
      mysqli_stmt_bind_param($stmt, 'i', $typeMap[$filter]);
      mysqli_stmt_execute($stmt);
      $result = mysqli_stmt_get_result($stmt);
    } else {
      $sql = "SELECT * FROM transac_tbl ORDER BY transac_time DESC";
      $result = mysqli_query($connections, $sql);
    }
    
    if ($result) {
      while ($row = mysqli_fetch_assoc($result)) {
        $TransactionID = $row['transac_id'];
        $Customer = $row['customer'];
        $Transaction = $row['transac_type'];
        $Time = $row['transac_time'];
        $Item = $row['item'];
        $Quantity = $row['quantity'];
        $TotalPrice = $row['total_price'];
        $Returns = $row['gal_return'];
        $Damaged = $row['damaged'];
        $Status = $row['status'];
    
        if ($Transaction == 0) {
          $Transaction1 = "Dealer";
        } elseif ($Transaction == 1) {
          $Transaction1 = "Regular";
        } else {
          $Transaction1 = "Refill";
        }
    
        if ($Item == 0) {
          $Item1 =  "Slim";
        } else {
          $Item1 = "Round";
        }
    
        echo '<tr>
              <td>'.$Customer.'</td>
              <td>'.$Transaction1.'</td>
              <td>'.date_format(new DateTime($Time), 'F d, Y | h:i A').'</td>
              <td>'.$Item1.'</td>
              <td>'.$Quantity.'</td>
              <td>'.$TotalPrice.'</td>
              <td>'.$Returns.'</td>';
    
        if ($Damaged != 0) {
          echo '<td><span class="badge bg-danger">'.$Damaged.'</span></td>';
        } else {
          echo '<td>None</td>';
        }
    
        if ($Status == 0) {
          echo '<td><button class="btn btn-dark" type="button" data-bs-toggle="modal" data-bs-target="#verticalycentered" style="margin: 3px; font-size: 13px; padding: 3px 5px; min-width: 50px;">Void</button></td>';
        } else {
          echo '<td><span class="badge bg-success">Voided</span></td>';
        }
      }
    }
    
    // 关闭预处理语句
    if (isset($stmt)) {
      mysqli_stmt_close($stmt);
    }
  ?>
  </tbody>
</table>

注意事项

方案二必须使用预处理语句，避免SQL注入风险，这是处理用户输入的标准操作。
下拉框添加了选中状态回显，刷新页面后会保留当前筛选选项。

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

如何通过HTML下拉框过滤PHP生成的交易表格？

方案一：前端JavaScript实时过滤（无需页面刷新） #

修改步骤 #

修改后完整代码 #

方案二：后端PHP结合GET请求（从数据库筛选，页面刷新） #

修改步骤 #

修改后完整代码 #

注意事项 #

方案一：前端JavaScript实时过滤（无需页面刷新）

修改步骤

修改后完整代码

方案二：后端PHP结合GET请求（从数据库筛选，页面刷新）

修改步骤

修改后完整代码

注意事项