Executing queries with query method in PHP - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

What is the output of this PHP code using query method?

Consider the following PHP code snippet that uses the query method to fetch data from a database. What will be the output?

PHP

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'testdb');
$result = $mysqli->query('SELECT name FROM users WHERE id = 1');
$row = $result->fetch_assoc();
echo $row['name'];
?>

A SyntaxError: Unexpected token in query method

B The name of the user with id 1 is printed

C Fatal error: Call to undefined method query()

D Empty output because query returns false

💡 Hint

Remember that query returns a result object on success, and you can fetch rows from it.

The query method executes the SQL and returns a result object. Using fetch_assoc() retrieves the row as an associative array. Then echo prints the 'name' field.

❓ Predict Output

intermediate

What error does this PHP code raise when using query method?

Look at this PHP code snippet. What error will it produce?

PHP

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'testdb');
$result = $mysqli->query('SELECT * FROM non_existing_table');
if (!$result) {
    echo $mysqli->error;
}
?>

A Outputs an error message about unknown table

B SyntaxError: Invalid SQL syntax

C Fatal error: Call to undefined method query()

D No output, query returns empty result

💡 Hint

Check what happens when the query references a table that does not exist.

The query method returns false on failure. The error message from $mysqli->error explains the problem, such as unknown table.

🔧 Debug

advanced

Why does this PHP code using query method cause a fatal error?

Examine this PHP code snippet. It causes a fatal error. What is the cause?

PHP

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'testdb');
$result = $mysqli->query('SELECT id FROM users');
echo $result['id'];
?>

A mysqli connection failed causing fatal error

B Query method returns false due to syntax error

C fetch_assoc() method missing causing fatal error

D Trying to access result object as array causes fatal error

💡 Hint

Remember what type query returns and how to access data from it.

The query method returns a result object, not an array. Accessing it like an array causes a fatal error. You must fetch rows first.

📝 Syntax

advanced

Which option correctly uses the query method to fetch all user names?

Choose the PHP code snippet that correctly fetches and prints all user names from the database using the query method.

A

<?php
$result = $mysqli->query('SELECT name FROM users');
while ($row = $result->fetch_assoc()) {
    echo $row['name'] . '\n';
}
?>

B

<?php
$result = $mysqli->query('SELECT name FROM users');
for ($i=0; $i < count($result); $i++) {
    echo $result[$i]['name'] . '\n';
}
?>

C

<?php
$result = $mysqli->query('SELECT name FROM users');
foreach ($result as $row) {
    echo $row['name'] . '\n';
}
?>

D

<?php
$result = $mysqli->query('SELECT name FROM users');
echo $result['name'];
?>

💡 Hint

Think about how to iterate over a mysqli result set properly.

The query method returns a result object. You must use fetch_assoc() in a loop to get each row. Options A, C, and D misuse the result object.

🚀 Application

expert

How many rows are returned by this PHP query method call?

Given this PHP code, how many rows will $count hold after execution?

PHP

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'testdb');
$result = $mysqli->query('SELECT * FROM users WHERE age > 30');
$count = $result->num_rows;
?>

A Total number of users in the database

B Always zero because num_rows is not supported

C The number of users with age greater than 30

D One, because query returns only one row by default

💡 Hint

Check what num_rows property represents after a SELECT query.

The num_rows property gives the number of rows in the result set. Here it counts users older than 30.

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()