MySQL与PHP中如何关联查询课程及用户课程通过状态？

嘿，我来帮你搞定这个问题！你的代码有几个小问题，而且嵌套查询的方式效率也不高，咱们一步一步来解决：

先说说你原代码里的几个明显错误 #

• 输出课程信息时，错误引用了不存在的字段：$row["firstname"]和$row["lastname"]，你的lessons表只有id、title、cat_id，应该用$row["title"]才对。
• 查询lessons_pass后输出状态时，你写了$result_two["status"]，但实际应该是$row_two["status"]——$row_two才是从结果集中取出的关联数组。
• 嵌套查询的方式会导致N+1查询问题：每查询一门课程就发起一次数据库请求，课程数量多的时候性能会很差。

更优的解决方案：用LEFT JOIN一次性查询 #

我们可以通过LEFT JOIN把两张表关联起来，一次查询就拿到所有课程和当前用户对应的状态，既高效又简洁，还能避免重复请求数据库。

优化后的SQL语句 #

SELECT l.id, l.title, l.cat_id, lp.status
FROM lessons l
LEFT JOIN lessons_pass lp ON l.id = lp.lesson_id AND lp.user_id = ?

这里用?做参数占位符，之后通过预处理语句绑定变量，能有效防止SQL注入。

修改后的PHP代码 #

<?php
$user_id = 4;

// 使用预处理语句，安全且高效
$sql = "SELECT l.id, l.title, l.cat_id, lp.status
        FROM lessons l
        LEFT JOIN lessons_pass lp ON l.id = lp.lesson_id AND lp.user_id = ?";

$stmt = $conn->prepare($sql);
$stmt->bind_param("i", $user_id); // "i"表示绑定的是整数类型的参数
$stmt->execute();
$result = $stmt->get_result();

if ($result->num_rows > 0) {
    // 遍历所有课程数据
    while($row = $result->fetch_assoc()) {
        echo "课程ID: " . $row["id"] . " - 课程名称: " . $row["title"] . "<br>";
        // 处理状态：如果用户没有该课程的记录，status会是NULL，这里可以自定义默认显示
        $status = isset($row["status"]) ? $row["status"] : "未完成";
        echo "用户学习状态: " . $status . "<br><br>";
    }
} else {
    echo "没有找到任何课程";
}

// 关闭语句和连接
$stmt->close();
$conn->close();
?>

这个方案的优势 #

1. 性能更好：只需要一次数据库请求，避免了循环嵌套查询的性能损耗。
2. 逻辑清晰：通过LEFT JOIN保证即使用户没有某门课的记录，课程信息依然会被返回，状态字段为NULL时可以轻松做默认处理。
3. 安全性高：使用预处理语句绑定参数，彻底避免SQL注入风险。
4. 兼容性强：用<?php替代<?，确保在所有PHP环境下都能正常运行。

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