PHP PDO : Can't bind value to multiple variables?

Question

我正在尝试通过以下查询执行一个使用相同外键连接多个表的查询，但它返回 false。

$question_id = 11406;

$query = $db->prepare("SELECT q.question_attempts_permitted, q.question_range, a.answer_text, r.attempt_count 
                           FROM checkup_questions q, checkup_answers a, user_responses r 
                           WHERE a.question_id=:question_id AND q.question_id=:question_id AND r.question_id=:question_id");
$query->bindValue(':question_id', $question_id, PDO::PARAM_INT);
$query->execute();

但是，如果我直接注入(inject) question_id，查询将返回所需的结果。

$query = $db->prepare("SELECT q.question_attempts_permitted, q.question_range, a.answer_text, r.attempt_count 
                       FROM checkup_questions q, checkup_answers a, user_responses r 
                       WHERE a.question_id=11406 AND q.question_id=11406 AND r.question_id=11406");
$query->execute();

bindValue 接口(interface)是否存在某些限制导致第一个查询失败而第二个查询按预期返回？

Answer 1

应使用JOIN 重写查询文本:

$query = $db->prepare("
    SELECT q.question_attempts_permitted, q.question_range, a.answer_text, r.attempt_count 
    FROM checkup_questions q 
    JOIN checkup_answers a ON a.question_id = q.question_id
    JOIN user_responses r ON r.question_id = q.question_id
    WHERE q.question_id=:question_id
");
// you can provide placeholder without `:`
$query->bindValue('question_id', $question_id, PDO::PARAM_INT);
$query->execute();

这里只有一个占位符。