fetchAll

Summary: in this tutorial, you’ll learn how to use the PHP fetchAll() method of the PDOStatement object to return an array containing all rows of a result set.

Introduction to the PHP fetchAll() method

The fetchAll() is a method of the PDOStatement class. The fetchAll() method allows you to fetch all rows from a result set associated with a PDOStatement object into an array.

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

public function fetchAll(int $mode = PDO::FETCH_DEFAULT): arrayCode language: PHP (php)

The $mode parameter determines how the fetchAll() 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. This is the default.
• 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 fetchAll() method returns an array that contains all rows of a result set.

If the result set is empty, the fetchAll() method returns an empty array. If the fetchAll() fails to fetch data, it’ll return false.

It’s important to notice that if you have a large result set, the fetchAll() may consume a lot of server memory and possibly network resources. To avoid this, you should execute a query that retrieves only necessary data from the database server.

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

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

• First, execute the query by calling the query() method of the PDO object.
• Then, fetch all rows from the result set into an array using the fetchAll() method.

The following example illustrates how to use the fetchAll() method to select all rows from the publishers table:

<?php

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

$sql = 'SELECT publisher_id, name 
        FROM publishers';

// execute a query
$statement = $pdo->query($sql);

// fetch all rows
$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);

// display the publisher name
foreach ($publishers as $publisher) {
    echo $publisher['name'] . '<br>';
}Code language: PHP (php)

Output:

1.McGraw-Hill Education
2.Penguin/Random House
3.Hachette Book Group
4.Harper Collins
5.Simon and SchusterCode language: plaintext (plaintext)

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 publisher_id and name from the publishers table:

$sql = 'SELECT publisher_id, name 
        FROM publishers';
$statement = $pdo->query($sql);Code language: PHP (php)

Third, fetch all rows from the result set into an array:

// display the publishers
foreach ($publishers as $publisher) {
    echo $publisher['name'] . '<br>';
}Code language: PHP (php)

Using the fetchAll() method with a prepared statement

If a query accepts one or more parameters, you can:

• First, execute a prepared statement.
• Second, fetch all rows from the result set into an array using the fetchAll() method.

The following example shows how to use fetchAll() to fetch all publishers with the id greater than 2:

<?php

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

$sql = 'SELECT publisher_id, name 
        FROM publishers
        WHERE publisher_id > :publisher_id';

// execute a query
$statement = $pdo->prepare($sql);
$statement->execute([
    ':publisher_id' => 2
]);
// fetch all rows
$publishers = $statement->fetchAll(PDO::FETCH_ASSOC);

// display the publishers
foreach ($publishers as $publisher) {
    echo $publisher['publisher_id'] . '.' . $publisher['name'] . '<br>';
}Code language: PHP (php)

Output:

3.Hachette Book Group
4.Harper Collins
5.Simon and SchusterCode language: plaintext (plaintext)

Summary

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