Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PDO fetchAll group key-value pairs into assoc array

Tags:

php

mysql

pdo

Every now and then, I get into a situation when I have a query similar in kind to:

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

In this case, I want to get an associative array, using values of key & value as respective entries of that array, e.g. if the database contained: ('first_name', 'Tom'), ('last_name', 'Jeferson'), the array should be array('first_name' => 'Tom', 'last_name' => 'Jeferson');.

The most common way to do this is:

$settings_flat = $db     ->query("SELECT `name`, `value` FROM `settings`;")     ->fetchAll(PDO::FETCH_ASSOC);  $settings   = array();  foreach ($settings_flat as $setting) {     $settings[$setting['name']] = $setting['value']; } 

*The other way to do this is by calling fetchAll(PDO::FETCH_COLUMN) two times & then using array_combine to create the array. However, since it involves two calls two the database, I leave out this as an option.

Is there another way to do this?

like image 565
Gajus Avatar asked Sep 16 '11 22:09

Gajus


1 Answers

For your problem there is pretty ready solution, that is:

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

Works for me, on PostgreSQL 9.1, and PHP 5.3.8 running on Windows 7 x64.

like image 97
devdRew Avatar answered Oct 01 '22 02:10

devdRew