php - 使用 php/mysql 处理多个搜索查询

Question

我建立了一个汽车经销商门户，我的搜索表单中有多个下拉菜单，我面临的问题是一旦用户执行搜索而不是显示用户搜索的内容，所有内容都会从数据库中显示。

我真的无法指出我哪里弄错了。如果有人不能请看一下并帮助我，我将非常感激。

表单页面如下。

<script>
function populate(s1,s2){
    var s1 = document.getElementById(s1);
    var s2 = document.getElementById(s2);
    s2.innerHTML = "";
    if(s1.value == "Honda"){
        var optionArray = ["accord|Accord","pilot|Pilot","crv|CRV"];
    } else if(s1.value == "Toyota"){
        var optionArray = ["corolla|Corolla","camry|Camry","highlander|Highlander"];
    } else if(s1.value == "Peugeot"){
        var optionArray = ["206|206","307|307"];
    }
    for(var option in optionArray){
        var pair = optionArray[option].split("|");
        var newOption = document.createElement("option");
        newOption.value = pair[0];
        newOption.innerHTML = pair[1];
        s2.options.add(newOption);
    }
}
</script>


<form action="test2.php" method="get" name="search">


<select name="bodytype">
  <option value="Saloon">Saloon</option>
  <option value="SUV">SUV</option>
</select>

<select name="make" id="make"  onchange="populate(this.id, 'model')">
 <option selected>Any</option>
                             <option value="Toyota">Toyota</option>
                             <option value="Honda">Honda</option>
                             <option value="BMW">BMW</option>
</select>


<select name="model"  id="model">

</select>


<select name="cost1">
                                                <option selected>Any</option>
                                                <option>10000</option>
                                                <option>20000</option>
                                                <option>30000</option>
                                                <option>40000</option>
                                                <option>50000</option>
                                                <option>60000</option>
                                                <option>70000</option>
                                                <option>80000</option>
                                                <option>90000</option>
                                                <option>100000</option>
                                          </select>


                                          <select name="cost2">
                                                <option selected>Any</option>
                                                <option>10000</option>
                                                <option>20000</option>
                                                <option>30000</option>
                                                <option>40000</option>
                                                <option>50000</option>
                                                <option>60000</option>
                                                <option>70000</option>
                                                <option>80000</option>
                                                <option>90000</option>
                                                <option>100000</option>
                                          </select>


<select name="transmission">
                                                <option selected>Any</option>
                                                <option>Auto</option>
                                                <option>Manual</option>

                                          </select>


<input name="submit" type="submit">


</form>

这里是表单处理页面

include('functions/config.php'); 
    if(isset($_GET['submit'])){






  $query = "SELECT * FROM details WHERE 1=1"; // Grab All Records
if ( isset($_GET["bodytype"]) ) 
   $query = $query . " AND bodytype = '%".$_GET["bodytype"]."%'"; // Filter on Body Type
if ( isset($_GET["make"]) ) 
   $query = $query . " AND make = '%".$_GET["make"]."%'"; // Filter on Make
   if ( isset($_GET["model"]) ) 
   $query = $query . " AND model = '%".$_GET["model"]."%'"; // Filter Model
   if ( isset($_GET["transmission"]) ) 
   $query = $query . " AND transmission = '%".$_GET["transmission"]."%'"; // Filter on Transmission




                                $search_query = mysqli_query($connection, $query);
                                if(!$search_query){

                                die("QUERY FAILED" .mysqli_error($connection));
                                                    }       

                                                    $count = mysqli_num_rows($search_query);

                                                    if($count == 0){

                                                            echo "no result";
                                                    }else{

                                                    while($row = mysqli_fetch_assoc($search_query)){
                                                    echo $row["bodytype"]."</br>";
                                                    echo $row["make"]."</br>";
                                                    echo $row["model"]."</br>";
                                                    /*echo $row["MinPrice"]."</br>";
                                                    echo $row["MaxPrice"]."</br>";*/
                                                    echo $row["transmission"]."</br>"; 

                                                    }



                                                    }



}

?>

Answer 1

如果这些 get[] 之一是空白参数，则假设该字段不为空，则该字段将返回 true，因为类似 '%%' 将为 true。

您只想对具有数据的 get 参数执行 like 语句。

这将是最一致的语法，但我也建议未列出的 SQL 注入(inject)更新。

请注意，我将您的过滤器从 OR 更改为 And

$query = "SELECT * FROM details WHERE 1=1"; // Grab All Records
if ( isset($_GET["bodytype"]) ) 
   $query = $query . " AND bodytype = '%".$_GET["bodytype"]."%'"; // Filter on Body Type
if ( isset($_GET["make"]) ) 
   $query = $query . " AND make = '%".$_GET["make"]."%'"; // Filter on Make

...