如何通过HTML复选框与下拉框从MySQL成功提取餐厅适配信息？

Hey there! Let's break down how to connect your HTML checkboxes (for food allergies) and dropdown (for cuisines) to your MySQL database using PHP, and retrieve the matching restaurant names. I'll cover everything from database setup to full code examples.

1. 先确认你的数据库表结构 #

First, let's make sure your database has a logical structure to store this data. Here's a recommended setup:

• restaurants: Stores basic restaurant info
  • id (INT, primary key, auto-increment)
  • name (VARCHAR, restaurant name)
  • cuisine (VARCHAR, e.g., "日式", "中式", "西式")
• allergies: Lists all possible allergy types
  • id (INT, primary key, auto-increment)
  • allergy_name (VARCHAR, e.g., "花生", "海鲜", "乳糖")
• restaurant_allergy_exclusions: Links restaurants to the allergies they can't accommodate (many-to-many relationship)
  • restaurant_id (INT, foreign key to restaurants.id)
  • allergy_id (INT, foreign key to allergies.id)

2. 创建HTML表单 #

Next, build the frontend form with checkboxes and a dropdown. The checkboxes need to use an array name (allergies[]) so we can capture multiple selections:

<form method="POST" action="process_restaurants.php">
  <h3>选择你的食物过敏类型</h3>
  <label><input type="checkbox" name="allergies[]" value="1"> 花生</label><br>
  <label><input type="checkbox" name="allergies[]" value="2"> 海鲜</label><br>
  <label><input type="checkbox" name="allergies[]" value="3"> 乳糖</label><br>
  <label><input type="checkbox" name="allergies[]" value="4"> 小麦</label><br>

  <h3>选择菜系</h3>
  <select name="cuisine">
    <option value="">请选择菜系</option>
    <option value="中式">中式</option>
    <option value="日式">日式</option>
    <option value="西式">西式</option>
    <option value="泰式">泰式</option>
  </select>

  <br><br>
  <button type="submit">查找适配餐厅</button>
</form>

Note: The value attributes of checkboxes should match the id values in your allergies table.

3. 编写PHP处理逻辑 (process_restaurants.php) #

Now, let's write the PHP code to handle the form submission, query the database, and output matching restaurants. We'll use prepared statements to prevent SQL injection (critical for security!):

<?php
// 完善数据库连接（替换成你的实际数据库信息）
$host = 'localhost';
$db_user = 'your_username';
$db_pass = 'your_password';
$db_name = 'your_database_name';

$con = mysqli_connect($host, $db_user, $db_pass, $db_name);

// 检查连接是否成功
if (mysqli_connect_errno()) {
    echo "数据库连接失败: " . mysqli_connect_error();
    exit();
}

// 处理表单提交
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // 获取表单数据
    $selected_allergies = isset($_POST['allergies']) ? $_POST['allergies'] : [];
    $selected_cuisine = isset($_POST['cuisine']) ? $_POST['cuisine'] : '';

    // 构建查询条件
    $conditions = [];
    $params = [];
    $types = '';

    // 添加菜系条件（如果用户选择了）
    if (!empty($selected_cuisine)) {
        $conditions[] = "r.cuisine = ?";
        $params[] = $selected_cuisine;
        $types .= 's'; // s表示字符串类型
    }

    // 添加过敏排除条件：查找不包含任何选中过敏的餐厅
    if (!empty($selected_allergies)) {
        // 创建占位符数组，比如 "?" 重复count($selected_allergies)次
        $placeholders = implode(',', array_fill(0, count($selected_allergies), '?'));
        $conditions[] = "r.id NOT IN (
            SELECT restaurant_id FROM restaurant_allergy_exclusions WHERE allergy_id IN ($placeholders)
        )";
        // 合并参数
        $params = array_merge($params, $selected_allergies);
        $types .= str_repeat('i', count($selected_allergies)); // i表示整数类型
    }

    // 构建完整SQL查询
    $sql = "SELECT r.name FROM restaurants r";
    if (!empty($conditions)) {
        $sql .= " WHERE " . implode(' AND ', $conditions);
    }

    // 使用预处理语句执行查询
    $stmt = mysqli_prepare($con, $sql);
    if ($stmt) {
        // 绑定参数
        if (!empty($params)) {
            mysqli_stmt_bind_param($stmt, $types, ...$params);
        }

        // 执行查询
        mysqli_stmt_execute($stmt);
        $result = mysqli_stmt_get_result($stmt);

        // 输出结果
        echo "<h3>适配的餐厅列表</h3>";
        if (mysqli_num_rows($result) > 0) {
            while ($row = mysqli_fetch_assoc($result)) {
                echo "- " . $row['name'] . "<br>";
            }
        } else {
            echo "没有找到符合条件的餐厅";
        }

        // 关闭语句
        mysqli_stmt_close($stmt);
    } else {
        echo "查询构建失败: " . mysqli_error($con);
    }
}

// 关闭数据库连接
mysqli_close($con);
?>

关键注意事项 #

• SQL Injection Protection: Always use prepared statements when dealing with user input (like form selections) to avoid malicious SQL attacks.
• Database Connection: Replace the placeholder values (your_username, your_password, etc.) with your actual database credentials.
• Empty Selections: The code handles cases where the user doesn't select any allergies or a cuisine—adjust the logic if you want to enforce required selections.
• Error Handling: The code includes basic error checking for connection and query issues; you can expand this for production use.

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