在使用 PDO 的结果中使用列的值作为索引 [英] Using value of a column as index in results using PDO
问题描述
我有一个名为brands"的 SQL 表，其中包含 id、name、url 列.在那个表中，我有这个数据集:
I have an SQL table called 'brands' with the columns id, name, url. In that table I have this data set:
1, Solidfloor, solidfloor;
2, Quickstep, quickstep;
4, Cleanfloor, cleanfloor;
5, Blue Dolphin, blue-dolphin;
6, Krono, krono;
8, Meister, meister;
我现在正在获取它们，并得到一个不错的数组作为回报，但是，我需要数组的索引不是递增的数字，而是该特定行的 id.我当然可以遍历结果集，但有没有更简洁的方法来做到这一点?
I'm fetching them all right now and I get a nice array in return, but, I need the index of the arrays to not be an incremented number, but the id of that particular row. I could of course loop through the result set, but is there a cleaner way of doing this?
推荐答案
Fetch as assoc
Fetch as assoc
对于手册:http://php.net/manual/en/pdostatement.fetchall.php
fetch_style
fetch_style
控制返回数组的内容，如 PDOStatement::fetch() 中所述.默认为 PDO::ATTR_DEFAULT_FETCH_MODE 的值(默认为 PDO::FETCH_BOTH)
Controls the contents of the returned array as documented in PDOStatement::fetch(). Defaults to value of PDO::ATTR_DEFAULT_FETCH_MODE (which defaults to PDO::FETCH_BOTH)
要从结果集中返回由单个列的所有值组成的数组，请指定 PDO::FETCH_COLUMN.您可以使用 column-index 参数指定所需的列.
To return an array consisting of all values of a single column from the result set, specify PDO::FETCH_COLUMN. You can specify which column you want with the column-index parameter.
要仅从结果集中获取单个列的唯一值，请按位或 PDO::FETCH_COLUMN 与 PDO::FETCH_UNIQUE.
To fetch only the unique values of a single column from the result set, bitwise-OR PDO::FETCH_COLUMN with PDO::FETCH_UNIQUE.
要返回按指定列的值分组的关联数组，请按位或 PDO::FETCH_COLUMN 与 PDO::FETCH_GROUP.
To return an associative array grouped by the values of a specified column, bitwise-OR PDO::FETCH_COLUMN with PDO::FETCH_GROUP.
最后一点是关键.它似乎没有完全记录(我可以找到)，但是您可以将 PDO::FETCH_ASSOC 与 PDO::FETCH_GROUP 结合使用，而不是 PDO::FETCH_COLUMN 来实现所需的结果:
That last bit is key. It doesn't seem to be completely documented (that I could find), but instead of PDO::FETCH_COLUMN, you can combine PDO::FETCH_ASSOC with PDO::FETCH_GROUP to achieve the desired result:
$PDOstmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP)
因此，鉴于上述数据:
$stmt = $PDO_obj->prepare('select * from brands');
$stmt->execute();
$result = $stmt->fetchAll(PDO::FETCH_ASSOC | PDO::FETCH_GROUP);
d($result);
结果:
array (6) [
'1' => array (1) [
array (2) [
'name' => string (10) "Solidfloor"
'url' => string (10) "solidfloor"
]
]
'2' => array (1) [
array (2) [
'name' => string (9) "Quickstep"
'url' => string (9) "quickstep"
]
]
'4' => array (1) [
array (2) [
'name' => string (10) "Cleanfloor"
'url' => string (10) "cleanfloor"
]
]
'5' => array (1) [
array (2) [
'name' => string (12) "Blue Dolphin"
'url' => string (12) "blue-dolphin"
]
]
'6' => array (1) [
array (2) [
'name' => string (5) "Krono"
'url' => string (5) "krono"
]
]
'8' => array (1) [
array (2) [
'name' => string (7) "Meister"
'url' => string (7) "meister"
]
]
]
( d() 只是来自kint 库的一个方便的调试函数，如var_dump() 或print_r() )
( d() is just a handy debugging function from the kint library, like var_dump() or print_r() )
请注意，用于索引数组的列将始终是结果中的第一列，因此您可以修改 select 语句以选择所需的列.还要注意，索引列将从每一行的数组中删除；为了解决这个问题，您可以将该列两次添加到您的选择语句中(即 select id,brands.* frombrands 等).
Note that the column used to index the array will always be the first column in the results, so you can modify your select statement to choose which column you want. And note also that the indexed column will be stripped out of each row's array; to get around that, you can add the column twice to your select statement (i.e., select id, brands.* from brands, etc.).
这里记录了更多参数:http://php.net/manual/en/pdostatement.fetch.php ，类似于 PDO::FETCH_UNIQUE 以确保每个索引只使用一次.
There are more parameters documented here: http://php.net/manual/en/pdostatement.fetch.php , like PDO::FETCH_UNIQUE to make sure that each index is used only once.
这篇关于在使用 PDO 的结果中使用列的值作为索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
