php - PHP bindParam和多个dropdownmenu

在表单中选择性别时，我想在查询中添加WHERE。

我正在使用bindParam来使用一个dropdownmenu来获取Year，但我想要第二个bindParam来向查询插入“ AND Gender =”，并插入“ $ _GET ['dropdowngender']”来填充年份。

我想使用一条if语句查看是否正在使用dropdownmenu以及是否选择了将信息添加到查询中的性别。到目前为止，这是我所拥有的，但似乎没有用。

<?php

@include ('conn.php');

$query = "SELECT Name, MAX(Count) AS MaxCount, ZIPcode, Year
          FROM Name_Data
          WHERE Year = :year ";
if( isset( $_GET['dropdowngender'] ) {
    $query = $query . " AND Gender = :gender ";
}

$query = $query . " AND Name_Data.Count =
         ( SELECT MAX( Count ) FROM Name_Data AS f
           WHERE f.id = Name_Data.id
         ) GROUP BY ZIPcode";

$query->bindParam(':year', $_GET['dropdownyear'], PDO::PARAM_STR);
if( isset( $_GET['dropdowngender'] ) {
    $query->bindParam(':gender', $_GET['dropdowngender'], PDO::PARAM_STR);
}

$query_send = $db->prepare($query);

$query->execute();

$result = $query->fetchAll (PDO::FETCH_ASSOC);

$jsonResult = json_encode($result);

echo $jsonResult;

?>

我的索引页

<form method="post" action="#" id="form">
        <select name="dropdownyear" id="menuyear">
            <option selected>Choose a year</option>
        </select>
        <select name="dropdowngender" id="menugender">
            <option value="M">Male</option>
            <option value="F">Female</option>
        </select>
</form>

我的jQuery脚本

$( "#form" )
.change(function(){
$(".marker").empty();
var SelectYear= $("#menuyear option:selected").text();
var SelectGender= $("#menugender option:selected").text();

    $.getJSON( "sql.php",  { dropdownyear: SelectYear, dropdowngender: SelectGender }, function(jsonData) {
        console.log( jsonData );

        $.each( jsonData, function(key,row) {
            var newDiv=$('<div>');
            var Content = row ['Name'] + ' ';
            newDiv.text(Content);
            newDiv.appendTo($("#result"));
    }); // each

    }); //get json
}); // change function

最佳答案

请添加收到的错误消息，是否打开了错误报告功能，有些语法错误会立即使程序停止。紧接着，您在字符串上调用bindParam，这也不起作用;）。

（Zag dit toevallig op fb，gtz，d）

编辑：

这未经测试，但是应该是这样的：

// I assume you have an instance of the pdo object? I will call it $db for convenience.
require_once 'conn.php';

$q = "SELECT Name, MAX(Count) AS MaxCount, ZIPcode, Year
      FROM Name_Data
      WHERE Year = :year";

if( isset( $_GET['dropdowngender'] ) && !empty( $_GET['dropdowngender']) ) {
    $q .= " AND Gender = :gender";
}

$q .= " AND Name_Data.Count =
         ( SELECT MAX( Count ) FROM Name_Data AS f
           WHERE f.id = Name_Data.id
         ) GROUP BY ZIPcode";

$stmt = $db->prepare($q);
$stmt->bindParam(':year', $_GET['dropdownyear'], PDO::PARAM_STR);
if( isset( $_GET['dropdowngender'] ) && !empty( $_GET['dropdowngender']) ) {
    $stmt->bindParam(':gender', $_GET['dropdowngender'], PDO::PARAM_STR);
}
$stmt->execute();

$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

$jsonResult = json_encode($result);

echo $jsonResult;