php将数据库转为数组,php – 将数据库结果转换为数组

搜索热词

我刚刚为这个幻灯片共享中的第70页所示的组织查询分层数据的“Closure表”方式进行了更新/添加/删除部分：

http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back

我的数据库看起来像这样：

表分类：

ID Name

1 Top value

2 Sub value1

表类别树：

child parent level

1 1 0

2 2 0

2 1 1

但是,从单个查询中将完整的树作为多维数组返回时,我遇到了一些问题.

这是我想要回来的：

array (

'topvalue' = array (

'Subvalue','Subvalue2','Subvalue3)

);

);

更新：

找到此链接,但我仍然很难将其转换为数组：

@L_301_1@

Update2：

我现在可以为每个类别添加深度,如果这可以有任何帮助.

@H_403_27@

好的,我编写了扩展Zend Framework DB表,行和行集类的PHP类.无论如何,我一直在开发这个,因为我在几个星期后在

PHP Tek-X讲关于分层数据模型.

我不想将我的所有代码发布到Stack Overflow,因为如果我这样做,他们会隐式获得Creative Commons的许可.更新：我将我的代码提交到Zend Framework extras incubator,我的演示文稿是Models for Hierarchical Data with SQL and PHP在slideshare.

我将用伪代码描述解决方案.我使用动物分类学作为测试数据,从ITIS.gov下载.该表是长名称：

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;

连接到闭包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行,在封闭中有4,299,883行,它运行得非常好：

+--------------------+----------+

| Status | Duration |

+--------------------+----------+

| starting | 0.000257 |

| @R_301_126@ 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;

我还为行和行集定义了类. Rowset基本上是一个行数组. A Row包含行数据的关联数组,并且还包含其子项的Rowset.叶节点的子行Rowset为空.

行和行集还定义了名为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','_children' =>

array (

0 =>

array (

'tsn' => '180210','completename' => 'Castoridae','_parent' => '180134',1 =>

array (

'tsn' => '180135','completename' => 'Sciuridae',2 =>

array (

'tsn' => '180131','completename' => 'Aplodontiidae',2 =>

array (

'tsn' => '573166','completename' => 'Anomaluromorpha','_children' =>

array (

0 =>

array (

'tsn' => '573168','completename' => 'Anomaluridae','_parent' => '573166',1 =>

array (

'tsn' => '573169','completename' => 'Pedetidae',3 =>

array (

'tsn' => '180273','completename' => 'Myomorpha','_children' =>

array (

0 =>

array (

'tsn' => '180399','completename' => 'Dipodidae','_parent' => '180273',1 =>

array (

'tsn' => '180360','completename' => 'Muridae',2 =>

array (

'tsn' => '180231','completename' => 'Heteromyidae',3 =>

array (

'tsn' => '180213','completename' => 'Geomyidae',4 =>

array (

'tsn' => '584940','completename' => 'Myoxidae',4 =>

array (

'tsn' => '573167','completename' => 'Sciuravida','_children' =>

array (

0 =>

array (

'tsn' => '573170','completename' => 'Ctenodactylidae','_parent' => '573167',)

重新评论有关计算深度 – 或每条路径的实际长度.

假设您刚刚在表中插入了一个包含实际节点的新节点(上例中的longname),新节点的id由MysqL中的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(),0;