Why Executing queries with query method in PHP? - Purpose & Use Cases

The Big Idea

What if you could run database commands with just one simple line of code?

The Scenario

Imagine you want to get data from a database by writing every SQL command manually and running it step by step in your code.

You have to write long code to connect, send the command, check for errors, and fetch results each time.

The Problem

This manual way is slow and easy to mess up.

You might forget to check for errors or write the SQL wrong.

It takes a lot of code just to do simple tasks, making your work boring and full of mistakes.

The Solution

The query method lets you send SQL commands directly and get results quickly.

It wraps all the steps into one simple call, so your code is shorter and cleaner.

This means less chance of errors and faster coding.

Before vs After

✗ Before

$result = mysqli_query($conn, "SELECT * FROM users");
if (!$result) {
  die('Error in query');
}
while ($row = mysqli_fetch_assoc($result)) {
  echo $row['name'];
}

✓ After

$result = $conn->query("SELECT * FROM users");
if (!$result) {
  die('Error in query');
}
while ($row = $result->fetch_assoc()) {
  echo $row['name'];
}

What It Enables

You can quickly run database commands and get data with less code and fewer mistakes.

Real Life Example

When building a website, you often need to show user info from a database.

Using the query method, you can easily get and display this data without writing extra code for error checks every time.

Key Takeaways

Manual database commands are long and error-prone.

The query method simplifies running SQL commands.

This makes your code cleaner, faster, and easier to maintain.

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