How to Retrieve Associative Arrays Grouped by Specific Column with PDO?

Fetching Associative Arrays Grouped by Column Values with PDO

Question:

In a database with multiple columns (e.g., "name," "age," "sex," and "position"), how can we retrieve associative arrays in a specific format using PDO's fetchAll method, where each array's key is a field from a specified column (e.g., "name") and the values are the corresponding rows for that key?

Answer:

Using PDO's fetchAll method, we can specify the following flags to achieve the desired result:

<code class="php">->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE);</code>

By combining FETCH_GROUP and FETCH_UNIQUE, PDO will automatically group results by the specified column and set the first column as the key for each group, while excluding duplicate records. This eliminates the additional nesting level or the need for post-processing operations like current.

Example:

Consider the following sample dataset:

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

Using the recommended fetch method, we can retrieve the desired result array:

<code class="php">$result = $pdo->query('SELECT * FROM employee')->fetchAll(\PDO::FETCH_GROUP|\PDO::FETCH_UNIQUE);

print_r($result);</code>

This will output the following associative array:

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
    )
)

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