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

Question

I am trying to execute a query that joins several tables using the same foreign key via the below query but it returns 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();

However, if I inject the question_id directly the query returns the desired result.

$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();

Is there some limitation with the bindValue interface that causes the first query to fail while the second one returns as expected?

ah ok. so how does one handle a situation in which the same value needs to be referenced multiple times such as the above example? — user1392897, Apr 27 '19 at 19:19
Use different placeholders, isn't it? Or a correct `JOIN` in the query. — u_mulder, Apr 27 '19 at 19:20
In this specific case: `WHERE a.question_id=:question_id AND q.question_id=a.question_id AND r.question_id=a.question_id`. You should though use `JOIN` syntax - Then the solution might be obvious. — Paul Spiegel, Apr 27 '19 at 19:24
@u_mulder - thank you as well. I did not understand what you meant by correct JOIN at first but I get it now. — user1392897, Apr 27 '19 at 19:29
See: [PDO Parameterized Query - Reuse named placeholders?](https://stackoverflow.com/questions/2432084/pdo-parameterized-query-reuse-named-placeholders) — Paul Spiegel, Apr 27 '19 at 19:29

score 3 · Accepted Answer · answered Apr 27 '19 at 19:29

Query text should be rewritten using 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();

Here you have only one placeholder.

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

1 Answers1