Executing queries with query method in PHP - Step-by-Step Execution

Concept Flow - Executing queries with query method

Create DB connection

↓

Prepare SQL query string

↓

Call query() method on connection

↓

Check if query succeeded

↓

Fetch results

↓

Use or display results

↓

Close connection

This flow shows how to run a SQL query using the query() method on a database connection, check success, then fetch or handle errors.

Execution Sample

PHP

<?php
$conn = new mysqli('localhost', 'user', 'pass', 'db');
$result = $conn->query('SELECT * FROM users');
if ($result) {
  while ($row = $result->fetch_assoc()) {
    echo $row['name'];
  }
}
$conn->close();
?>

This code connects to a MySQL database, runs a SELECT query using query(), then prints each user's name.

Execution Table

Step	Action	Evaluation	Result
1	Create mysqli connection	Connection established	Connection object created
2	Call query() with 'SELECT * FROM users'	Query sent to DB	Result object returned
3	Check if result is truthy	Result is valid	Enter fetch loop
4	Fetch first row with fetch_assoc()	Row data: ['name' => 'Alice']	Row array returned
5	Echo $row['name']	Output 'Alice'	Printed 'Alice'
6	Fetch second row	Row data: ['name' => 'Bob']	Row array returned
7	Echo $row['name']	Output 'Bob'	Printed 'Bob'
8	Fetch next row	No more rows	fetch_assoc() returns null
9	Exit loop	No more rows	Stop fetching
10	Close connection	Connection closed	Resources freed

💡 fetch_assoc() returns null when no more rows, ending the loop

Variable Tracker

Variable	Start	After Step 2	After Step 4	After Step 6	After Step 8	Final
$conn	null	mysqli connection object	mysqli connection object	mysqli connection object	mysqli connection object	closed
$result	null	mysqli_result object	mysqli_result object	mysqli_result object	mysqli_result object	mysqli_result object
$row	null	null	['name' => 'Alice']	['name' => 'Bob']	null	null

Key Moments - 3 Insights

Why do we check if $result is truthy before fetching rows?

Because query() returns false if the query fails. Checking $result ensures we only fetch rows if the query succeeded (see step 3 in execution_table).

What happens when fetch_assoc() returns null?

It means there are no more rows to fetch, so the loop ends (see step 8 and 9 in execution_table).

Why do we close the connection at the end?

Closing frees resources and ends the connection cleanly (step 10).

Visual Quiz - 3 Questions

Look at the execution_table, what is the value of $row after step 6?

A ['name' => 'Bob']

B ['name' => 'Alice']

C null

D mysqli_result object

💡 Hint

Check the variable_tracker row for $row at After Step 6

At which step does the fetch loop end because there are no more rows?

A Step 7

B Step 9

C Step 8

D Step 10

💡 Hint

Look at execution_table rows where fetch_assoc() returns null

If the query failed, what would $result be after step 2?

A null

B false

C mysqli_result object

D empty array

💡 Hint

Recall that query() returns false on failure (see key_moments about checking $result)

Concept Snapshot

Use $conn->query($sql) to run SQL queries.
Check if result is truthy before fetching.
Use fetch_assoc() to get rows one by one.
fetch_assoc() returns null when done.
Close connection when finished.

Full Transcript

This example shows how to execute SQL queries in PHP using the query() method of a mysqli connection. First, a connection is created. Then, the query() method sends the SQL string to the database. If the query succeeds, it returns a result object. We check if the result is valid before fetching rows. Using fetch_assoc(), we get each row as an associative array until no rows remain. Each row's data can be used or printed. Finally, the connection is closed to free resources.

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