将数据库结果转换为数组?mysql

好的，我编写了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;来源：stack overflow