Bird
Raised Fist0
PHPprogramming~5 mins

Prepared statements and why they matter in PHP - Time & Space Complexity

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Time Complexity: Prepared statements and why they matter
O(n)
Understanding Time Complexity

When using prepared statements in PHP, it's important to understand how the time to run your code changes as your data grows.

We want to see how the number of operations changes when preparing and executing statements multiple times.

Scenario Under Consideration

Analyze the time complexity of the following PHP code using prepared statements.


// Connect to database
$conn = new PDO($dsn, $user, $pass);

// Prepare once
$stmt = $conn->prepare('INSERT INTO users (name, email) VALUES (?, ?)');

// Execute multiple times
foreach ($users as $user) {
    $stmt->execute([$user['name'], $user['email']]);
}

This code prepares an SQL insert once, then runs it many times with different data.

Identify Repeating Operations

Look at what repeats as input grows.

  • Primary operation: Executing the prepared statement inside the loop.
  • How many times: Once for each user in the input list.
How Execution Grows With Input

As the number of users grows, the number of execute calls grows the same way.

Input Size (n)Approx. Operations
1010 execute calls
100100 execute calls
10001000 execute calls

Pattern observation: The work grows directly with the number of users; double the users, double the work.

Final Time Complexity

Time Complexity: O(n)

This means the time to run grows in a straight line with the number of inputs.

Common Mistake

[X] Wrong: "Preparing the statement inside the loop is just as fast as preparing once."

[OK] Correct: Preparing inside the loop repeats the setup work many times, making the program slower as input grows.

Interview Connect

Understanding how prepared statements affect performance shows you can write code that stays fast even with lots of data.

Self-Check

"What if we prepared the statement inside the loop instead of once before? How would the time complexity change?"

Practice

(1/5)
1. What is the main reason to use prepared statements in PHP when working with databases?
easy
A. To protect the database from SQL injection attacks
B. To make the code run faster by skipping queries
C. To store data permanently in variables
D. To automatically create database tables

Solution

  1. Step 1: Understand the purpose of prepared statements

    Prepared statements separate SQL code from user data, preventing malicious input from changing the query.

  2. Step 2: Identify the security benefit

    This separation protects against SQL injection, a common hacking method.

  3. Final Answer:

    To protect the database from SQL injection attacks -> Option A

  4. Quick Check:

    Security = Protect from SQL injection [OK]
Hint: Prepared statements stop hackers from changing your queries [OK]
Common Mistakes:
  • Thinking prepared statements speed up queries significantly
  • Confusing prepared statements with data storage
  • Believing prepared statements create tables automatically
2. Which of the following is the correct syntax to prepare a statement using PDO in PHP?
easy
A. $stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?');
B. $stmt = $pdo->query('SELECT * FROM users WHERE id = ?');
C. $stmt = $pdo->execute('SELECT * FROM users WHERE id = ?');
D. $stmt = $pdo->fetch('SELECT * FROM users WHERE id = ?');

Solution

  1. Step 1: Recall PDO method for preparing statements

    The correct method to prepare a statement is prepare(), which sets up the SQL with placeholders.

  2. Step 2: Check the options for correct method usage

    Only $stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?'); uses prepare() correctly with a SQL string containing a placeholder.

  3. Final Answer:

    $stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?'); -> Option A

  4. Quick Check:

    PDO prepare() method = $stmt = $pdo->prepare('SELECT * FROM users WHERE id = ?'); [OK]
Hint: Use prepare() to set up SQL with placeholders [OK]
Common Mistakes:
  • Using query() instead of prepare() for statements with placeholders
  • Trying to execute SQL directly with execute() without preparing
  • Confusing fetch() with prepare()
3. What will be the output of the following PHP code snippet?
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE users (id INTEGER, name TEXT)');
$stmt = $pdo->prepare('INSERT INTO users (id, name) VALUES (?, ?)');
$stmt->execute([1, 'Alice']);
$stmt->execute([2, 'Bob']);
$stmt = $pdo->prepare('SELECT name FROM users WHERE id = ?');
$stmt->execute([2]);
echo $stmt->fetchColumn();
medium
A. 1
B. Alice
C. Bob
D. Error

