How can PDO\'s `fetchAll` method create an associative array from key-value pairs retrieved from a database?

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

Question:

When retrieving key-value pairs from a database, how can we obtain an associative array with values grouped by keys using PDO's fetchAll method?

For example, given the query:

SELECT `key`, `value` FROM `settings`;

We want to create an array like:

array('first_name' => 'Tom', 'last_name' => 'Jefferson')

Answer:

PDO offers a simple and efficient solution for this:

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

This approach:

• Executes the query and retrieves all rows into the $q PDOStatement object.
• Invokes fetchAll() with PDO::FETCH_KEY_PAIR to group the results by the first column (key) and assign the second column (value) as the corresponding value.
• Stores the associative array in the $r variable.

This method is optimized for this specific use case, as it retrieves the data with a single query and directly into the desired associative array format.

For PostgreSQL 9.1 and PHP 5.3.8 running on Windows 7 x64, this solution has been verified to work effectively.

The above is the detailed content of How can PDO\'s `fetchAll` method create an associative array from key-value pairs retrieved from a database?. For more information, please follow other related articles on the PHP Chinese website!