PDO associative arrays - return associative
Solution 1
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?
Solution 2
Everybody forgot about the
$sth->fetchAll(PDO::FETCH_KEY_PAIR);
Solution 3
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 :)
Related videos on Youtube
bestprogrammerintheworld
I'm developing both code and my own company Wibergs Web. I live in Skövde, Sweden - and I'm enjoying life even though I haven't seen any polar bears here recently ;-)
Updated on July 31, 2021Comments
-
bestprogrammerintheworld almost 3 years
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?
-
jdstankosky over 11 yearsIf 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]
-
-
tattooedgeek over 11 yearsyeah I was like "damn got me".. but I'm not so sure with time... hmmm lol
-
bestprogrammerintheworld over 11 yearsThanks 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?
-
jdstankosky over 11 years@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. -
dangre00 over 7 yearsIf you are working with more than one column, this might be a better solution:
$STH->fetchAll(PDO::FETCH_ASSOC);
-
JBH about 6 yearsThis is the best answer. @dangre00, that comment, while true, is outside the scope of the OP's question and simply muddies the water.
-
OXiGEN over 5 yearsThis 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;"
-
Gleb Kemarsky over 3 yearsDocumentation php.net/manual/en/… "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: php.net/manual/en/pdostatement.fetchall.php#120517