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 namePDO::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 thePDOobject. - 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:
// 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:
// 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 aPDOStatementobject.