PHP实现票务预约JSON数据按列展示及选择结果回传方案求助

需求概述 #

• 功能模块：实现预约日期、预约时段、可预约余量全流程管理，时段默认总可预约量为60，用户每成功预约1次对应时段余量减1
• 展示规则：通过PHP读取数据库数据，按日期分列展示，列头为预约日期，单元格内展示对应时段及当前余量/总可预约量
• 数据交互：用户完成选择后，将预约数据以JSON格式提交到后端API，更新数据库余量的同时绑定用户IP存储预约记录

现有基础代码 #

<?php
    try{
        $pdo=new PDO(
            'mysql:host=localhost;dbname=date_booking',
            'root','',
            array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
    } catch(PDOException $e){
        echo $e->getMessage();
    }

    $statement=$pdo->prepare("SELECT * FROM tbl_booking");
    $statement->execute();
    $datas = array();
    while($res=$statement->fetch(PDO::FETCH_ASSOC)) {
        $datas[]=$res;
    }
    $someArray = $datas; // Replace ... with your PHP Array
    foreach ($someArray as $key => $value) {
        echo '<pre>';
        echo $value["date_booking"]. ' | ' . $value["hour_booking"]. ' | ' . $value["nb_booking"];
    echo '</pre>'; 
    }
?>

实现思路 #

1. 数据分组处理 #

首先将从数据库查询到的一维数据按预约日期分组，方便后续按日期生成列：

// 数据分组
$groupData = [];
foreach ($datas as $item) {
    $groupData[$item['date_booking']][] = $item;
}

2. 前端页面渲染 #

用flex布局实现多列展示，循环遍历分组后的数据生成对应列和时段单元格：

<style>
.booking-container {display: flex;gap: 20px;overflow-x: auto;padding: 20px;}
.date-column {min-width: 150px;border: 1px solid #eee;border-radius: 8px;}
.date-header {padding: 10px;text-align: center;background: #f5f5f5;font-weight: bold;border-radius: 8px 8px 0 0;}
.time-item {padding: 10px;border-bottom: 1px solid #eee;cursor: pointer;}
.time-item.disable {background: #f0f0f0;cursor: not-allowed;color: #999;}
.time-item.selected {background: #e3f2fd;color: #1976d2;}
</style>
<div class="booking-container">
    <?php foreach ($groupData as $date => $timeList): ?>
    <div class="date-column">
        <div class="date-header"><?= $date ?></div>
        <?php foreach ($timeList as $time): ?>
        <div class="time-item <?= $time['nb_booking'] <=0 ? 'disable' : '' ?>" 
             data-date="<?= $date ?>" 
             data-time="<?= $time['hour_booking'] ?>" 
             data-remaining="<?= $time['nb_booking'] ?>">
            <div><?= $time['hour_booking'] ?></div>
            <div><?= $time['nb_booking'] ?>/60</div>
        </div>
        <?php endforeach; ?>
    </div>
    <?php endforeach; ?>
</div>
<button id="submitBtn" style="margin: 20px;padding: 10px 20px;">提交预约</button>

3. 交互逻辑实现 #

用JS实现选择、校验、提交逻辑：

// 选中状态管理
const selected = [];
document.querySelectorAll('.time-item:not(.disable)').forEach(item => {
    item.addEventListener('click', () => {
        const date = item.dataset.date;
        const time = item.dataset.time;
        const key = `${date}-${time}`;
        if (item.classList.contains('selected')) {
            // 取消选中
            item.classList.remove('selected');
            const index = selected.findIndex(s => s.key === key);
            if (index > -1) selected.splice(index, 1);
        } else {
            // 新增选中
            item.classList.add('selected');
            selected.push({
                key: key,
                date: date,
                time: time
            });
        }
    })
})
// 提交逻辑
document.getElementById('submitBtn').addEventListener('click', () => {
    if (selected.length === 0) {
        alert('请至少选择一个时段');
        return;
    }
    fetch('/api/submit-booking.php', {
        method: 'POST',
        headers: {'Content-Type': 'application/json'},
        body: JSON.stringify({
            booking_list: selected
        })
    }).then(res => res.json()).then(res => {
        if (res.code === 0) {
            alert('预约成功');
            window.location.reload();
        } else {
            alert(res.msg || '预约失败');
        }
    })
})

4. 后端提交接口实现 #

新建submit-booking.php处理提交请求，做数据校验、余量更新、预约记录存储：

<?php
header('Content-Type': 'application/json');
// 数据库连接代码同上，此处省略
// 获取用户IP
$userIp = $_SERVER['REMOTE_ADDR'];
// 接收参数
$raw = file_get_contents('php://input');
$data = json_decode($raw, true);
$bookingList = $data['booking_list'] ?? [];
if (empty($bookingList)) {
    echo json_encode(['code' => 1, 'msg' => '参数错误']);
    exit;
}
try {
    $pdo->beginTransaction();
    foreach ($bookingList as $item) {
        // 先校验余量，加行锁防止超卖
        $stmt = $pdo->prepare("SELECT nb_booking FROM tbl_booking WHERE date_booking = ? AND hour_booking = ? FOR UPDATE");
        $stmt->execute([$item['date'], $item['time']]);
        $remaining = $stmt->fetchColumn();
        if ($remaining <= 0) {
            throw new Exception("{$item['date']} {$item['time']} 时段已约满");
        }
        // 更新余量
        $stmt = $pdo->prepare("UPDATE tbl_booking SET nb_booking = nb_booking - 1 WHERE date_booking = ? AND hour_booking = ?");
        $stmt->execute([$item['date'], $item['time']]);
        // 写入预约记录 假设表名为tbl_booking_log 字段为ip、date_booking、hour_booking、create_time
        $stmt = $pdo->prepare("INSERT INTO tbl_booking_log (ip, date_booking, hour_booking, create_time) VALUES (?, ?, ?, ?)");
        $stmt->execute([$userIp, $item['date'], $item['time'], date('Y-m-d H:i:s')]);
    }
    $pdo->commit();
    echo json_encode(['code' => 0, 'msg' => '预约成功']);
} catch (Exception $e) {
    $pdo->rollBack();
    echo json_encode(['code' => 1, 'msg' => $e->getMessage()]);
}
?>

注意事项 #

• 数据库查询加行锁FOR UPDATE防止超卖
• 可以加IP预约限制，避免同一IP重复预约同一时段
• 前端校验仅做体验优化，所有校验逻辑后端必须再做一次

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