PDO FetchAll: Group Key-Value Pairs into an Associative Array

In database queries that return key-value pairs, it is often convenient to retrieve the data as an associative array, with the values mapped to their respective keys. While there are common methods to achieve this, such as using fetchAll(PDO::FETCH_ASSOC) followed by manual iteration to create the array, this approach can be cumbersome.

An alternative solution, specifically designed for this purpose, is to use the fetchAll(PDO::FETCH_KEY_PAIR) option. This method automatically creates an associative array with the query results, mapping the first column to the keys and the second column to the values.

To demonstrate this, consider a query that retrieves name and value columns from the settings table:

$q = $db->query("SELECT `name`, `value` FROM `settings`;");
$r  = $q->fetchAll(PDO::FETCH_KEY_PAIR);

In this case, if the database contained rows like ('first_name', 'Tom') and ('last_name', 'Jeferson'), the resulting $r array would be:

Array(
    'first_name' => 'Tom',
    'last_name'  => 'Jeferson'
)

This method provides a straightforward and efficient way to retrieve key-value pairs into an associative array, eliminating the need for manual array creation. It is supported by modern PHP versions and some popular databases like PostgreSQL.