如何在php中运行2个sql查询到不同的表，使下拉列表在html形式？

尝试在html中创建一个用户输入表单，其中用户选择输入是基于下拉列表的。第一个下拉列表是在php中从一个sql查询到一个表，第二个下拉列表是在同一个php文件中，但用不同的sql查询到同一个数据库中的另一个表。我可以让1工作，但不是2 -下拉列表显示返回的项目的正确数量，但它们是空白的。
我试过搜索解决方案，可以让表单使用php数组工作，而不需要sql查询。当在myphpadmin中运行时，sql查询返回正确的列表，我也尝试在数组中获得结果，但没有成功。我使用mariadb作为sql数据库。我可以在同一个php文件中运行2个查询，生成2个表来显示结果，但无法弄清楚如何将结果放入html中的工作下拉列表中
php file with 2 sql queries to different sql table as different html tables

<!-- add new item to the database-->
<?php
// set variables for the php to read from the HTML below
$inputA = "";
$inputB ="";

$errorMessage = "";
$successMessage = "";

// check data transmitted using post method
if ( $_SERVER['REQUEST_METHOD'] == 'POST') {
    $inputA = mysqli_real_escape_string($connection, $_POST['inputA']);
    $inputB = mysqli_real_escape_string($connection, $_POST['inputB']);
    
    // data validation
    do {
        // check if all fields are completed as required
        if  ( empty($inputA) || empty($inputB)) {
            $errorMessage = "All fields are all required";
            break;
        } 
        
        //SQL query to update database
        $sql = "INSERT INTO trial (inputA, inputB) " .
                "VALUES ('$inputA', '$inputB')";
        //run query
        $result = $connection->query($sql);

        //if db connection query NG then show error
        if (!$result) {
            $errorMessage = "Invalid query" .$connection->error;
            break;
        }

        //reset variables ready for next use
        $inputA = "";
        $inputB ="";

        //show success message
        $successMessage ="Breakdown added";
        
        //redirect back to table page
        header("location: index.php");
        exit;

    } while (false);
}
?>

<!-- HTML set up-->
<!DOCTYPE html>
<html land="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE-edge">
    <meta name="viewport" content="width-device-width, initial-scale=1.0">
    <title>Add New Item</title>
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha1/dist/css/bootstrap.min.css">
</head>

<!-- main body of page -->
<body>
    <!-- title -->
    <div class="container my-5">
        <h2>New Item</h2>

        <!-- show message under title on error -->
        <?php
        if ( !empty($errorMessage) ){
            echo"
            <div class='alert alert-warning alert-dismissible fade show' role='alert'>
                <strong>$errorMessage</strong>
                <button type='button' class='btn-close' data-bs-dismiss='alert' aria-label='Close'></button>
            </div>
            ";
        }
        ?>
        
        <!-- HTML form to collect data-->
        <form method="post">
  
            <!-- input based on drop down list from database -->
            <div class="row mb-3">
                <label class ="col-sm-3 col-form-label">Input A</label>
                <div class ="col-sm-6">
                    <select name = "inputA" class = "form-control">
                    <option value = "" disabled="disabled" selected="selected">inputA select</option> <!--to add blank at loading-->
                    <?php
                    // include the database connection file
                        include_once("config.php");
                    //get info for drop down lists on html form from the database
                        $sql = "SELECT fruit FROM fruit";
                        $mc_list = $connection->query($sql); // run query and store as variable
                        //use while loop to fetch data from database and make into category array
                        while ($category1 = mysqli_fetch_array($inputA,MYSQLI_ASSOC)):; //variable with SQL result stored in
                    ?>
                        <option value ="<?php echo $category1["inputA"]; ?>">
                            <?php echo $category1["inputA"]; ?> <!--to show the category content to the user-->
                        </option>
                    <?php
                        endwhile;
                        //while loop must be terminated

                    ?>
                    </select>
                </div>
            </div>

            <!-- input based on drop down list from database -->
            <div class="row mb-3">
                <label class ="col-sm-3 col-form-label">inputB</label>
                <div class ="col-sm-6">
                    <select name = "responding_mbr" class = "form-control">
                    <option value = "" disabled="disabled" selected="selected">inputB select</option> <!--to add blank at loading-->
                    <?php
                    // include the database connection file
                        //$mysqli->next_result();
                        $sql1 = "SELECT veggies FROM veggies";
                        $inputB = $connection->query($sql1); // run query and store as variable
                        //use while loop to fetch data from database and make into category array
                        while ($category2 = mysqli_fetch_array($inputB,MYSQLI_ASSOC)):; //variable with SQL result stored in
                    ?>
                        <option value ="<?php echo $category2["inputB"]; ?>">
                            <?php echo $category2["inputB"]; ?> <!--to show the category content to the user-->
                        </option>
                    <?php
                        endwhile;
                        //while loop must be terminated
                    ?>
                    </select>
                </div>
            </div>

            <!--show succss message-->
            <?php
            if (!empty($successMessage) ) {
                echo "
                <div class='row mb-3'>
                    <div class='offset-sm-3 col-sm-6'>
                        <div class='alert alert-success alert-dismissible fade show' role='alert'>
                            <strong>$successMessage</strong>
                            <button type='button' class='btn-close' data-bs-dismiss='alert' aria-label='Close'></button>
                        </div>
                    </div>
                </div>
                ";
            }
            ?>

            <!-- boot strap control buttons -->
            <div class="row mb-3">
                <div class="offset-sm-3 col-sm-3 d-grid">
                    <button type="submit" class="btn btn-primary">Submit</button>
                </div>
                <div class="colsm-3 col-sm-3 d-grid">
                    <a class="btn btn-outline-primary" href="index.php" role="button">Cancel</a>
                </div>
            </div>
        </form>
    </div>

<?php
    //close connection
    mysqli_close($connection);
?>

</body>
</html>

你的代码中有几个错误。让我们首先修复第二个下拉列表的代码，称为veggies。必须使用列名作为索引。

<div class="row mb-3">
    <label class ="col-sm-3 col-form-label">inputB</label>
    <div class ="col-sm-6">
        <select name = "responding_mbr" class = "form-control">
        <option value = "" disabled="disabled" selected="selected">inputB select</option> <!--to add blank at loading-->
        <?php
        // include the database connection file
            //$mysqli->next_result();
            $sql1 = "SELECT veggies FROM veggies";
            $inputB = $connection->query($sql1); // run query and store as variable
            //use while loop to fetch data from database and make into category array
            while ($category2 = mysqli_fetch_array($inputB,MYSQLI_ASSOC)):; //variable with SQL result stored in
        ?>
            <option value ="<?php echo $category2["veggies"]; ?>"> // <------ use the column name here as index, as you're using MYSQLI_ASSOC
                <?php echo $category2["veggies"]; ?> // <------ use the column name here as index, as you're using MYSQLI_ASSOC
            </option>
        <?php
            endwhile;
            //while loop must be terminated
        ?>
        </select>
    </div>
</div>

现在，对于第一个下拉列表，您使用了错误的变量作为结果。inputA应该是mc_list，并且必须再次使用列名作为索引。

<div class="row mb-3">
    <label class ="col-sm-3 col-form-label">Input A</label>
    <div class ="col-sm-6">
        <select name = "inputA" class = "form-control">
        <option value = "" disabled="disabled" selected="selected">inputA select</option> <!--to add blank at loading-->
        <?php
        // include the database connection file
            include_once("config.php");
        //get info for drop down lists on html form from the database
            $sql = "SELECT fruit FROM fruit";
            $mc_list = $connection->query($sql); // run query and store as variable
            //use while loop to fetch data from database and make into category array
            while ($category1 = mysqli_fetch_array($mc_list,MYSQLI_ASSOC)):; // use mc_list here, instead of inputA
        ?>
            <option value ="<?php echo $category1["fruit"]; ?>"> // <------ use the column name here as index, as you're using MYSQLI_ASSOC
                <?php echo $category1["fruit"]; ?> // <------ use the column name here as index, as you're using MYSQLI_ASSOC
            </option>
        <?php
            endwhile;
            //while loop must be terminated

        ?>
        </select>
    </div>
</div>

如何在php中运行2个sql查询到不同的表，使下拉列表在html形式？

1条答案

相关问题

热门标签

最新问答