{"id":2152,"date":"2021-07-08T00:57:20","date_gmt":"2021-07-08T00:57:20","guid":{"rendered":"https:\/\/phptutorial.net\/?page_id=2152"},"modified":"2021-07-08T01:06:26","modified_gmt":"2021-07-08T01:06:26","slug":"pdo-fetch_group","status":"publish","type":"page","link":"https:\/\/www.phptutorial.net\/php-pdo\/pdo-fetch_group\/","title":{"rendered":"PDO FETCH_GROUP"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you’ll learn how to use the PDO::FETCH_GROUP<\/code> mode to group the selected rows by the first column.<\/p>\n\n\n\n

Introduction to the PDO::FETCH_GROUP mode #<\/a><\/h2>\n\n\n\n

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

For example, if you have a query like this:<\/p>\n\n\n

SELECT<\/span> role<\/span>, username, email\nFROM<\/span> users<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The PDO::FETCH_GROUP<\/code> mode will return the following output:<\/p>\n\n\n
[\n    'admin'<\/span> => [\n        0<\/span> => [\n            'username'<\/span> => 'admin'<\/span>,\n            'email'<\/span> => 'admin@phptutorial.net'<\/span>\n        ],\n        1<\/span> => [\n            'username'<\/span> => 'bob'<\/span>,\n            'email'<\/span> => 'bob@phptutorial.net'<\/span>\n        ]\n    ]\n    'contributor'<\/span> => [\n        0<\/span> => [\n            'username'<\/span> => 'alex'<\/span>,\n            'email'<\/span> => 'alex@phptutorial.net'<\/span>\n        ],\n        1<\/span> => [\n            'username'<\/span> => 'alice'<\/span>,\n            'email'<\/span> => 'alice@phptutorial.net'<\/span>\n        ]\n    ]\n]<\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
The PDO::FETCH_GROUP<\/code> 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<\/code> to select data for generating groupings of options within a select element.<\/p>\n\n\n\n
The PDO::FETCH_GROUP example #<\/a><\/h2>\n\n\n\n
The following example selects the books and publishers from the books<\/code> and publishers<\/code> table. The PDO::FETCH_GROUP<\/code> groups the books by the publisher names:<\/p>\n\n\n
<?php<\/span>\n\n$pdo = require<\/span> 'connect.php'<\/span>;\n\n$sql = 'SELECT name, book_id, title\n        FROM publishers p\n        INNER JOIN books b ON b.publisher_id = p.publisher_id'<\/span>;\n\n$statement = $pdo->query($sql);\n\n$publishers = $statement->fetchAll(PDO::FETCH_GROUP | PDO::FETCH_ASSOC);\n?><\/span>\n\n<!DOCTYPE html>\n<html lang=\"en\"<\/span>>\n<head>\n    <meta charset=\"UTF-8\"<\/span>>\n    <meta name=\"viewport\"<\/span> content=\"width=device-width, initial-scale=1.0\"<\/span>>\n    <title>Books<\/title>\n<\/head>\n<body>\n    <label for<\/span>=\"book\"<\/span>>Select a book:<\/label>\n    <select name=\"book\"<\/span> id=\"book\"<\/span>>\n        <?php<\/span> foreach<\/span> ($publishers as<\/span> $publisher => $books) : ?><\/span>\n        <optgroup label=\"<?php echo $publisher ?>\"<\/span>>\n            <?php<\/span> foreach<\/span> ($books as<\/span> $book) : ?><\/span>\n            <option value=\"<?php echo $book['book_id'] ?>\"<\/span>><?php<\/span> echo<\/span> $book['title'<\/span>] ?><\/span><\/option>\n            <?php<\/span> endforeach<\/span> ?><\/span>\n        <\/optgroup>\n        <?php<\/span> endforeach<\/span> ?><\/span>\n    <\/select>\n<\/body>\n<\/html><\/code><\/span>Code language:<\/span> PHP<\/span> (<\/span>php<\/span>)<\/span><\/small><\/pre>\n\n\n
\n
\n