PHP+MySQL票务剩余数量更新异常问题求助

问题现象 #

• 用户输入1-8的购票数量提交表单后，MySQL数据库tickets_finland表中的剩余票务数量无变化
• 曾出现点击购买按钮后，所有剩余票务被清空的异常情况

核心错误点 #

• 表单嵌套违规：HTML中存在外层<form>嵌套内部购票表单的情况，导致表单提交行为异常，无法正确传递购票数量参数
• 结果集读取错误：执行fetchAll()后立刻调用fetchColumn()，结果集被耗尽，$total_tickets_finland被赋值为false，后续计算时会出现false - 购票数量的非预期逻辑，最终导致票务被清空
• 数据未实时刷新：更新数据库后，页面显示的剩余票数仍使用旧数据，未重新查询最新状态
• 表结构不完整：id字段未设置主键和自增属性，可能导致数据匹配操作异常

修复后的PHP代码 #

<?php
session_start();
?>

<!DOCTYPE html>
<html>
<head>
    <title>WinterValley | Tickets</title>
    <style>
        <?php include '../style.css'; ?>
    </style>
</head>
<body>
<?php
try {
    // PDO连接配置
    $serverName = "localhost";
    $dbname = "wintervalley";
    $dBUsername = "root";
    $dBPassword = "";
    $charset = 'utf8mb4';

    $dsn = "mysql:host=$serverName;dbname=$dbname;charset=$charset";
    $options = [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES   => false,
    ];

    $pdo = new PDO($dsn, $dBUsername, $dBPassword, $options);
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}

// 处理购票提交逻辑
$message = '';
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['ticketQuantity_finland'])) {
        $ticketQuantity_finland = (int)$_POST['ticketQuantity_finland'];
        if ($ticketQuantity_finland >= 1 && $ticketQuantity_finland <= 8) {
            // 原子更新：直接在数据库层面扣除库存并判断余量，避免并发超卖
            $stmt = $pdo->prepare("UPDATE tickets_finland 
                                  SET quantity = quantity - :quantity 
                                  WHERE id = :id AND quantity >= :quantity");
            $stmt->bindParam(':quantity', $ticketQuantity_finland, PDO::PARAM_INT);
            $stmt->bindParam(':id', 1, PDO::PARAM_INT); // 替换为实际活动ID，若只有一条数据可固定为1
            $stmt->execute();

            // 检查是否有行被更新，判断库存是否充足
            if ($stmt->rowCount() === 0) {
                $message = "库存不足，无法完成购票";
            }
        } else {
            $message = "请输入1-8之间的有效数字";
        }
    }
}

// 查询最新票务数据
$stmt = $pdo->prepare("SELECT * FROM `tickets_finland` WHERE id = :id");
$stmt->bindParam(':id', 1, PDO::PARAM_INT);
$stmt->execute();
$ticket_fi = $stmt->fetch();
?>

<!-- logo -->
<a href="index.php"> <img src="../img/logo.png" alt="logo" /></a>
<h1>WinterValley</h1>

<!-- 登录/注册区域 -->
<?php
if (isset($_SESSION["useruid"])) {
    echo '<a class="log-out-button" href="../include/logout.inc.php">Log out</a>';
} else {
    include "../include/login.php";
?> <div class="empty2"></div> <?php
    include "../include/register.php";
}
?>
<div class="empty3"></div>
</div>
<!-- 导航栏 -->
<?php include "../include/navbar.php" ?>

<!-- 票务弹窗（芬兰站） -->
<div class="ticketPopupBox">
    <div class="ticketPopup" id="ticket_pop-up_finland">
        <div class="ticketContainer"> <!-- 外层form改为div，消除嵌套问题 -->
            <label for="ticketCheckbox">
                <h2 class="popup_ticket_title"><?php echo $ticket_fi['event_id']; ?></h2>
            </label>
            <div class="checkbox_info">
                <?php echo "€" . $ticket_fi['ticket_price'] . "<br>"; ?>
                <?php if (!empty($message)) echo "<p style='color:red;'>$message</p>"; ?>
                <form method="post" action=""> <!-- 提交到当前页面，避免路径错误 -->
                    <label for="ticketQuantity">购票数量:</label>
                    <input type="number" id="ticketQuantity_finland" name="ticketQuantity_finland" min="1" max="8" required>
                    <input type="submit" value="购买" class="btnTicket">
                </form>
                <?php echo $ticket_fi['quantity'] . " 张剩余" . "<br>"; ?>
                <div class="ticket_featuring">
                    <p>演出嘉宾:</p>
                    <ol>
                        <li>Sarah Brightman</li>
                        <li>Ed Sheeran</li>
                        <li>Kate Bush</li>
                        <li>Linkin Park</li>
                    </ol>
                </div>
                <button type="button" class="btn_cancelTicket" onclick="ticket_closePopupFinland()">关闭</button>
            </div>
        </div>
    </div>
</div>

<!-- 票务信息展示 -->
<div class="ticket_box">
    <h1 class="ticket_h1">即将举办的活动票务</h1>
    <div class="ticket_afstand">
        <div class="finland_bg">
            <div class="ticket_content">
                <h2 class="ticket_head">WinterValley Finland</h2>
                <ul>
                    <li>16:00-00:00</li>
                    <li>2023年2月25日</li>
                    <li>Eerikinkatu 3, 00100 Helsinki</li>
                    <div><button onclick="ticket_openPopupFinland()" class="ticket_button">立即购票</button></div>
                </ul>
            </div>
        </div>
    </div>
    <?php include "../include/footer.php" ?>

<script>
    // 芬兰站弹窗控制
    function ticket_openPopupFinland() {
        document.getElementById("ticket_pop-up_finland").style.display = "block";
    }

    function ticket_closePopupFinland() {
        document.getElementById("ticket_pop-up_finland").style.display = "none";
    }
</script>
</body>
</html>

修复后的MySQL建表语句 #

CREATE TABLE `tickets_finland` (
    `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `event_id` varchar(255) NOT NULL,
    `quantity` int(11) NOT NULL CHECK (`quantity` <= 2000), 
    `ticket_price` decimal(10,2) NOT NULL 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

修复说明 #

1. 消除表单嵌套：将外层表单标签改为<div>，避免浏览器解析异常导致的参数丢失
2. 原子更新操作：用单条SQL完成库存扣除和余量判断，减少查询次数同时避免并发超卖
3. 实时数据刷新：提交表单后重新查询数据库，确保页面显示最新剩余票数
4. 完善表结构：给id字段添加主键和自增属性，保证数据操作的唯一性
5. 优化提交路径：表单提交到当前页面，避免路径错误导致的参数传递失败

内容的提问来源于stack exchange，提问作者eddard