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)
                     );

 );

更新:
找到此链接,但我仍然很难将其转换为数组:
http://karwin.blogspot.com/2010/03/rendering-trees-with-closure-tables.html

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

好的,我编写了扩展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 |
| 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;

我还为行和行集定义了类. 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;
原文链接:https://www.f2er.com/php/135075.html

猜你在找的PHP相关文章