时间戳

首页 > 解决方案 > 使用下拉列表时 SQL 更新查询不更新

php - 使用下拉列表时 SQL 更新查询不更新

问题描述

我有 2 张桌子：product (id, name, quantity, c_id)和product_category (cat_id, cat_name).

我可以选择更新现有产品。当我更改名称和数量时，它工作得很好，但是当我尝试更改产品类别时，c_id它不会更改为新的。

更新页面的代码（update.php）：

<?php
    include 'database.php';
    $id = $_POST['productId'];
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "SELECT * FROM product where id = ?";
    $q = $pdo->prepare($sql);
    $q->execute(array($id));
    $data = $q->fetch(PDO::FETCH_ASSOC);
    $name = $data['name'];
    $quantity = $data['quantity'];

    Database::disconnect();
  ?>
    <form id="updateFrom" action="update2.php" method="POST">
    <table border="1" cellpadding="10">
        <tr align='center'>
            <td>Name</th>
            <td><input name="name" type="text" value="<?php echo $name;?>"/></td>
        </tr>
        <tr align='center'>
            <td>Quantity</th>
            <td><input name="quantity" type="text" value="<?php echo $quantity;?>"/></td>
        </tr>
        <tr align='center'>
        <?php  $cat = $pdo->query("SELECT c_name, CATEGORY_ID  FROM product_category");


        ?>
//Here the user selects the new category from the dropdown list
            <td>Category</th>
            <td>
                <select name="c_id"> 
                 <?php
                    while ($rows = $cat->fetch(PDO::FETCH_ASSOC))
                    {
                        $cat_name = $rows['c_name'];
                        $cat_id = $rows['CATEGORY_ID'];
                        echo"<option value='$cat_id'>$cat_name</option>";
                    }

                 ?>
                </select>

            </td>
        </tr>
    </table>
    <input type="hidden" id="productId" name="productId" value="<?php echo $id;?>"/>
    <button type="submit">update</button>
    </form>

</body>

进行更新（update2.php）的代码：

<?php
require 'database.php';

$id = null;

if ( !empty($_POST)) {
    $id = $_POST['productId'];
    $name = $_POST['name'];
    $quantity = $_POST['quantity'];

    // update data
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "UPDATE product set name = ?, quantity = ? WHERE id = ?";
    $q = $pdo->prepare($sql);
    $q->execute(array($name,$quantity,$id));
    Database::disconnect();
    header("Location: index.php");
}
?>

标签： phphtmlsqldrop-down-menu

解决方案

您需要从 设置c_id列$_POST['c_id']。

if ( !empty($_POST)) {
    $id = $_POST['productId'];
    $name = $_POST['name'];
    $quantity = $_POST['quantity'];
    $category = $_POST['c_id'];

    // update data
    $pdo = Database::connect();
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "UPDATE product set name = ?, quantity = ?, c_id = ? WHERE id = ?";
    $q = $pdo->prepare($sql);
    $q->execute(array($name,$quantity,$category,$id));
    Database::disconnect();
    header("Location: index.php");
}

我还建议您在下拉列表中默认选择现有类别。

                    while ($rows = $cat->fetch(PDO::FETCH_ASSOC))
                    {
                        $cat_name = $rows['c_name'];
                        $cat_id = $rows['CATEGORY_ID'];
                        $selected = $cat_id == $data['c_id'] ? "selected" : "";
                        echo "<option value='$cat_id' $selected>$cat_name</option>";
                    }

推荐阅读