fetch

Summary: in this tutorial, you’ll learn how to use the PHP fetch() method of the PDOStatement object to fetch a row from the result set.

Introduction to the PHP fetch() method

The fetch() is a method of the PDOStatement class. The fetch() method allows you to fetch a row from a result set associated with a PDOStatement object.

Internally, the fetch() method fetches a single row from a result set and moves the internal pointer to the next row in the result set. Therefore, the subsequent call to the fetch() method will return the next row from the result set.

To fetch all rows from a result set one by one, you typically use the fetch() method in a while loop.

The following shows the syntax of the fetch() method:

public function fetch(
    int $mode = PDO::FETCH_DEFAULT, 
    int $cursorOrientation = PDO::FETCH_ORI_NEXT, 
    int $cursorOffset = 0
): mixedCode language: PHP (php)

The fetch() method accepts three optional parameters. The most important one is the first parameter $mode.

The $mode parameter determines how the fetch() returns the next row. The $mode parameter accepts one of the PDO::FETCH_* constants. The most commonly used modes are:

• PDO::FETCH_BOTH – returns an array indexed by both column name and 0-indexed column number.
• PDO::FETCH_ASSOC – returns an array indexed by column name
• PDO::FETCH_CLASS – returns a new class instance by mapping the columns to the object’s properties.

The fetch() method returns a value depending on the $mode parameter. It returns false on failure.

Using the PHP fetch() method with the query() method

If a query doesn’t accept a parameter, you can fetch a row from the result set as follows:

• First, execute the query by calling the query() method of the PDO object.
• Then, fetch each row from the result set using the fetch() method and a while loop:

The following example shows how to use the fetch() method to select each row from the books table:

<?php

// connect to the database to get the PDO instance
$pdo = require 'connect.php';

// execute a query
$sql = 'SELECT book_id, title FROM books';
$statement = $pdo->query($sql);

// fetch the next row
while (($row = $statement->fetch(PDO::FETCH_ASSOC)) !== false) {
    echo $row['title'] . '<br>';
}Code language: PHP (php)

How it works.

First, connect to the bookdb database using the connect.php script.

$pdo = require 'connect.php';Code language: PHP (php)

Second, execute a query that selects the book_id and title from the books table:

$sql = 'SELECT book_id, title FROM books';
$statement = $pdo->query($sql);Code language: PHP (php)

Third, fetch each row from the result set until there’s no more row to fetch and display the book title in each iteration.

while (($row = $statement->fetch(PDO::FETCH_ASSOC)) !== false) {
    echo $row['title'] . '<br>';
}Code language: PHP (php)

Using the fetch() method with a prepared statement

When a query accepts one or more parameters, you can fetch the next row from the result set as follows:

• First, execute the prepared statement.
• Second, fetch the next row from the result set using the fetch() method.

The following example shows how to fetch() to fetch a book from the books table with publisher id 1:

<?php

// connect to the database to get the PDO instance
$pdo = require 'connect.php';

$sql = 'SELECT book_id, title 
        FROM books 
        WHERE publisher_id =:publisher_id';

// prepare the query for execution
$statement = $pdo->prepare($sql);

// execute the query
$statement->execute([
    ':publisher_id' => 1
]);

// fetch the next row
while (($row = $statement->fetch(PDO::FETCH_ASSOC)) !== false) {
    echo $row['title'] . PHP_EOL;
}Code language: PHP (php)

Summary

• Use the fetch() method to fetch a row from the result set associated with a PDOStatement object.