Fetching Data with PDO

When you use the query() or prepare() methods of PDO, they return an instance of the PDOStatement object. The PDOStatement class represents a prepared statement or the result set from a query. It provides methods to execute the statement, bind parameters, fetch results, and more.

So far, we've executed queries, but we haven't worked with the results. The PDOStatement class supplies us with a few methods for working with the results and fetching data. They're the following:

Let's go through some of these methods to understand the differences.

fetch() Method

The fetch() method of the PDOStatement class is used to retrieve the next row from the result set of a query. It allows you to specify a "fetch style" to determine the format of the returned data.

Here's the method definition

PDOStatement::fetch(int $mode = PDO::FETCH_DEFAULT, int $cursorOrientation = PDO::FETCH_ORI_NEXT, int $cursorOffset = 0): mixed

Parameters

• $mode: The style of the result required. Examples include PDO::FETCH_ASSOC (for associative arrays) and PDO::FETCH_OBJ (for objects).
• $cursorOrientation and $cursorOffset: These are less commonly used but allow for advanced cursor navigation.

Imagine you have a users table, and you want to fetch all users' names.

$stmt = $pdo->query("SELECT name FROM users");
 
while ($row = $stmt->fetch()) {
  echo $row['name'] . "<br>";
}

In this example:

1. We use the query() method to run a SQL command.
2. We loop through the result using the fetch() method. The result will be an associative array where the keys are the column names. So $row['name'] fetches the name column from the current row in each iteration of the loop.

fetchAll() Method

In the previous example, we looped through the fetch() method. In some cases, you may want to grab results immediately and store them in a single variable.

The fetchAll() method of the PDOStatement class is used to retrieve all the rows from the result set of a query at once. It has similar parameters to the fetch() method.

Imagine you have a books table, and you want to fetch the titles of all books.

$stmt = $pdo->query("SELECT title FROM books");
 
$allBooks = $stmt->fetchAll(PDO::FETCH_ASSOC);
 
foreach ($allBooks as $book) {
  echo $book['title'] . "<br>";
}

In this example:

1. We use the query() method to run a SQL command.
2. We fetch all results at once with the fetchAll() method. The results are stored in the $allBooks array.
3. We then loop through $allBooks to display each book title.

fetchAll() is particularly useful when working with datasets that aren't too large, as it allows you to fetch all results in a single call. However, for very large datasets, fetching row-by-row with fetch() might be more memory-efficient.

fetchColumn() Method

The fetchColumn() method of the PDOStatement class retrieves a single column's value from the next row in the result set. It's particularly useful when you're interested in just one field from a row, for instance, when checking the existence of a record or getting a specific value.

This method has one parameter. The 0-indexed number of the column you wish to retrieve. If omitted, it fetches the first column.

Let's assume you have a users table, and you want to fetch just the email of the first user with the name "John Doe".

$stmt = $pdo->prepare("SELECT email FROM users WHERE name = :name");
$stmt->execute(['name' => 'John Doe']);
 
$email = $stmt->fetchColumn();
echo $email;

In this example:

1. We prepare a SQL query to get the email of users with the name "John Doe".
2. We execute the query.
3. We use fetchColumn() to retrieve the email of the first user with that name. If there are multiple users with the name "John Doe", only the email of the first one encountered will be fetched.

fetchColumn() simplifies operations where you're only interested in a single value rather than a full row of data.

Setting the Fetch Mode

The fetch() and fetchAll() methods have the same parameters. The first parameter allows you to configure the fetch mode.

The fetch mode dictates how the result sets from database queries are presented to the developer. It determines the format of the data you get when you fetch a row from your result set. PDO offers various fetch modes, allowing developers flexibility in how they access and interact with the returned data.

Fetch modes are stored in constants under the PDO class. These are the most commonly used modes:

• PDO::FETCH_ASSOC: Returns the result as an associative array, where column names become keys.
• PDO::FETCH_NUM: Returns the result as a numeric array indexed by column numbers.
• PDO::FETCH_OBJ: Returns the result as an object, where column names turn into object properties.
• PDO::FETCH_BOTH: Returns an array that's both associative and numeric.
• PDO::FETCH_BOUND: Binds columns in the result set to PHP variables.
• PDO::FETCH_CLASS: Returns a new instance of a specified class, setting the properties with column values.
• PDO::FETCH_LAZY: Combines features of the OBJ, ASSOC, and NUM modes, allowing data to be accessed in multiple ways without fetching it more than once.

