如何实现批次号下拉框与单选按钮联动筛选用户列表？

Hey there! Let's get that batch number + user status filter working properly. I'll walk you through fixing the existing code and adding the missing联动 logic step by step.

First, let's spot the issues in your current code: #

1. Wrong value in dropdown options: Your SQL selects rpp_id, but you're echoing $row['id'] (which doesn't exist) as the option value. This means the batch ID isn't being passed correctly to the backend.
2. No batch condition in SQL queries: Your current SELECT statements don't filter by the selected batch number, so it's returning all users of type 3 regardless of batch.
3. Missing table association: We need to link tbl_user with tbl_regn_prescribed_parameters (assuming your user table has a field that ties to the batch table, like batch_rpp_id or stores the rpp_regn_bulkbatch_no directly).

Step 1: Fix the Frontend Dropdown #

First, correct the option value to use the actual rpp_id from your query:

<form action="" method="POST">
  <div class="col-md-6">
    <div class="col-md-4">
      <label for="username" class="control-label">Batch No. : </label>
    </div>
    <div class="col-md-8">
      <select name="batchnumber" class="form-control" required id="batchno">
        <option value="">select batch number </option>
        <?php 
        $sql = mysql_query("SELECT DISTINCT rpp_regn_bulkbatch_no, rpp_id FROM tbl_regn_prescribed_parameters where rpp_regn_bulkbatch_no != 0"); 
        while ($row = mysql_fetch_assoc($sql)){
          // Use rpp_id as the option value instead of non-existent 'id'
          echo "<option value='" . $row['rpp_id'] . "'>" . $row['rpp_regn_bulkbatch_no'] . "</option>"; 
        } 
        ?>
      </select>
    </div>
  </div>
  
  <input type='radio' name='users' value='unapproved' checked /> Unapproved Candidates &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type='radio' name='users' value='approved' /> Approved Candidates &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  <input type='radio' id='show' name='users' value='all' /> All Candidates
  
  <input type="submit" value="View Candidates" id="submit" class="btn btn-success">
</form>

Step 2: Update Backend PHP Logic #

Now, modify your SQL queries to include the batch number filter. I'll assume your tbl_user has a field named batch_rpp_id that links to tbl_regn_prescribed_parameters.rpp_id (adjust the field name if your schema is different):

<?php
// Initialize variables
$users = '';
$batchnumber = '';

// Check if form is submitted and batch is selected
if(isset($_POST['users']) && isset($_POST['batchnumber']) && !empty($_POST['batchnumber'])){
  $users = $_POST['users'];
  $batchnumber = $_POST['batchnumber'];
} else {
  // Optional: Show error if batch isn't selected
  echo "<p>Please select a batch number first!</p>";
  exit;
}

// Build base query with batch filter (using JOIN to link user and batch tables)
$sqlQuery = "SELECT u.* FROM tbl_user u 
             JOIN tbl_regn_prescribed_parameters b ON u.batch_rpp_id = b.rpp_id 
             WHERE u.type = 3 AND b.rpp_id = '$batchnumber'";

// Add status filter based on radio selection
switch ($users) {
  case "approved":
    $sqlQuery .= " AND u.status = 1";
    break;
  case "unapproved":
    $sqlQuery .= " AND u.status = 0";
    break;
  // For "all", no additional status condition needed
  case "all":
  default:
    break;
}

// Execute query (note: mysql_* functions are deprecated! Switch to mysqli/PDO for security)
$result = mysql_query($sqlQuery);

// Example: Display results in a table
if(mysql_num_rows($result) > 0){
  echo "<table border='1'>
          <tr>
            <th>User ID</th>
            <th>Username</th>
            <th>Status</th>
          </tr>";
  while($userRow = mysql_fetch_assoc($result)){
    $statusText = $userRow['status'] == 1 ? 'Approved' : 'Unapproved';
    echo "<tr>
            <td>{$userRow['id']}</td>
            <td>{$userRow['username']}</td>
            <td>{$statusText}</td>
          </tr>";
  }
  echo "</table>";
} else {
  echo "<p>No candidates found for this batch and status!</p>";
}
?>

Key Improvements & Notes: #

• SQL Injection Warning: The mysql_* extension is deprecated and insecure. I strongly recommend switching to mysqli or PDO with prepared statements to protect your database from injection attacks.
• Table Association Adjustment: If your tbl_user stores the batch number (rpp_regn_bulkbatch_no) directly instead of rpp_id, update the query to use u.batch_number = '$batchnumber' (and set the dropdown option value to $row['rpp_regn_bulkbatch_no']).
• UX Enhancement: For a smoother experience, you could add JavaScript to auto-submit the form when the batch dropdown or radio button changes, instead of requiring users to click the submit button every time.

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