Executing queries with query method in PHP - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to execute a SQL query using the query method.

PHP

$result = $conn->[1]("SELECT * FROM users");

Drag options to blanks, or click blank then click option'

Aquery

Bexecute

Crun

Dsend

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'execute' instead of 'query' causes an error.

Trying to call a method that doesn't exist like 'run' or 'send'.

The query method is used to execute SQL statements in PHP's mysqli.

2fill in blank

medium

Complete the code to check if the query was successful.

PHP

if ($result === [1]) {
    echo "Query failed.";
}

Drag options to blanks, or click blank then click option'

Atrue

B0

Cfalse

Dnull

Attempts:

3 left

💡 Hint

Common Mistakes

Checking for true instead of false.

Using null which is not returned by query failures.

The query method returns false on failure.

3fill in blank

hard

Fix the error in the code to fetch rows from the query result.

PHP

while ($row = $result->[1]()) {
    echo $row['name'];
}

Drag options to blanks, or click blank then click option'

Afetch_assoc

Bfetch_array

Cfetch_row

Dfetch_object

Attempts:

3 left

💡 Hint

Common Mistakes

Using fetch_row() returns numeric indexes, not associative keys.

Using fetch_object() returns an object, not an array.

fetch_assoc() returns an associative array, allowing access by column names.

4fill in blank

hard

Fill both blanks to execute a query and check if it succeeded.

PHP

$result = $conn->[1]($sql);
if ($result === [2]) {
    echo "Error in query.";
}

Drag options to blanks, or click blank then click option'

Aquery

Bfalse

Ctrue

Dexecute

Attempts:

3 left

💡 Hint

Common Mistakes

Using execute instead of query.

Checking for true instead of false.

Use query to run the SQL and check if the result is false to detect errors.

5fill in blank

hard

Fill all three blanks to fetch and print all user names from the query result.

PHP

$result = $conn->[1]($sql);
while ($row = $result->[2]()) {
    echo $row[[3]] . "\n";
}

Drag options to blanks, or click blank then click option'

Aquery

Bfetch_assoc

C'name'

Dfetch_row

Attempts:

3 left

💡 Hint

Common Mistakes

Using fetch_row() and trying to access by column name.

Forgetting to use quotes around the column name.

Use query to run the SQL, fetch_assoc() to get associative arrays, and access the 'name' column.

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