The choice of fetch mode depends on the specific needs of the application and developer preference. For instance, one might choose FETCH_OBJ for OOP-centric applications or FETCH_ASSOC for easier array manipulation. Overall, the fetch mode in PDO provides a flexible way to work with database results, enabling developers to retrieve data in the most suitable format for their application logic.

Basic Example

Here's how you can set the fetch mode to retrieve results as an associative array using the PDO::FETCH_ASSOC constant. Let's say we have a students table, and we want to fetch the names and ages of all students.

$stmt = $pdo->query("SELECT name, age FROM students");
 
$allStudents = $stmt->fetchAll(PDO::FETCH_ASSOC);
 
foreach ($allStudents as $student) {
  echo "Name: " . $student['name'] . " - Age: " . $student['age'] . "<br>";
}

In this example:

1. After executing the query with the query() method, we set the fetch mode of the statement to PDO::FETCH_ASSOC using the fetchAll() method.
2. As we loop through the results, they are returned as associative arrays, allowing us to access columns using their names ($row['name'] and $row['age']).

Where the fetch mode can be set

There are several areas where you can set the fetch mode, depending on your specific requirements and workflow. Here's a breakdown:

1. Directly in the fetch() method: You can set the fetch mode directly when calling the fetch() method on a PDOStatement object. This is a one-time specification just for that particular fetch.

$stmt->fetch(PDO::FETCH_ASSOC);

2. Directly in the fetchAll() method: When retrieving all results at once, you can set the fetch mode directly in the fetchAll() method.

$stmt->fetchAll(PDO::FETCH_ASSOC);

3. Using the setFetchMode() method: This sets the default fetch mode for a PDOStatement object. Every subsequent call to fetch() on this object will use the specified mode unless overridden.

$stmt->setFetchMode(PDO::FETCH_ASSOC);

4. In the query() method as a second parameter: When executing a quick one-off query, you can set the fetch mode immediately after.

$pdo->query("SELECT * FROM table_name", PDO::FETCH_ASSOC);

5. In the PDO class's contructor method: The PDO constructor can indeed accept an options array as its fourth argument, and within this array, you can specify the default fetch mode. Any fetch() operation that doesn't specify a mode will use this default.

$dsn = "mysql:host=localhost;dbname=your_dbname";
$username = "your_username";
$password = "your_password";
 
$options = [
  PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC
];
 
$pdo = new PDO($dsn, $username, $password, $options);

It's evident that PDO provides tremendous flexibility when it comes to specifying how to fetch data. The choice of where and how to set the fetch mode often comes down to the specific needs of the application or the developer's preference.

Counting Results

One more thing! There's another method that might come in handy when you want to count how many results were updated after performing an UPDATE operation called rowCount().

The rowCount() method in PDO returns the number of rows affected by the last DELETE, INSERT, or UPDATE statement executed by the corresponding PDOStatement object. When it comes to counting how many rows were updated after an UPDATE operation, you can simply call rowCount() on the PDOStatement object used for the update.

Take the following:

$stmt = $pdo->prepare("UPDATE students SET age = :age WHERE name = :name");
 
$stmt->bindParam(':age', $age);
$stmt->bindParam(':name', $name);
 
$name = "John Doe";
$age = 22;
 
$stmt->execute();

In this example, we're updating a table called students. Afterward, we may want to verify how many rows were updated. We can do the following:

$affectedRows = $stmt->rowCount();
echo "Number of rows updated: " . $affectedRows;

In this example, after executing the UPDATE statement, we call rowCount() to determine how many rows in the students table were affected by the update.

Key Takeaways

• After a query is executed using the PDO instance, a PDOStatement object is returned. This object provides methods to fetch data.
• PDO provides multiple fetch modes to retrieve data in various formats, such as associative arrays, numeric arrays, or objects.
• PDO::FETCH_ASSOC fetch as an associative array, PDO::FETCH_NUM fetch as a numeric array, PDO::FETCH_OBJ fetch as an object, PDO::FETCH_BOTH fetch as both an associative and numeric array, and many more.
• Modes can be set directly in the fetch() or fetchAll() methods.
• fetch(): Fetches a single row from the result set.
• fetchAll(): Fetches all rows from the result set.
• fetchColumn(): Fetches a single column from the next row of the result set.
• The rowCount() method provides the number of rows affected by the last DELETE, INSERT, or UPDATE statement. However, its behavior might not be consistent for SELECT statements across different database drivers.