How to Use PDO to Retrieve an Associative Array Grouped by Column Values?

Using PDO to Retrieve an Associative Array Grouped by Column Values

In database operations, it is often necessary to retrieve data organized in specific formats. One such format is an associative array grouped by the values of a particular column. This allows for easy access to data based on the specified column. Here's a discussion on how to achieve this using PDO.

Problem: Given a table with the following data:

name	age	sex	position
Antony	34	M	programmer
Sally	30	F	manager
Matthew	28	M	designer

Goal: Fetch the data in the following format:

Array
(
  [Antony] => Array
    (
      [age] => 34
      [sex] => M
      [position] => programmer
    )

  [Sally] => Array
    (
      [age] => 30
      [sex] => F
      [position] => manager
    )

  [Matthew] => Array
    (
      [age] => 28
      [sex] => M
      [position] => designer
    )
)

Solution: To achieve this, you can use PDO's fetchAll method with the following modes:

$pdo->query('SELECT * FROM employee')->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_UNIQUE);

The FETCH_GROUP mode groups the results by the values of the first column. The FETCH_UNIQUE mode limits the results to a single row for each unique value in the grouping column. As a result, you get an associative array with the column values serving as keys and the remaining columns as values.

Example Output:

Array
(
  [Antony] => Array
    (
      [0] => (age: 34, sex: M, position: programmer)
    )
  [Sally] => Array
    (
      [0] => (age: 30, sex: F, position: manager)
    )
  [Matthew] => Array
    (
      [0] => (age: 28, sex: M, position: designer)
    )
)

Note that the output contains arrays with a single row for each group. This is because the FETCH_UNIQUE mode ensures that only the first row for each group is retrieved.

The above is the detailed content of How to Use PDO to Retrieve an Associative Array Grouped by Column Values?. For more information, please follow other related articles on the PHP Chinese website!