将数据库结果转换为数组

好的，我编写了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 aJOIN 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 | 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

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 arrayvar_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

LAST_INSERT_ID()

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;