mysql - 添加到 MySQL 查询

Question

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

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

我想使用 if 语句来查看是否正在使用下拉菜单，以及是否正在选择性别以将信息添加到查询中。

<?php

@include ('conn.php');

$query = $db->prepare("SELECT Name, MAX(Count) AS MaxCount, ZIPcode, Year FROM Name_Data WHERE Year = :year AND Name_Data.Count = ( SELECT MAX( Count ) FROM Name_Data AS f WHERE f.id = Name_Data.id ) GROUP BY ZIPcode");

$query->bindParam(':jaar', $_GET['dropdownyear'], PDO::PARAM_STR, 30);

$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();

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

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

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

Answer 1

这可能对您有帮助:

$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);
}