优化多选框数据存储：数据库设计与PHP处理实践

<div class="images">
  <ul>
    <li><input type="checkbox" id="cb1" name="hotel[]" value="casamontanha" />
      <label for="cb1" class="lblimgs"><img src="pictures/casamontanha.jpg" /></label>
    </li>
    <li><input type="checkbox" id="cb2" name="hotel[]" value="fillitheyo" />
      <label for="cb2" class="lblimgs"><img src="pictures/filitheyo.jpg" /></label>
    </li>
    <!-- 更多复选框... -->
  </ul>
</div>

<?php
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    if (isset($_POST['hotel']) && is_array($_POST['hotel'])) {
        $selectedHotels = $_POST['hotel'];
        echo "选中的酒店有: ";
        foreach ($selectedHotels as $hotelValue) {
            echo $hotelValue . ", ";
        }
        // $selectedHotels 现在是一个包含所有选中值的数组，例如: ['casamontanha', 'fillitheyo']
        // 接下来可以将其存储到数据库
    } else {
        echo "没有选择任何酒店。";
    }
}
?>

// 错误示例：这种方式无法正确处理多选值
$cb1=$_POST['hotel']; // 如果hotel是数组，这里会报错或行为异常
$cbx1="";
foreach($checkbox1 as $cbx1) // $checkbox1 未定义
{
    $cb1 .= $cbx1.",";
}

<?php
// 假设已连接数据库 $pdo
// 假设当前用户ID为 $currentUserId = 1;
// 假设用户提交的选中酒店值为 $selectedHotels = ['casamontanha', 'fillitheyo'];

// 1. 清除该用户旧的酒店选择 (可选，如果每次都是完全替换)
$stmt = $pdo->prepare("DELETE FROM user_hotels WHERE user_id = :user_id");
$stmt->execute([':user_id' => $currentUserId]);

// 2. 准备插入新的选择
$insertStmt = $pdo->prepare("INSERT INTO user_hotels (user_id, hotel_id) VALUES (:user_id, :hotel_id)");

foreach ($selectedHotels as $hotelValue) {
    // 根据hotel_value查询对应的hotel_id
    $hotelIdStmt = $pdo->prepare("SELECT id FROM hotels WHERE hotel_value = :hotel_value");
    $hotelIdStmt->execute([':hotel_value' => $hotelValue]);
    $hotel = $hotelIdStmt->fetch(PDO::FETCH_ASSOC);

    if ($hotel) {
        $hotelId = $hotel['id'];
        // 插入到中间表
        $insertStmt->execute([
            ':user_id' => $currentUserId,
            ':hotel_id' => $hotelId
        ]);
    } else {
        // 处理未找到酒店的情况，可能需要记录日志或报错
        error_log("未找到对应的酒店值: " . $hotelValue);
    }
}
echo "酒店选择已成功保存。";
?>

SELECT h.hotel_name, h.hotel_value
FROM hotels h
JOIN user_hotels uh ON h.id = uh.hotel_id
WHERE uh.user_id = :user_id;

SELECT u.username
FROM users u
JOIN user_hotels uh ON u.id = uh.user_id
JOIN hotels h ON uh.hotel_id = h.id
WHERE h.hotel_value = 'casamontanha';

<?php
// 假设 $selectedHotels 是从表单获取的数组，例如 ['casamontanha', 'fillitheyo']

// 将数组转换为JSON字符串
$jsonString = json_encode($selectedHotels);

// 然后将 $jsonString 存储到数据库的某个列中
// 例如：UPDATE users SET selected_hotels_json = :jsonString WHERE id = :userId
$stmt = $pdo->prepare("UPDATE users SET selected_hotels_json = :jsonString WHERE id = :userId");
$stmt->execute([':jsonString' => $jsonString, ':userId' => $currentUserId]);

echo "酒店选择已通过JSON保存。";
?>

<?php
// 假设从数据库中读取到 $jsonString，例如 '["casamontanha","fillitheyo"]'

// 将JSON字符串解码为PHP数组
$retrievedHotels = json_decode($jsonString, true); // true表示解码为关联数组，此处为索引数组

if (is_array($retrievedHotels)) {
    echo "从数据库读取的酒店选择: ";
    foreach ($retrievedHotels as $hotelValue) {
        echo $hotelValue . ", ";
    }
} else {
    echo "无法解码JSON数据。";
}
?>