MySql 下拉过滤器选择 NULL

Question

我正在尝试为自定义表格建立一个下拉过滤系统。我有以下代码，但我似乎无法让它工作，这样如果未选择其中一个下拉列表，它仍然会根据第二个下拉菜单的选择返回结果:

<form method="POST"  action="<?php echo $_SERVER['REQUEST_URI']; ?>">
   <select name="bt_nbs_intervention">
     <option value=''>NbS intervention type</option>
     <option value="3">Protection</option>
     <option value="143">Restoration</option>
   </select>

  <select name="climate_change_impacts">
    <option value=''>Climate change impacts</option>
    <option value="15">Drought</option>
    <option value="12">Flood</option>
    <option value="25">Mangrove</option>
  </select>

 <input type='submit' value='Search'>
 </form>

<?php

if ($_POST['bt_nbs_intervention'] === '') {
$_POST['bt_nbs_intervention'] = null; 
}

if ($_POST['climate_change_impacts'] === '') {
$_POST['climate_change_impacts'] = null; 
}

$bt_nbs_intervention = (int)$_POST['bt_nbs_intervention'];
$cc_impact  = (int)$_POST['climate_change_impacts'];
$interventions = $wpdb->get_results($wpdb->prepare("

SELECT ID, intervention_post_title
FROM wp_cpt_combined
WHERE (bt_nbs_intervention IS NULL OR bt_nbs_intervention = %d) AND 
(climate_change_impacts IS NULL OR climate_change_impacts = %d)", 
$bt_nbs_intervention, $cc_impact));

if($interventions)
  foreach ( $interventions as $intervention )
  {
   echo $intervention->intervention_post_title;
  }
else {
 echo "no results";
}

本质上，我想做的就是允许用户选择一个或多个选择框来产生结果。这样，如果他们只选择干预类型，他们就会得到结果，或者只有气候变化影响，或者如果他们选择两者。

希望这是有道理的。

谢谢d

Answer 1

请检查以下内容:

<form method="POST"  action="<?php echo $_SERVER['REQUEST_URI']; ?>">
  <select name="bt_nbs_intervention">
    <option value=''>NbS intervention type</option>
    <option value="3">Protection</option>
    <option value="143">Restoration</option>
  </select>

  <select name="climate_change_impacts">
    <option value=''>Climate change impacts</option>
    <option value="15">Drought</option>
    <option value="12">Flood</option>
    <option value="25">Mangrove</option>
  </select>

  <input type='submit' value='Search'>
</form>

<?php

$where = [];
$placeholders = [];    
if(!empty($_POST['bt_nbs_intervention'])){
  $where[] = 'bt_nbs_intervention = %d';
  $placeholders[] = (int)$_POST['bt_nbs_intervention'];
}

if(!empty($_POST['climate_change_impacts'])){
  $where[] = 'climate_change_impacts = %d';
  $placeholders[] = (int)$_POST['climate_change_impacts'];
}

$sql = "SELECT ID, intervention_post_title FROM wp_cpt_combined";

if(!empty($where)){
  $sql .= ' WHERE ';

  $i = 0;
  foreach($where as $clause){
    $sql .= $i == 0 ? $clause : ' AND ' . $clause;
    $i++;
  }

  $sql = $wpdb->prepare($sql, $placeholders);
}

$interventions = $wpdb->get_results($sql);