Executing queries with query method in PHP - Time & Space Complexity

Time Complexity: Executing queries with query method

O(n)

Understanding Time Complexity

When running database queries using the query method, it's important to understand how the time it takes grows as the data or query size grows.

We want to know how the number of operations changes when the input gets bigger.

Scenario Under Consideration

Analyze the time complexity of the following code snippet.

// Connect to database
$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass');

// Execute a query
$result = $pdo->query('SELECT * FROM users');

// Fetch all rows
$rows = $result->fetchAll(PDO::FETCH_ASSOC);

// Process rows
foreach ($rows as $row) {
    echo $row['name'] . "\n";
}
    

This code runs a SQL query to get all users, fetches all results, and then prints each user's name.

Identify Repeating Operations

Identify the loops, recursion, array traversals that repeat.

• Primary operation: Looping through all rows returned by the query.
• How many times: Once for each row in the users table.

How Execution Grows With Input

As the number of users grows, the time to fetch and print each user grows too.

Input Size (n)	Approx. Operations
10	About 10 fetch and print steps
100	About 100 fetch and print steps
1000	About 1000 fetch and print steps

Pattern observation: The work grows directly with the number of rows; doubling rows doubles work.

Final Time Complexity

Time Complexity: O(n)

This means the time to run and process the query grows in a straight line with the number of rows returned.

Common Mistake

[X] Wrong: "The query method runs instantly no matter how many rows are returned."

[OK] Correct: Fetching and processing each row takes time, so more rows mean more work and longer time.

Interview Connect

Understanding how query execution time grows helps you write efficient database code and explain your reasoning clearly in interviews.

Self-Check

"What if we used a query with a WHERE clause that limits results to a fixed number? How would the time complexity change?"

Practice

1. What does the query method in PHP do when used with a database connection?

easy

A. It executes an SQL statement on the database.

B. It creates a new database connection.

C. It formats the SQL query string.

D. It closes the database connection.

Solution

1. Step 1: Understand the purpose of the query method

   The query method is designed to send SQL commands to the database for execution.

2. Step 2: Identify what the method does with the SQL statement

   It runs the SQL command and returns the result, such as data or success status.

3. Final Answer:

   It executes an SQL statement on the database. -> Option A

4. Quick Check:

   query method = executes SQL [OK]

Hint: query method runs SQL commands on the database [OK]

Common Mistakes:

• Thinking query creates or closes connections
• Confusing query with preparing statements
• Assuming query formats SQL strings

2. Which of the following is the correct syntax to execute a SELECT query using the query method on a PDO object named $pdo?

easy

A. $pdo->query = 'SELECT * FROM users';

B. $pdo->execute('SELECT * FROM users');

C. query($pdo, 'SELECT * FROM users');

D. $pdo->query('SELECT * FROM users');

Solution

1. Step 1: Recall the method call syntax for PDO query

   The query method is called on the PDO object using arrow notation with the SQL string as argument.

2. Step 2: Check each option for correct syntax

   $pdo->query('SELECT * FROM users'); correctly calls $pdo->query('SELECT * FROM users');. Others misuse assignment, function call, or wrong method.

3. Final Answer:

   $pdo->query('SELECT * FROM users'); -> Option D

4. Quick Check:

   Correct method call = $pdo->query('SELECT * FROM users'); [OK]

Hint: Use arrow operator and pass SQL string to query() [OK]

Common Mistakes:

• Using assignment instead of method call
• Calling query as a standalone function
• Using execute instead of query for simple queries

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

$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE test (id INTEGER)');
$pdo->exec('INSERT INTO test (id) VALUES (1), (2), (3)');
$result = $pdo->query('SELECT COUNT(*) FROM test');
$count = $result->fetchColumn();
echo $count;

medium

A. 3

B. COUNT(*)

C. 1

D. Error

Solution

1. Step 1: Understand the SQL commands executed

   The code creates a table and inserts three rows with ids 1, 2, and 3.

2. Step 2: Analyze the SELECT COUNT(*) query and fetch result

   The query counts rows in the table, which is 3. fetchColumn() returns this count as a string.

3. Final Answer:

   3 -> Option A

4. Quick Check:

   Row count = 3 [OK]

Hint: COUNT(*) returns number of rows, fetchColumn gets that value [OK]

Common Mistakes:

• Confusing fetchColumn() with fetch() returning array
• Expecting column name instead of count value
• Assuming query returns error without table

4. Identify the error in the following PHP code that uses the query method:

$pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'pass');
$sql = "INSERT INTO users (name) VALUES ('Alice')";
$result = $pdo->query($sql);
if ($result) {
    echo "Insert successful";
} else {
    echo "Insert failed";
}

medium

A. PDO connection string is wrong.

B. The SQL syntax is incorrect for INSERT.

C. Using query() for INSERT may not return true/false as expected.

D. Missing fetch() after query() call.

Solution

1. Step 1: Check usage of query() with INSERT statements

   The query method can execute INSERT but returns a PDOStatement or false, not a simple true/false.

2. Step 2: Understand how to check success of INSERT

   For INSERT, it's better to use exec() which returns affected rows count or false on failure.

3. Final Answer:

   Using query() for INSERT may not return true/false as expected. -> Option C

4. Quick Check:

   query() returns PDOStatement, not boolean for INSERT [OK]

Hint: Use exec() for INSERT to get affected rows count [OK]

Common Mistakes:

• Expecting query() to return boolean for INSERT
• Confusing query() and exec() methods
• Ignoring return value type of query()

5. You want to retrieve all usernames from a users table and store them in an array using the query method. Which code snippet correctly does this?

hard

A. $usernames = $pdo->query('SELECT username FROM users')->fetch(PDO::FETCH_COLUMN);

B. $stmt = $pdo->query('SELECT username FROM users');
$usernames = $stmt->fetchAll(PDO::FETCH_COLUMN);

C. $usernames = $pdo->query('SELECT username FROM users')->fetchColumn();

D. $usernames = $pdo->query('SELECT username FROM users')->fetchAll();

Solution

1. Step 1: Understand how to fetch all values from a single column

   The fetchAll(PDO::FETCH_COLUMN) fetches all rows from the first column into an array.

2. Step 2: Check each option for correct usage

   $stmt = $pdo->query('SELECT username FROM users'); $usernames = $stmt->fetchAll(PDO::FETCH_COLUMN); correctly calls query(), then fetchAll with FETCH_COLUMN to get usernames array. Others misuse fetchAll or fetchColumn.

3. Final Answer:

   $stmt = $pdo->query('SELECT username FROM users'); $usernames = $stmt->fetchAll(PDO::FETCH_COLUMN); -> Option B

4. Quick Check:

   fetchAll(PDO::FETCH_COLUMN) returns array of column values [OK]

Hint: Use fetchAll(PDO::FETCH_COLUMN) to get single column array [OK]

Common Mistakes:

• Using fetchColumn() to get all rows instead of one
• Not storing query result before fetching
• Passing wrong fetch mode to fetch()