Insert, update, delete operations in PHP - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this PHP insert operation?

Consider the following PHP code that inserts a new user into a database. What will be the output?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);");
$stmt = $pdo->prepare("INSERT INTO users (name) VALUES (:name)");
$result = $stmt->execute([':name' => 'Alice']);
echo $result ? 'Insert successful' : 'Insert failed';
?>

A Fatal error: Uncaught exception

B Insert successful

C Insert failed

D Syntax error

💡 Hint

Check if the execute() method returns true on success.

The execute() method returns true if the insert was successful, so the output is 'Insert successful'.

❓ Predict Output

intermediate

What is the output after this update operation?

This PHP code updates a user's name in the database. What will be the output?

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 ('Bob');");
$stmt = $pdo->prepare("UPDATE users SET name = :name WHERE id = :id");
$success = $stmt->execute([':name' => 'Robert', ':id' => 1]);
echo $success ? $stmt->rowCount() : 0;
?>

A Syntax error

B 0

C 1

D false

💡 Hint

rowCount() returns the number of rows affected by the update.

The update affects one row, so rowCount() returns 1 and the output is '1'.

❓ Predict Output

advanced

What error does this PHP delete operation raise?

Look at this PHP code that tries to delete a user. What error will it produce?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);");
$stmt = $pdo->prepare("DELETE FROM users WHERE id = ?");
$stmt->execute();
?>

A PDOException: SQLSTATE[HY093]: Invalid parameter number

B No error, deletes all rows

C Syntax error

D Fatal error: Missing argument

💡 Hint

Check if the execute() method is called with the required parameter.

The prepared statement expects one parameter, but execute() is called without arguments, causing an invalid parameter number error.

🧠 Conceptual

advanced

Which option correctly updates multiple rows in PHP?

You want to update the status of all users older than 30 to 'active'. Which PHP code snippet does this correctly?

A $stmt = $pdo->prepare("UPDATE users SET status = 'active' WHERE age > 30"); $stmt->execute();

B $stmt = $pdo->prepare("UPDATE users SET status = :status WHERE age > :age"); $stmt->execute(['status' => 'active']);

C $stmt = $pdo->prepare("UPDATE users SET status = 'active' WHERE age > 30"); $stmt->execute(['active', 30]);

D $stmt = $pdo->prepare("UPDATE users SET status = :status WHERE age > :age"); $stmt->execute(['active', 30]);

💡 Hint

Check if the parameters match the placeholders in the query.

Option A uses no placeholders and executes directly, which works. Options A and D have missing or mismatched parameters. Option A passes parameters but the query has no placeholders.

❓ Predict Output

expert

What is the output of this PHP code with transactions?

This PHP code uses a transaction to insert and then delete a user. What will be the output?

PHP

<?php
$pdo = new PDO('sqlite::memory:');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);");
try {
  $pdo->beginTransaction();
  $pdo->exec("INSERT INTO users (name) VALUES ('Charlie');");
  $pdo->exec("DELETE FROM users WHERE name = 'Charlie';");
  $pdo->commit();
  $count = $pdo->query("SELECT COUNT(*) FROM users;")->fetchColumn();
  echo $count;
} catch (Exception $e) {
  $pdo->rollBack();
  echo 'Failed';
}
?>

A Syntax error

B 1

C Failed

D 0

💡 Hint

Think about what happens when you insert and then delete the same row inside a transaction.

The insert adds one row, then the delete removes it before commit. So the final count is 0.