如何在WordPress中通过AJAX实现多筛选条件的数据库数据查询

Got it, let's get your multi-filter system up and running, with support for future checkboxes and instant updates without a submit button. We'll break this into frontend and backend changes, focusing on flexibility and security.

Step 1: Update the HTML (Add a Common Class for Filters) #

First, let's add a shared class to all filter controls so we can listen for changes on any of them easily—this will make adding new filters (like checkboxes) later a breeze.

<div class="sidebar-filters">
  <div class="filters">
    <span>Gender</span>
    <select id="gender" name="gender" class="filter-control">
      <option value="">Please select gender</option>
      <option value="male">male</option>
      <option value="female">female</option>
      <option value="agender">agender</option>
    </select>

    <span>Marital Status</span>
    <select id="marital_status" name="marital_status" class="filter-control">
      <option value="">Please select marital status</option>
      <option value="married">married</option>
      <option value="widowed">widowed</option>
      <option value="separated">separated</option>
      <option value="divorced">divorced</option>
      <option value="single">single</option>
    </select>

    <!-- Example future checkbox filter (you can add these later) -->
    <!-- <span>Age Range</span>
    <input type="checkbox" name="age_18_25" value="18-25" class="filter-control"> 18-25
    <input type="checkbox" name="age_26_35" value="26-35" class="filter-control"> 26-35 -->
  </div>
</div>

<div class="results-wrap">
  <?php 
  $query = "SELECT * FROM reviews1";
  $result = mysqli_query($conn, $query);
  $queryResult = mysqli_num_rows($result);
  if($queryResult > 0){
    while($row = mysqli_fetch_assoc($result)){
      echo "<div class='review-wrap'>
              <h3>".$row['r_title']."</h3>
              <p>".$row['age']."</p>
            </div>";
    }
  }else{
    echo "There are no results matching your search";
  } 
  ?>
</div>

Step 2: Rewrite the JavaScript to Handle All Filters #

Instead of listening only to the gender dropdown, we'll listen to all .filter-control elements. We'll collect all filter values and send them as a single object to the backend—this makes it easy to add more filters later without rewriting the AJAX logic.

// Listen for changes on ANY filter control (dropdowns, checkboxes, etc.)
$('.filter-control').on('change', function(){
  // Collect all filter values
  const filters = {
    gender: $('#gender').val(),
    marital_status: $('#marital_status').val(),
    // Add future filter values here, e.g.:
    // age_ranges: $('input[name="age_range"]:checked').map(function(){ return $(this).val(); }).get()
  };

  $.ajax({
    url: "http://uttertest.ecom-shops.com/wp-admin/admin-ajax.php",
    type: 'POST',
    data: { 
      action: 'filtering',
      filters: filters // Send all filters as an object
    },
    beforeSend:function(){
      $(".results-wrap").html("Wait..."); // Fixed selector (added dot for class)
    },
    success:function(data){
      $(".results-wrap").html(data); // Fixed selector
    },
    error:function(xhr){
      $(".results-wrap").html("Error loading results");
    }
  });
});

Note: I fixed the selector in beforeSend/success—you were missing the dot for the .results-wrap class, which would have broken the loading state and result update.

Step 3: Update the Backend PHP to Handle Dynamic Filters #

The biggest change here is building a dynamic SQL query that only includes filters with non-empty values, and using prepared statements to prevent SQL injection (critical for security!). We'll also handle the case where no filters are selected (return all results).

add_action( 'wp_ajax_nopriv_filtering', 'filtering' );
add_action( 'wp_ajax_filtering', 'filtering' );

function filtering(){
  $server = "localhost";
  $username = "username";
  $password = "*****";
  $dbname = "databasename";
  
  $conn = mysqli_connect($server, $username, $password, $dbname);
  if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
  }

  $filters = $_POST['filters'];
  $conditions = [];
  $params = [];
  $types = "";

  // Build conditions for each filter that has a value
  if (!empty($filters['gender'])) {
    $conditions[] = "gender = ?";
    $params[] = $filters['gender'];
    $types .= "s"; // 's' for string
  }

  if (!empty($filters['marital_status'])) {
    $conditions[] = "marital_status = ?";
    $params[] = $filters['marital_status'];
    $types .= "s";
  }

  // Build the query
  $query = "SELECT * FROM reviews";
  if (!empty($conditions)) {
    $query .= " WHERE " . implode(" AND ", $conditions);
  }

  // Use prepared statement to prevent SQL injection
  $stmt = mysqli_prepare($conn, $query);
  if (!empty($params)) {
    mysqli_stmt_bind_param($stmt, $types, ...$params);
  }
  mysqli_stmt_execute($stmt);
  $result = mysqli_stmt_get_result($stmt);

  $queryResult = mysqli_num_rows($result);
  if($queryResult > 0){
    while($row = mysqli_fetch_assoc($result)){
      echo "<div class='review-wrap'>
              <h3>".$row['r_title']."</h3>
              <p>".$row['age']."</p>
            </div>";
    }
  }else{
    echo "There are no results matching your search";
  }

  mysqli_stmt_close($stmt);
  mysqli_close($conn);
  wp_die(); // Required for WordPress AJAX responses
}

Key Backend Improvements: #

• Dynamic Conditions: Only adds WHERE clauses for filters that have a selected value (so if marital status is empty, it ignores that filter).
• Prepared Statements: No more directly inserting user input into SQL—this eliminates SQL injection risks.
• Extensible: Adding a new filter (like checkboxes for age ranges) just requires adding another condition block here (you'd need to adjust the logic for multiple checkbox values, but the structure stays the same).
• Proper Cleanup: Closing statements and connections, plus adding wp_die() which is required for WordPress AJAX to work correctly.

How It Works #

• When any filter (dropdown, future checkbox) changes, JavaScript collects all current filter values and sends them to the backend.
• The backend builds a query that only includes active filters, runs it safely with prepared statements, and returns the matching results.
• The results are instantly updated in the results-wrap div—no submit button needed!

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