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.

Practice

1. Which SQL statement is used in PHP to add new data into a database table?

easy

A. UPDATE

B. INSERT INTO

C. DELETE FROM

D. SELECT

Solution

1. Step 1: Understand the purpose of each SQL command

   INSERT INTO adds new rows, UPDATE changes existing rows, DELETE FROM removes rows, and SELECT retrieves data.

2. Step 2: Match the command to adding new data

   Since adding new data means inserting new rows, the correct command is INSERT INTO.

3. Final Answer:

   INSERT INTO -> Option B

4. Quick Check:

   Insert = Add new data [OK]

Hint: Insert adds new rows; update and delete modify existing ones [OK]

Common Mistakes:

• Confusing UPDATE with INSERT
• Thinking DELETE adds data
• Using SELECT to add data

2. Which of the following is the correct PHP syntax to update a user's email in a MySQL database using mysqli_query?

easy

A. $sql = "DELETE FROM users SET email = '[e-mail]' WHERE id = 1";

B. $sql = "INSERT INTO users email = '[e-mail]' WHERE id = 1";

C. $sql = "SELECT email = '[e-mail]' FROM users WHERE id = 1";

D. $sql = "UPDATE users SET email = '[e-mail]' WHERE id = 1";

Solution

1. Step 1: Identify the correct SQL syntax for updating

   The correct syntax for updating is UPDATE table SET column = value WHERE condition.

2. Step 2: Check each option's syntax

   $sql = "UPDATE users SET email = '[e-mail]' WHERE id = 1"; matches the correct syntax. Options A, B, and D misuse SQL commands or syntax.

3. Final Answer:

   $sql = "UPDATE users SET email = '[e-mail]' WHERE id = 1"; -> Option D

4. Quick Check:

   Update syntax = UPDATE ... SET ... WHERE ... [OK]

Hint: Update uses SET with WHERE to change specific rows [OK]

Common Mistakes:

• Using INSERT syntax for update
• Omitting WHERE clause in update
• Using DELETE or SELECT instead of UPDATE

3. What will be the output of the following PHP code snippet?

$conn = new mysqli('localhost', 'user', 'pass', 'db');
$sql = "DELETE FROM products WHERE id = 10";
if ($conn->query($sql) === TRUE) {
    echo "Deleted successfully";
} else {
    echo "Error deleting record";
}

medium

A. No output

B. Error deleting record

C. Deleted successfully

D. Syntax error

Solution

1. Step 1: Understand the code logic

   The code tries to delete a product with id 10. If the query runs without error, it prints "Deleted successfully".

2. Step 2: Assume the query runs correctly

   Since the SQL syntax is correct and connection is assumed valid, the query returns TRUE, so the success message prints.

3. Final Answer:

   Deleted successfully -> Option C

4. Quick Check:

   Successful DELETE query prints success message [OK]

Hint: If query returns TRUE, success message prints [OK]

Common Mistakes:

• Assuming syntax error without checking code
• Confusing TRUE with FALSE return values
• Ignoring the if-else structure

4. Identify the error in this PHP code that tries to update a user's name:

$sql = "UPDATE users SET name = 'John'";
$conn->query($sql);

medium

A. Missing WHERE clause to specify which user to update

B. Incorrect SQL keyword, should be INSERT

C. Quotes around 'John' should be double quotes

D. Missing semicolon after query execution

Solution

1. Step 1: Review the SQL update statement

   The statement updates the name but does not specify which row to update because it lacks a WHERE clause.

2. Step 2: Understand the impact of missing WHERE

   Without WHERE, all rows in the table will be updated, which is usually unintended and considered an error.

3. Final Answer:

   Missing WHERE clause to specify which user to update -> Option A

4. Quick Check:

   Update without WHERE affects all rows [OK]

Hint: Always include WHERE in update to target specific rows [OK]

Common Mistakes:

• Thinking quotes type causes error
• Confusing UPDATE with INSERT
• Ignoring missing WHERE consequences

5. You want to delete all users from the users table who have not logged in for over a year. Which PHP code snippet correctly performs this operation assuming the last login date is stored in last_login column?

hard

A. $sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)";
$conn->query($sql);

B. $sql = "UPDATE users SET last_login = NULL WHERE last_login > DATE_SUB(NOW(), INTERVAL 1 YEAR)";
$conn->query($sql);

C. $sql = "DELETE FROM users WHERE last_login > DATE_ADD(NOW(), INTERVAL 1 YEAR)";
$conn->query($sql);

D. $sql = "INSERT INTO users (last_login) VALUES (DATE_SUB(NOW(), INTERVAL 1 YEAR))";
$conn->query($sql);

Solution

1. Step 1: Understand the condition for deletion

   We want to delete users whose last_login is older than one year, so the condition is last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR).

2. Step 2: Check each option's SQL logic

   $sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); correctly deletes users with last login older than one year. $sql = "UPDATE users SET last_login = NULL WHERE last_login > DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); updates instead of deletes. $sql = "DELETE FROM users WHERE last_login > DATE_ADD(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); deletes users with last login in the future, which is wrong. $sql = "INSERT INTO users (last_login) VALUES (DATE_SUB(NOW(), INTERVAL 1 YEAR))"; $conn->query($sql); inserts new rows, which is incorrect.

3. Final Answer:

   $sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); -> Option A

4. Quick Check:

   Delete with condition older than 1 year = $sql = "DELETE FROM users WHERE last_login < DATE_SUB(NOW(), INTERVAL 1 YEAR)"; $conn->query($sql); [OK]

Hint: Use DELETE with WHERE and DATE_SUB for time conditions [OK]

Common Mistakes:

• Using UPDATE instead of DELETE
• Wrong date comparison operators
• Using INSERT instead of DELETE