Binding parameters in PHP - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of prepared statement with bound parameters

What is the output of this PHP code snippet that uses PDO to bind parameters and execute a query?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec("INSERT INTO users (name) VALUES ('Alice'), ('Bob')");
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = :id');
$id = 2;
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$stmt->execute();
echo $stmt->fetchColumn();
?>

A Alice

B SyntaxError

C NULL

D Bob

💡 Hint

Remember that bindParam binds the variable by reference and the value of $id is 2.

The query selects the user with id = 2, which is 'Bob'. The bindParam binds the variable $id by reference, so the value 2 is used when executing.

❓ Predict Output

intermediate

Effect of changing variable after bindParam

What will this PHP code output when using bindParam and changing the variable after binding but before execution?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE items (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec("INSERT INTO items (name) VALUES ('Pen'), ('Pencil')");
$stmt = $pdo->prepare('SELECT name FROM items WHERE id = :id');
$id = 1;
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
$id = 2;
$stmt->execute();
echo $stmt->fetchColumn();
?>

A NULL

B Pencil

C Pen

D Fatal error

💡 Hint

bindParam binds by reference, so the value at execution time matters.

Because bindParam binds the variable by reference, changing $id to 2 before execute means the query uses id=2, which corresponds to 'Pencil'.

🔧 Debug

advanced

Why does this bindValue code not update with variable change?

Consider this PHP code using bindValue. Why does changing the variable after binding not affect the executed query?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT)');
$pdo->exec("INSERT INTO products (name) VALUES ('Table'), ('Chair')");
$stmt = $pdo->prepare('SELECT name FROM products WHERE id = :id');
$id = 1;
$stmt->bindValue(':id', $id, PDO::PARAM_INT);
$id = 2;
$stmt->execute();
echo $stmt->fetchColumn();
?>

A bindValue copies the value at binding time, so changing $id later has no effect.

B bindValue binds by reference, so the query uses the updated $id value.

C The code throws a runtime error because $id is changed after binding.

D The query returns NULL because bindValue requires execute parameters.

💡 Hint

bindValue copies the value immediately, unlike bindParam.

bindValue binds the value at the time of binding, so changing the variable later does not affect the query. The query uses id=1 and returns 'Table'.

📝 Syntax

advanced

Identify the syntax error in this bindParam usage

Which option shows the correct way to bind a parameter using bindParam in PHP? The others contain syntax errors.

A $stmt->bindParam(':name', $name, PDO::PARAM_STR);

B $stmt->bindParam(':name' $name, PDO::PARAM_STR);

C $stmt->bindParam(:name, $name, PDO::PARAM_STR);

D $stmt->bindParam(':name', $name PDO::PARAM_STR);

💡 Hint

Check for missing commas and quotes around parameter names.

Option A correctly uses quotes around ':name' and commas between arguments. Options B, C, and D have missing commas or missing quotes causing syntax errors.

🚀 Application

expert

How many rows will be updated with this bindParam loop?

Given this PHP code that updates multiple rows using bindParam inside a loop, how many rows will be updated after execution?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE scores (id INTEGER PRIMARY KEY, score INTEGER)');
$pdo->exec("INSERT INTO scores (score) VALUES (10), (20), (30)");
$stmt = $pdo->prepare('UPDATE scores SET score = :score WHERE id = :id');
$stmt->bindParam(':score', $score, PDO::PARAM_INT);
$stmt->bindParam(':id', $id, PDO::PARAM_INT);
for ($i = 1; $i <= 3; $i++) {
    $id = $i;
    $score = $i * 100;
    $stmt->execute();
}
$count = $pdo->query('SELECT COUNT(*) FROM scores WHERE score >= 100')->fetchColumn();
echo $count;
?>

A 0

B SyntaxError

C 3

D 1

💡 Hint

bindParam binds variables by reference, so changing them in the loop affects each execute call.

The loop updates each row with id 1, 2, and 3, setting scores to 100, 200, and 300 respectively. All three rows have score >= 100 after the loop, so the count is 3.