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
– user1392897, Commented Apr 27, 2019 at 19:19
Use different placeholders, isn't it? Or a correct JOIN in the query. — u_mulder
– u_mulder, Commented Apr 27, 2019 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
– Paul Spiegel, Commented Apr 27, 2019 at 19:24

u_mulder · Accepted Answer · 2019-04-27 19:29:27Z

3

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.

answered Apr 27, 2019 at 19:29

u_mulder

54.8k5 gold badges54 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related