PDO FETCH

Summary: in this tutorial, you’ll learn how to use the PDO::FETCH_GROUP mode to group the selected rows by the first column.

Introduction to the PDO::FETCH_GROUP mode

The PDO::FETCH_GROUP allows you to group rows from the result set into a nested array, where the indexes will be the unique values from the column and the values will be arrays of the remaining columns.

For example, if you have a query like this:

SELECT role, username, email
FROM users;Code language: SQL (Structured Query Language) (sql)

The PDO::FETCH_GROUP mode will return the following output:

[
    'admin' => [
        0 => [
            'username' => 'admin',
            'email' => '[email protected]'
        ],
        1 => [
            'username' => 'bob',
            'email' => '[email protected]'
        ]
    ]
    'contributor' => [
        0 => [
            'username' => 'alex',
            'email' => '[email protected]'
        ],
        1 => [
            'username' => 'alice',
            'email' => '[email protected]'
        ]
    ]
]Code language: PHP (php)

The PDO::FETCH_GROUP is helpful in case you want to group rows by unique values of the first column in the result set. For example, you can use the PDO::FETCH_GROUP to select data for generating groupings of options within a select element.

The PDO::FETCH_GROUP example

The following example selects the books and publishers from the books and publishers table. The PDO::FETCH_GROUP groups the books by the publisher names:

<?php

$pdo = require 'connect.php';

$sql = 'SELECT name, book_id, title
        FROM publishers p
        INNER JOIN books b ON b.publisher_id = p.publisher_id';

$statement = $pdo->query($sql);

$publishers = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Books</title>
</head>
<body>
    <label for="book">Select a book:</label>
    <select name="book" id="book">
        <?php foreach ($publishers as $publisher => $books) : ?>
        <optgroup label="<?php echo $publisher ?>">
            <?php foreach ($books as $book) : ?>
            <option value="<?php echo $book['book_id'] ?>"><?php echo $book['title'] ?></option>
            <?php endforeach ?>
        </optgroup>
        <?php endforeach ?>
    </select>
</body>
</html>Code language: PHP (php)

Select a book:

How it works.

First, connect to the database using the connect.php script. The script returns an instance of the PDO object:

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

Next, construct an SQL statement to select publisher name from the publishers table and book id and title from the books table:

$sql = 'SELECT name, book_id, title
        FROM publishers p
        INNER JOIN books b ON b.publisher_id = p.publisher_id';Code language: PHP (php)

Note that the query uses INNER JOIN clause to combine data from two tables. If you’re not familiar with INNER JOIN, you can check out the INNER JOIN tutorial.

Then, execute the SQL statement using the query() method of the PDO object:

$statement = $pdo->query($sql);Code language: PHP (php)

After that, fetch rows from the result set using the PDO::FETCH_GROUP and PDO::FETCH_ASSOC modes:

$publishers = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);Code language: PHP (php)

Finally, create a grouping of options for a select element using a nested foreach loop. The outer foreach loop creates the optgroup element. And the inner loop creates the option elements.

<label for="book">Select a book:</label>
<select name="book" id="book">
    <?php foreach ($publishers as $publisher => $books) : ?>
    <optgroup label="<?php echo $publisher ?>">
        <?php foreach ($books as $book) : ?>
        <option value="<?php echo $book['book_id'] ?>"><?php echo $book['title'] ?></option>
        <?php endforeach ?>
 </optgroup>
<?php endforeach ?>Code language: PHP (php)

Summary

Use the PDO::FETCH_GROUP to group rows from a result set by the first selected column.

Did you find this tutorial useful?