php - 根据之前的表单输入和来自 MySQL 的数据填充下拉列表

Question

我有一张表格要求客户提供一些详细信息。我希望根据客户的输入填充最终的下拉列表。该下拉列表的数据是使用 PHP 从 MySql 数据库中获取的。这是我准备的代码，但它似乎不起作用。

表单代码:

               <tr>  
                <td><label>Trade:</label></td>
                <span class="text_11">
                <td><input type="radio" id="Buy" name="tradetype" class="listen" required value="Buy"/><radio style="font-size: 16px;"> Buy</radio>
                <input type="radio" id="Sell" name="tradetype" class="listen" value="Sell"/><radio style="font-size: 16px;"> Sell </radio></span></td>
           </tr>
           <tr>  
                <td><label>Item:</label></td>
                <span class="text_11">
                <td><input type="radio" id="Cloth" name="item" class="listen" required value="Cloth"/><radio style="font-size: 16px;"> Cloth</radio>
                <input type="radio" id="Fruit" name="item" class="listen" value="Fruit"/><radio style="font-size: 16px;"> Fruit</radio></span></td>
           </tr>
            <tr>  
                <td class="select"><label>Date:</label></td>
                    <td><select id="exdate" name="date1">
                <option value="2">Select</option>
                <?php include_once "selectdate.php"?></td>
                </select>
           </tr> 
           <tr>  
                <td class="select"><label>Amount:</label></td>
                <td><select id="noselect" name="noselect">
                <option value="1">Select</option>
                <option value="1">Choose Item</option>
                </select>
           </tr> 

金额下拉列表必须使用来自 MySQL 的基于贸易、项目和日期的数据进行填充。

这是我的 jquery:

$(document).ready(function () {

$('#exdate').change(function () {

    var tradetype = $('[name="tradetype"]:checked').val();
    var date = $('select[name=date1]').val()
    var item = $('[name="item"]:checked').val();

    $('#confirm_tradetype').text(tradetype);
    $('#confirm_date1').text(date1);
    $('#confirm_item').text(item);

    $.ajax({
        type: "POST",
        url: "selectamount.php",
        data: {
                "tradetype": tradetype,
                "item": item,
                "date1": date1,
        },
        success: function (data) { 
        var source = 
        {
            datatype: "json",
            datafields: 
            { name: 'Amount'},
            url: 'selectamount.php'
        };
        var dataAdpater = new $.jqx.dataAdapter(source);
        $("#noselect").jqxDropDownList(
        {
        source: dataAdapter,
        displayMember: 'Amount',
        valueMember: 'Amount',
        });
    }
    });
  });
  });

这是 php 查询 (selectamount.php)

<?php
include_once "connect.php";
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); 

$form=$_POST;
$trade=$form['tradetype'];
$date1=$form['date1'];
$item=$form['item'];

$stmt = $conn->query("SELECT Amount FROM Contracts WHERE Trade='$trade' AND Item='$item' AND Date='$date1' ORDER BY Amount ASC"); 
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
   echo json_encode($row);
}

$conn->db=null;
?>

Answer 1

对代码进行了一些实验后，这对我有用:

表单代码更改:将 onchange="getAmount(this.value);" 添加到日期选择

           <tr>  
            <td><label>Trade:</label></td>
            <span class="text_11">
            <td><input type="radio" id="Buy" name="tradetype" class="listen" required value="Buy"/><radio style="font-size: 16px;"> Buy</radio>
            <input type="radio" id="Sell" name="tradetype" class="listen" value="Sell"/><radio style="font-size: 16px;"> Sell </radio></span></td>
       </tr>
       <tr>  
            <td><label>Item:</label></td>
            <span class="text_11">
            <td><input type="radio" id="Cloth" name="item" class="listen" required value="Cloth"/><radio style="font-size: 16px;"> Cloth</radio>
            <input type="radio" id="Fruit" name="item" class="listen" value="Fruit"/><radio style="font-size: 16px;"> Fruit</radio></span></td>
       </tr>
        <tr>  
            <td class="select"><label>Date:</label></td>
                <td><select id="exdate" name="date1" onchange="getAmount(this.value);">
            <option value="2">Select</option>
            <?php include_once "selectdate.php"?></td>
            </select>
       </tr> 
       <tr>  
            <td class="select"><label>Amount:</label></td>
            <td><select id="amount" name="amount">
            <option value="1">Select</option>
            <option value="1">Choose Item</option>
            </select>
       </tr> 

jquery 代码更改:使用其 id 将 php 中的数据分配给 amount select。

function getAmount(val) {

var tradetype = $('[name="tradetype"]:checked').val();
var date = $('select[name=date1]').val()
var item = $('[name="item"]:checked').val();

$('#confirm_tradetype').text(tradetype);
$('#confirm_date1').text(date1);
$('#confirm_item').text(item);

$.ajax({
    type: "POST",
    url: "selectamount.php",
    data: {
            "tradetype": tradetype,
            "item": item,
            "date1": date1,
    },
    success: function (data) { 
        $("#amount").html(data);
    }
});

}

Php 代码更改:将查询结果回显为选项值。

<?php
include_once "connect.php";
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); 

$form=$_POST;
$trade=$form['tradetype'];
$date1=$form['date1'];
$item=$form['item'];

$stmt = $conn->query("SELECT Amount FROM Contracts WHERE Trade='$trade' AND Item='$item' AND Date='$date1' ORDER BY Amount ASC"); 
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
      echo "<option value='" .  $row['Amount'] . "'>" . $row['Amount'] . "</option>";
}
$conn->db=null;

?>