将数据库结果转换为数组
我刚刚为组织此表中第70页上显示的查询层次结构数据的“关闭表”方法做了“更新/添加/删除”部分：http ://www.slideshare.net/billkarwin/sql-antipatterns-
strike -背部
我的数据库如下所示：
ID Name1 Top value
2 Sub value1
child parent level1 1 0
2 2 0
2 1 1
但是，从单个查询获取整棵树作为多维数组时，我遇到了一个问题。
这是我想回来的东西：
array ( 'topvalue' = array (
'Subvalue',
'Subvalue2',
'Subvalue3)
);
);
找到了此链接，但是我仍然很难将其转换为数组：http : //karwin.blogspot.com/2010/03/rendering-
trees-with-closure-tables.html
我现在可以为每个类别添加深度，如果有帮助的话。
回答：
好的，我编写了PHP类来扩展Zend Framework DB表，行和行集类。无论如何，我一直在开发它，因为我在PHP
Tek-X上谈论了两周有关分层数据模型的内容。
我不想将我所有的代码发布到Stack Overflow，因为如果这样做，它们将隐式地获得知识共享许可。 我将代码提交给Zend
Framework
Extras孵化器，在幻灯片共享中，我的演示文稿是带有SQL和PHP的分层数据模型。
我将用伪代码描述解决方案。我使用的是动物学分类学作为测试数据，可从ITIS.gov下载。该表是longnames：
CREATE TABLE `longnames` ( `tsn` int(11) NOT NULL,
`completename` varchar(164) NOT NULL,
PRIMARY KEY (`tsn`),
KEY `tsn` (`tsn`,`completename`)
)
我为分类法层次结构中的路径创建了一个 ：
CREATE TABLE `closure` ( `a` int(11) NOT NULL DEFAULT '0', -- ancestor
`d` int(11) NOT NULL DEFAULT '0', -- descendant
`l` tinyint(3) unsigned NOT NULL, -- levels between a and d
PRIMARY KEY (`a`,`d`),
CONSTRAINT `closure_ibfk_1` FOREIGN KEY (`a`) REFERENCES `longnames` (`tsn`),
CONSTRAINT `closure_ibfk_2` FOREIGN KEY (`d`) REFERENCES `longnames` (`tsn`)
)
给定一个节点的主键，您可以通过以下方式获取其所有后代：
SELECT d.*, p.a AS `_parent`FROM longnames AS a
JOIN closure AS c ON (c.a = a.tsn)
JOIN longnames AS d ON (c.d = d.tsn)
LEFT OUTER JOIN closure AS p ON (p.d = d.tsn AND p.l = 1)
WHERE a.tsn = ? AND c.l <= ?
ORDER BY c.l;
联接要closure AS p包括每个节点的父ID。
该查询很好地利用了索引：
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
| 1 | SIMPLE | a | const | PRIMARY,tsn | PRIMARY | 4 | const | 1 | Using index; Using filesort |
| 1 | SIMPLE | c | ref | PRIMARY,d | PRIMARY | 4 | const | 5346 | Using where |
| 1 | SIMPLE | d | eq_ref | PRIMARY,tsn | PRIMARY | 4 | itis.c.d | 1 | |
| 1 | SIMPLE | p | ref | d | d | 4 | itis.c.d | 3 | |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+
鉴于我有490,032行longnames和4,299,883行closure，它的运行时间相当不错：
+--------------------+----------+| Status | Duration |
+--------------------+----------+
| starting | 0.000257 |
| Opening tables | 0.000028 |
| System lock | 0.000009 |
| Table lock | 0.000013 |
| init | 0.000048 |
| optimizing | 0.000032 |
| statistics | 0.000142 |
| preparing | 0.000048 |
| executing | 0.000008 |
| Sorting result | 0.034102 |
| Sending data | 0.001300 |
| end | 0.000018 |
| query end | 0.000005 |
| freeing items | 0.012191 |
| logging slow query | 0.000008 |
| cleaning up | 0.000007 |
+--------------------+----------+
现在，我对上述SQL查询的结果进行后处理，根据层次结构（伪代码）将行分类为子集：
while ($rowData = fetch()) { $row = new RowObject($rowData);
$nodes[$row["tsn"]] = $row;
if (array_key_exists($row["_parent"], $nodes)) {
$nodes[$row["_parent"]]->addChildRow($row);
} else {
$top = $row;
}
}
return $top;
我还为“行”和“行集”定义类。行集基本上是行的数组。行包含行数据的关联数组，还包含其子级的行集。叶节点的子行集为空。
行和行集还定义了称为的方法toArrayDeep()，这些方法以纯数组的形式递归地转储其数据内容。
然后，我可以像这样一起使用整个系统：
// Get an instance of the taxonomy table data gateway $tax = new Taxonomy();
// query tree starting at Rodentia (id 180130), to a depth of 2
$tree = $tax->fetchTree(180130, 2);
// dump out the array
var_export($tree->toArrayDeep());
输出如下：
array ( 'tsn' => '180130',
'completename' => 'Rodentia',
'_parent' => '179925',
'_children' =>
array (
0 =>
array (
'tsn' => '584569',
'completename' => 'Hystricognatha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '552299',
'completename' => 'Hystricognathi',
'_parent' => '584569',
),
),
),
1 =>
array (
'tsn' => '180134',
'completename' => 'Sciuromorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '180210',
'completename' => 'Castoridae',
'_parent' => '180134',
),
1 =>
array (
'tsn' => '180135',
'completename' => 'Sciuridae',
'_parent' => '180134',
),
2 =>
array (
'tsn' => '180131',
'completename' => 'Aplodontiidae',
'_parent' => '180134',
),
),
),
2 =>
array (
'tsn' => '573166',
'completename' => 'Anomaluromorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '573168',
'completename' => 'Anomaluridae',
'_parent' => '573166',
),
1 =>
array (
'tsn' => '573169',
'completename' => 'Pedetidae',
'_parent' => '573166',
),
),
),
3 =>
array (
'tsn' => '180273',
'completename' => 'Myomorpha',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '180399',
'completename' => 'Dipodidae',
'_parent' => '180273',
),
1 =>
array (
'tsn' => '180360',
'completename' => 'Muridae',
'_parent' => '180273',
),
2 =>
array (
'tsn' => '180231',
'completename' => 'Heteromyidae',
'_parent' => '180273',
),
3 =>
array (
'tsn' => '180213',
'completename' => 'Geomyidae',
'_parent' => '180273',
),
4 =>
array (
'tsn' => '584940',
'completename' => 'Myoxidae',
'_parent' => '180273',
),
),
),
4 =>
array (
'tsn' => '573167',
'completename' => 'Sciuravida',
'_parent' => '180130',
'_children' =>
array (
0 =>
array (
'tsn' => '573170',
'completename' => 'Ctenodactylidae',
'_parent' => '573167',
),
),
),
),
)
关于计算深度-或实际上每个路径的长度，发表您的评论。
假设您刚刚在表中插入了一个包含实际节点的新节点（longnames在上面的示例中），则新节点的ID由LAST_INSERT_ID()MySQL
返回，否则您可以通过某种方式获取它。
INSERT INTO Closure (a, d, l) SELECT a, LAST_INSERT_ID(), l+1 FROM Closure
WHERE d = 5 -- the intended parent of your new node
UNION ALL SELECT LAST_INSERT_ID(), LAST_INSERT_ID(), 0;
以上是 将数据库结果转换为数组 的全部内容， 来源链接： utcz.com/qa/427549.html
