javascript - PHP 中使用多个复选框进行过滤

Question

我想用 PHP 进行过滤。我已经这样做了，但在性别类别中，它的工作方式类似于单选按钮。当我点击男性和女性时，不会找到任何记录。所以请任何人都可以解决我的问题。

我的代码在这里: 索引.php

<h1>Snowboards</h1>

<table id="snowboards">
  <thead>
    <tr>
      <th>ID</th>
      <th>Gender</th>
      <th>Price Range</th>
      <th>Brand</th>
      <th>Model</th>
      <th>Rocker Type</th>
      <th>Flex</th>
      <th>Size Range</th>
      <th>Image Path</th>
    </tr>
  </thead>
  <tbody>
  </tbody>
</table>

<div id="filter">
  <h2>Filter options</h2>
  <h3>Gender</h3>
  <div>
    <input type="checkbox" id="male" name="male">
    <label for="male">Male</label>
  </div>
  <div>
    <input type="checkbox" id="female" name="female">
    <label for="female">Female</label>
  </div>

  <h3>Brand</h3>
  <div>
    <input type="checkbox" id="xyz" name="xyz">
    <label for="xyz">xyz</label>
  </div>
  <div>
    <input type="checkbox" id="burton" name="burton">
    <label for="burton">burton</label>
  </div>
   <div>
    <input type="checkbox" id="abc" name="abc">
    <label for="abc">abc</label>
  </div>
   <div>
    <input type="checkbox" id="www" name="www">
    <label for="www">www</label>
  </div>

  <h3>Price Range</h3>
  <div>
    <input type="checkbox" id="cheap" name="cheap">
    <label for="cheap">$200 - $299</label>
  </div>
  <div>
    <input type="checkbox" id="medium-priced" name="medium-priced">
    <label for="medium-priced">$300 - $399</label>
  </div>
  <div>
    <input type="checkbox" id="expensive" name="expensive">
    <label for="expensive">$400 - $499</label>
  </div>
</div>

<script src="http://code.jquery.com/jquery-latest.js"></script> 
<script>`enter code here`
  function makeTable(data){
   var tbl_body = "";
      $.each(data, function() {
        var tbl_row = "";
        $.each(this, function(k , v) {
          tbl_row += "<td>"+v+"</td>";
        })
        tbl_body += "<tr>"+tbl_row+"</tr>";
      })

    return tbl_body;
  }

  function getSnowboardFilterOptions(){
    var opts = [];
    $checkboxes.each(function(){
      if(this.checked){
        opts.push(this.name);
      }
    });

    return opts;
  }

  function updateSnowboards(opts){
    $.ajax({
      type: "POST",
      url: "submit1.php , submit3.php",
      dataType : 'json',
      cache: false,
      data: {filterOpts: opts},
      success: function(data){
        $('#snowboards tbody').html(makeTable(data));
      }
    });
  }

  var $checkboxes = $("input:checkbox");
  $checkboxes.on("change", function(){
    var opts = getSnowboardFilterOptions();
    updateSnowboards(opts);
  });

  updateSnowboards();
</script> 

我的submit.php是:

 <?php

   $pdo = new PDO('mysql:host=localhost;dbname=abc', 'root', '');
   $select = 'SELECT *';
   $from = ' FROM snowboards';
   $where = ' WHERE TRUE';
   $opts = isset($_POST['filterOpts'])? $_POST['filterOpts'] : array('');
    if (in_array("male", $opts)){
         $where .= " AND gender = 'male'";
    }
    if (in_array("female", $opts)){
         $where .= " AND gender = 'female'";
    }
    if (in_array("cheap", $opts)){
          $where .= " AND price_range > 199 AND price_range < 300";
     }
    if (in_array("medium-priced", $opts)){
           $where .= " AND price_range > 299 AND price_range < 400";
    }
    if(in_array("expensive", $opts)){
            $where .= " AND price_range > 399 AND price_range < 500";
     }  
     if(in_array("xyz", $opts) || in_array("burton", $opts) || in_array("abc", $opts) || 
      in_array("www", $opts)){                                                                                                                                                                                                      
           $brand = " AND (";
           if (in_array("xyz", $opts)){
               $brand .= " AND brand = 'xyz'";        
           }
           if (in_array("burton", $opts)){
                  if($brand != " AND (")
                      $brand .= " OR ";
                  $brand .= " AND brand = 'burton' ";
           }
           if(in_array("abc", $opts)){
                 if($brand != " AND (")
                      $brand .= " OR ";
                 $brand .= "AND brand = 'abc'";
           }  
           if(in_array("www", $opts)){
                  if($brand != " AND (")
                     $brand .= " OR ";
                  $brand .= "AND brand = 'www'";
           }  
           $brand .= ")";
           $where .= $brand;
  }
  $sql = $select . $from . $where;
  $statement = $pdo->prepare($sql);
  $statement->execute();
  $results=$statement->fetchAll(PDO::FETCH_ASSOC);
  $json=json_encode($results);
  echo($json);