Solution

  1. Step 1: Insert two users into the table

    The code inserts two rows: id=1 with name 'Alice' and id=2 with name 'Bob'.

  2. Step 2: Select the name where id = 2

    The prepared statement selects the name for id=2, which is 'Bob'.

  3. Final Answer:

    Bob -> Option C

  4. Quick Check:

    Selected name for id=2 = Bob [OK]
Hint: Check which id is queried to find the correct name [OK]
Common Mistakes:
  • Confusing the order of inserted rows
  • Expecting the first inserted name instead of the queried one
  • Assuming an error due to missing database file
4. Identify the error in the following PHP code using prepared statements:
$stmt = $pdo->prepare('SELECT * FROM users WHERE email = :email');
$stmt->execute('user@example.com');
$result = $stmt->fetchAll();
medium
A. The SQL query is missing a WHERE clause
B. The prepare() method is missing a placeholder
C. fetchAll() cannot be used after execute()
D. The execute() method should receive an array, not a string

Solution

  1. Step 1: Check the execute() method argument

    When using named placeholders like ':email', execute() expects an associative array with keys matching placeholders.

  2. Step 2: Identify the incorrect argument type

    The code passes a string directly instead of an array like ['email' => 'user@example.com'].

  3. Final Answer:

    The execute() method should receive an array, not a string -> Option D

  4. Quick Check:

    execute() needs array for named placeholders [OK]
Hint: Pass an array to execute() when using named placeholders [OK]
Common Mistakes:
  • Passing a string instead of an array to execute()
  • Forgetting to match placeholder names in the array keys
  • Thinking fetchAll() cannot follow execute()
5. You want to insert multiple user records safely using prepared statements in PHP. Which approach below correctly uses prepared statements to insert two users with ids and names?
hard
A. $stmt = $pdo->prepare('INSERT INTO users (id, name) VALUES (?, ?)'); $stmt->execute(1, 'Alice'); $stmt->execute(2, 'Bob');
B. $stmt = $pdo->prepare('INSERT INTO users (id, name) VALUES (:id, :name)'); $stmt->execute(['id' => 1, 'name' => 'Alice']); $stmt->execute(['id' => 2, 'name' => 'Bob']);
C. $pdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob')");
D. $stmt = $pdo->prepare('INSERT INTO users (id, name) VALUES (:id, :name)'); $stmt->execute(['id' => 1, 'name' => 'Alice', 'id' => 2, 'name' => 'Bob']);

Solution

  1. Step 1: Understand how to use named placeholders for multiple inserts

    Using named placeholders (:id, :name) allows binding values clearly for each execute call.

  2. Step 2: Check each option for correct usage

    Only $stmt = $pdo->prepare('INSERT INTO users (id, name) VALUES (:id, :name)'); $stmt->execute(['id' => 1, 'name' => 'Alice']); $stmt->execute(['id' => 2, 'name' => 'Bob']); correctly prepares once and executes twice with separate associative arrays for each user.

  3. Step 3: Identify errors in other options

    $stmt = $pdo->prepare('INSERT INTO users (id, name) VALUES (?, ?)'); $stmt->execute(1, 'Alice'); $stmt->execute(2, 'Bob'); incorrectly passes parameters directly to execute() without an array; $pdo->exec("INSERT INTO users (id, name) VALUES (1, 'Alice'), (2, 'Bob')"); uses exec() without preparing; $stmt = $pdo->prepare('INSERT INTO users (id, name) VALUES (:id, :name)'); $stmt->execute(['id' => 1, 'name' => 'Alice', 'id' => 2, 'name' => 'Bob']); tries to pass multiple users in one execute call incorrectly.

  4. Final Answer:

    $stmt = $pdo->prepare('INSERT INTO users (id, name) VALUES (:id, :name)'); $stmt->execute(['id' => 1, 'name' => 'Alice']); $stmt->execute(['id' => 2, 'name' => 'Bob']); -> Option B

  5. Quick Check:

    Named placeholders + separate execute calls = $stmt = $pdo->prepare('INSERT INTO users (id, name) VALUES (:id, :name)'); $stmt->execute(['id' => 1, 'name' => 'Alice']); $stmt->execute(['id' => 2, 'name' => 'Bob']); [OK]
Hint: Use named placeholders and separate execute() calls per record [OK]
Common Mistakes:
  • Passing multiple records in one execute call incorrectly
  • Not using prepared statements and risking SQL injection
  • Passing parameters to execute() without an array for positional placeholders
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard