php - 如何显示显示当前值的数据库动态生成的选项列表

问题描述

我创建了一个程序，允许您从表格中单击要编辑的书籍。然后，您可以选择从动态创建的表单中更新图书的内容。

我可以很好地向用户反映账面价值，但是在将它们放入选项下拉列表时，我遇到了问题。

所以我试图显示一个可以由用户更改的类别和发布者的下拉列表，但加载时会填充正确的信息

创建表的代码

$sql = "SELECT bookISBN
             , bookTitle
             , bookYear
             , bookPrice
             , catDesc 
          FROM nbl_books b
          JOIN nbl_category c
            on c.catID = b.catID
         ORDER 
            BY bookTitle
    ";

$queryResult = $dbConn->query($sql);
    if($queryResult->rowcount() > 0){
        echo "<table class='table-bordered table-striped'>"; //bootstrap class
            echo "<thead>";
                echo "<tr>";
                    echo "<th>Title</th>";
                    echo "<th>Year</th>";
                    echo "<th>Category</th>";
                    echo "<th>Price</th>";
                echo "</tr>";
            echo "</thead>"; //Tableheading end
            echo "<tbody>";
            while ($rowObj = $queryResult->fetchObject()){ 
                $rowObj->booktitle = filter_var($rowObj->bookTitle, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
                $rowObj->booktitle = filter_var($rowObj->bookTitle, FILTER_SANITIZE_SPECIAL_CHARS);

                $rowObj->bookISBN = filter_var($rowObj->bookISBN, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
                $rowObj->bookISBN = filter_var($rowObj->bookISBN, FILTER_SANITIZE_SPECIAL_CHARS);

                $rowObj->bookYear = filter_var($rowObj->bookYear, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
                $rowObj->bookYear = filter_var($rowObj->bookYear, FILTER_SANITIZE_SPECIAL_CHARS);

                $rowObj->catDesc = filter_var($rowObj->catDesc, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
                $rowObj->catDesc = filter_var($rowObj->catDesc, FILTER_SANITIZE_SPECIAL_CHARS);


                $rowObj->bookPrice = filter_var($rowObj->bookPrice, FILTER_SANITIZE_STRING, FILTER_FLAG_NO_ENCODE_QUOTES);
                $rowObj->bookPrice = filter_var($rowObj->bookPrice, FILTER_SANITIZE_SPECIAL_CHARS);

                echo "<tr>";
                echo "<td><a href='update.php?bookISBN={$rowObj->bookISBN}'> {$rowObj->bookTitle} </a></td>";
                echo "<td> $rowObj->bookYear </td>";
                echo "<td> $rowObj->catDesc </td>";
                echo "<td> $rowObj->bookPrice </td>";
                echo "</tr>";
            }
            echo "</tbody>";
            echo "</table>";
            unset($queryResult);


    } else {
        echo "no records found";
    }

} catch (Exception $e){
    echo "<p>Query failed: ".$e->getMessage()."</p>\n";
}

向用户显示前三个可编辑值

<form id='updatebook' action='updateMovie.php' method='get'>

<?php 
//BookTitle passed by hyperlink
$bookISBN = $_GET['bookISBN'];


if (empty($bookISBN)) {
    echo "<p>Please <a href='welcome.php'>choose</a> a movie.</p>\n";
}
else {
    try {
        require_once ("functions.php");
        $dbConn = getConnection(); 

$sql = "SELECT bookISBN, bookTitle, bookYear, bookPrice -- catDesc, pubName
        FROM nbl_books
        -- INNER JOIN nbl_category
        -- ON nbl_category.catID = nbl_books.catID
        -- INNER JOIN nbl_publisher
        -- ON nbl_publisher.pubID = nbl_publisher.pubID
        WHERE bookISBN = $bookISBN";
        
        $queryResult = $dbConn->query($sql);

        $rowObj = $queryResult->fetchObject();
        
        echo "
        <h1>Update '{$rowObj->bookTitle}'</h1>
        
            <p>Title <input type='text' name='title' size='50' value='{$rowObj->bookTitle}' /></p>
            <p>Book year <input type='text' name='bookYear' value='{$rowObj->bookYear}' /></p>
            <p>Book Price <input type='text' name='bookPrice' value='{$rowObj->bookPrice}' /></p> ";
            ?>

现在这是我遇到问题的地方。

在第一个下拉列表中，您可以看到类别是正确的，但我无法显示其他选项

类别

<select name="categoryID">
<?php   

        $sqlCat = "SELECT nbl_category.catID, catDesc, bookISBN
        from nbl_category
        INNER JOIN nbl_books
        ON nbl_books.catID = nbl_category.catID
        WHERE bookISBN = $bookISBN
        ORDER BY catDesc";

        
        $nblCat = $dbConn->query($sqlCat);

        while ($catRecord = $nblCat->fetchObject()) {
            echo "<option value='{$catRecord->catID}'> {$catRecord->catDesc}</option>";
        }; 
?>
    </select>

在另一个下拉列表中，您可以看到发布者，尽管这不是正确的值，它只是数据库表中的第一个值，但是 while 循环成功地遍历了其他发布者，因此还有其他选项。

<select name="PublisherID">
    <?php   
    
                $sqlPub = "SELECT pubID, pubName 
                from nbl_publisher 
                ORDER BY pubName";
                
                $nblPub = $dbConn->query($sqlPub);

                while ($pubRecord = $nblPub->fetchObject()) {
                    echo "<option value='{$pubRecord->pubID}'>{$pubRecord->pubName}</option>";
                }; 
    ?>
            </select> <br>

标签： phpmysqlpdo

我相信您的类别下拉填充查询不应仅限于您当前书籍的 ISBN。尝试这个：

<select name="categoryID">
<?php   

        $sqlCat = "
SELECT nbl_category.catID
     , catDesc
     , IF(nbl_books.bookISBN = $bookISBN, 1, 0) as isCurrentCategory
  FROM nbl_category
  LEFT 
  JOIN nbl_books 
    ON nbl_books.catID = nbl_category.catID 
   AND nbl_books.bookISBN = $bookISBN
 ORDER 
    BY catDesc
";

        
        $nblCat = $dbConn->query($sqlCat);

        while ($catRecord = $nblCat->fetchObject()) {
            $selected = $catRecord->isCurrentCategory ? 'selected' : '';
            echo "<option value='{$catRecord->catID}' {$selected}> {$catRecord->catDesc}</option>";
        }; 
?>
    </select>

对于您的发布者下拉菜单，您几乎做对了。只有您没有您正在展示的图书的出版商。您可以使用上面提出的相同策略：

<select name="PublisherID">
    <?php   
    
                $sqlPub = "
SELECT nbl_publisher.pubID
     , nbl_publisher.pubName
     , IF(nbl_books.bookISBN = $bookISBN, 1, 0) as isCurrentPublisher
  FROM nbl_publisher
  LEFT 
  JOIN nbl_books 
    ON nbl_books.pubID = nbl_publisher.pubID 
   AND nbl_books.bookISBN = $bookISBN
 ORDER 
    BY pubName
";
                
                $nblPub = $dbConn->query($sqlPub);

                while ($pubRecord = $nblPub->fetchObject()) {
                    $selected = $pubRecord->isCurrentPublisher ? 'selected' : '';
                    echo "<option value='{$pubRecord->pubID}' {$selected}>{$pubRecord->pubName}</option>";
                }; 
    ?>
            </select> <br>

编辑：

而且，正如 Strawberry 所建议的，您应该注意 SQL 注入，并且 PDO 增加了一些好处，例如参数作为模板。有关详细信息，请参阅本教程。

php - 如何显示显示当前值的数据库动态生成的选项列表

问题描述

解决方案

推荐阅读