?>

提前致谢。

Answer 1

问题是，当您选择男性和女性复选框时，您的查询会搜索性别等于男性且性别等于女性的记录。

尝试替换这个:

if (in_array("male", $opts)){
     $where .= " AND gender = 'male'";
}
if (in_array("female", $opts)){
     $where .= " AND gender = 'female'";
}

这样:

if(!(in_array("male", $opts) && in_array("female", $opts))){
     if (in_array("male", $opts)){
          $where .= " AND gender = 'male'";
     }
     if (in_array("female", $opts)){
          $where .= " AND gender = 'female'";
     }
}

现在，在向 where 变量添加任何内容之前，我们检查是否同时检查了male 和female。

更新:

要解决价格范围的相同问题，请替换:

if (in_array("cheap", $opts)){
      $where .= " AND price_range > 199 AND price_range < 300";
 }
if (in_array("medium-priced", $opts)){
       $where .= " AND price_range > 299 AND price_range < 400";
}
if(in_array("expensive", $opts)){
        $where .= " AND price_range > 399 AND price_range < 500";
 }  

与:

 // TO ADD MORE RANGES: Add " || in_array("name", $opts)" to the if-statement.
 if(in_array("cheap", $opts) || in_array("medium-priced", $opts) || in_array("expensive", $opts)){
     $price_range = " AND (";
     if (in_array("cheap", $opts)){
          $price_range .= "price_range > 199 AND price_range < 300";        
     }
     if (in_array("medium-priced", $opts)){
          if($price_range != " AND (")
               $price_range .= " OR ";
          $price_range .= "price_range > 299 AND price_range < 400";
     }
     if(in_array("expensive", $opts)){
          if($price_range != " AND (")
               $price_range .= " OR ";
          $price_range .= "price_range > 399 AND price_range < 500";
     } 

     /* TO ADD MORE RANGES: Add the following code
     if(in_array("name", $opts)){
          if($price_range != " AND (")
               $price_range .= " OR ";
          $price_range .= "price_range > 999 AND price_range < 9999";
     }  
     */

     $price_range .= ")";
     $where .= $price_range;
 }

现在我们检查是否选中了任一价格范围复选框，以便我们可以将 AND() 添加到查询中。然后，对于中等价格和昂贵的产品，我们检查 $price_range 变量是否为空。如果没有，我们添加OR将其添加到价格范围声明中。

更新2:

您的品牌代码中犯了一些错误。请查看以下几行:

 $brand .= " AND brand = 'xyz'";
 ...
 $brand .= " AND brand = 'burton' ";
 ...
 $brand .= "AND brand = 'abc'";
 ...
 $brand .= "AND brand = 'www'";

查询现在最终将如下所示:WHERE ... AND ( AND Brand = 'xyz' OR AND Brand = 'burton'换句话说，您想要从这些行中删除所有 AND 和空格:

 $brand .= "brand = 'xyz'";
 ...
 $brand .= "brand = 'burton'";
 ...
 $brand .= "brand = 'abc'";
 ...
 $brand .= "brand = 'www'";

请记住，我在这里输入了这段代码，但尚未对其进行测试，请告诉我是否犯了错误