PDO associative arrays - return associative

Question

I have this code:

$dbInstance = DB_Instance::getDBO();
$statement = $dbInstance->prepare("SELECT id, name FROM language ORDER BY id");
$statement->execute();      
$rows = $statement->fetchAll(); 

//Create associative array wuth id set as an index in array
$languages = array();
foreach($rows as $r) {
    $languages[$r['id']] = $r['name'];
}
return $languages;

I can't figure out how to use PDO-statement to achieve the same result that array $languages produces. I've tried some different fetch_styles.

I've tried some different styles and I could get like:

[0] svenska
[1] engelska

but I want like:

[1] svenska
[2] engelska

(where 1 and 2 are the values of id in database)

I guess I could create a function and call that with FETCH_FUNC but I'm not sure that would be so great either.

Is the above the best/cleanest way to do it?

If you want to return an associative array with PDO, do `$rows = $statement->fetchAll(PDO::FETCH_ASSOC);` Then you can reference by `$rows[$row_num][$col_name]` — jdstankosky, Mar 20 '13 at 15:53
I understand what you are asking for now. Please see my updated answer to address your particular inquiry. — jdstankosky, Mar 20 '13 at 16:49

score 28 · Answer 1 · edited Nov 21 '14 at 08:37

28

Everybody forgot about the

$sth->fetchAll(PDO::FETCH_KEY_PAIR);

edited Nov 21 '14 at 08:37

Gottlieb Notschnabel

9,408
18
74
116

answered Feb 13 '14 at 19:50

Miron Yanovskiy

640
7
12

8

If you are working with more than one column, this might be a better solution: `$STH->fetchAll(PDO::FETCH_ASSOC);` – dangre00 Nov 23 '16 at 00:20
This is the best answer. @dangre00, that comment, while true, is outside the scope of the OP's question and simply muddies the water. – JBH Apr 30 '18 at 20:24
1

This is the correct answer. It requires exactly 2 columns. First column is the key. Second column is the value. For example: `"SELECT some_key, some_value FROM some_table GROUP BY some_key;"` – OXiGEN Mar 19 '19 at 18:35
Documentation https://www.php.net/manual/en/pdo.constants.php#pdo.constants.fetch-key-pair "**PDO::FETCH_KEY_PAIR** Fetch a two-column result into an array where the first column is a key and the second column is the value. Available since PHP 5.2.3." Example: https://www.php.net/manual/en/pdostatement.fetchall.php#120517 – Gleb Kemarsky Oct 30 '20 at 09:43

jdstankosky · Accepted Answer · 2017-08-10T13:48:21.220

Not really sure if there's any better way. You could try this?

$rows = $statement->fetchAll(PDO::FETCH_ASSOC);

$languages = array();

function getLangs($col, $row) {
     $languages[$col['id']] = $col['name'];
}

array_walk($rows, 'getLangs');

There's nothing wrong with foreach loops. I'd actually use what you've got. It's hard to get cleaner than that...

UPDATE:

After carefully re-reading your question, what you REALLY should be asking is whether you can format your QUERY in such a way that the results are returned in a different format.

The way that your normal SELECT query is returned is thusly:

+----+----------+
| id |     name |
+----+----------+
|  1 |  svenska |
|  2 | engelska |
| .. |      ... |
| .. |      ... |
+----+----------+

$row = array(
    row_1 => array(
        id   => "1",
        name => "svenska"
    ),
    row_2 => array(
        id   => "2",
        name => "engelska"
    ),
    row_3 => array(
        id   => "...",
        name => "..."
    ),
    row_4 => array(
        id   => "...",
        name => "..."
    )
)

$row[$row_number][$column_name] = $value

What you're asking for is for some way to return your query results like THIS:

// Query result is only one row, with each 'id' as column name
// And the 'name' from the same row as it's value...

+---------+----------+-----+-----+-----+
|       1 |        2 | ... | ... | ... |
+---------+----------+-----+-----+-----+
| svenska | engelska | ... | ... | ... |
+---------+----------+-----+-----+-----+

$row = array(
    row_1 => array(
          1 => "svenska",
          2 => "engelska",
        ... => "...",
        ... => "...",
        ... => "..."
    )
)

$languages = $row[row_1];
$languages[$id] = $name;

I'm not entirely sure you CAN do this in SQL, to be perfectly honest. I would also recommend against it, even if you could. It would be horrendous for a scaling table. If your table is static, then why not format it in the way I just mentioned to begin with? Why not just have it in a static PHP array in an include file?

Thanks a LOT! :-) What do you mean. Why should I include this in a static PHP array in an include-file? What's the point of that? — bestprogrammerintheworld, Mar 21 '13 at 19:31
@bestprogrammerintheworld I didn't know if it was a static table, as in, the languages will always be there, and the only reason they're in the database is to look them up with a query, why not just create an array of them in your script manually (ie., `include "lang.php";`? Again, I have no other knowledge about the table in question, so I'm just inferring here. — jdstankosky, Mar 22 '13 at 13:17

score 6 · Answer 3 · edited Dec 11 '13 at 15:54

6

I think you might be looking for $result = $sth->fetch(PDO::FETCH_ASSOC);

Reference: http://php.net/manual/en/pdostatement.fetch.php

[edit] oops to late :)

edited Dec 11 '13 at 15:54

aksu

5,221
5
24
39

answered Mar 20 '13 at 16:00

tattooedgeek

518
1
8
22

yeah I was like "damn got me".. but I'm not so sure with time... hmmm lol – tattooedgeek Mar 20 '13 at 17:10

score 0 · Answer 4 · edited Oct 28 '20 at 20:26

0

<?php
$dbhost = 'somehost';
$dbuser = 'someuser';
$dbpw = 'somepw';
$dbname = 'somename';
$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,];
$dsn = "mysql:host=$dbhost;dbname=$dbname;";
$pdo = new PDO($dsn, $dbuser, $dbpw, $options);

$data = $pdo->query("SELECT * FROM language")->fetchAll(\PDO::FETCH_UNIQUE);
var_dump($data);

FETCH_UNIQUE does the trick...

edited Oct 28 '20 at 20:26

Your Common Sense

156,878
40
214
345

answered Oct 28 '20 at 20:18

lvl

1

PDO associative arrays - return associative

4 Answers4

Linked

Related