将数据库结果转为数组

Question

我刚刚为组织查询分层数据的“闭包表”方式制作了更新/添加/删除部分，如本幻灯片第 70 页所示： http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back

我的数据库如下所示：

表类别：

ID         Name
1          Top value
2          Sub value1

表 CategoryTree：

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

更新2： 我现在能够为每个类别添加深度，如果这有任何帮助的话。

Answer 1

好的，我已经编写了扩展 Zend Framework DB 表、行和行集类的 PHP 类。无论如何，我一直在开发这个，因为几周后我将在 PHP Tek-X 上谈论分层数据模型。

我不想将我的所有代码发布到 Stack Overflow，因为如果我这样做，它们就会隐式获得知识共享许可。 更新：我将代码提交到Zend Framework extras 孵化器< /a> 我的演讲是使用 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;

与闭包 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 |                             |
+----+-------------+-------+--------+---------------+---------+---------+----------+------+-----------------------------+

鉴于我在 longnames 中有 490,032 行，在 closure 中有 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;

我还为 Rows 和 Rowsets 定义了类。 Rowset 基本上是一个行数组。 Row 包含行数据的关联数组，还包含其子项的 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',
      '_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',
        ),
      ),
    ),
  ),
)

---

回复您关于计算深度或每条路径的实际长度的评论。

假设您刚刚将一个新节点插入到保存实际节点的表中（上例中的长名称），则新节点的 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;

Answer 2

建议的解决方案

下面的示例提供的内容比您要求的要多一些，但这是一种非常好的方法，并且仍然演示了每个阶段的信息来自何处。

它使用以下表结构：

+--------+------------------+------+-----+---------+----------------+
| Field  | Type             | Null | Key | Default | Extra          |
+--------+------------------+------+-----+---------+----------------+
| id     | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| parent | int(10) unsigned | NO   |     | NULL    |                |
| name   | varchar(45)      | NO   |     | NULL    |                |
+--------+------------------+------+-----+---------+----------------+

这里是：

<?php

    // Connect to the database
    mysql_connect('localhost', 'root', '');
    mysql_select_db('test');

    echo '<pre>';

    $categories = Category::getTopCategories();
    print_r($categories);

    echo '</pre>';

class Category
{
    /**
     * The information stored in the database for each category
     */
    public $id;
    public $parent;
    public $name;

    // The child categories
    public $children;

    public function __construct()
    {
        // Get the child categories when we get this category
        $this->getChildCategories();
    }

    /**
     * Get the child categories
     * @return array
     */
    public function getChildCategories()
    {
        if ($this->children) {
            return $this->children;
        }
        return $this->children = self::getCategories("parent = {$this->id}");
    }

    ////////////////////////////////////////////////////////////////////////////

    /**
     * The top-level categories (i.e. no parent)
     * @return array
     */
    public static function getTopCategories()
    {
        return self::getCategories('parent = 0');
    }

    /**
     * Get categories from the database.
     * @param string $where Conditions for the returned rows to meet
     * @return array
     */
    public static function getCategories($where = '')
    {
        if ($where) $where = " WHERE $where";
        $result = mysql_query("SELECT * FROM categories$where");

        $categories = array();
        while ($category = mysql_fetch_object($result, 'Category'))
            $categories[] = $category;

        mysql_free_result($result);
        return $categories;
    }
}

测试用例

在我的数据库中，我有以下行：

+----+--------+-----------------+
| id | parent | name            |
+----+--------+-----------------+
|  1 |      0 | First Top       |
|  2 |      0 | Second Top      |
|  3 |      0 | Third Top       |
|  4 |      1 | First Child     |
|  5 |      1 | Second Child    |
|  6 |      2 | Third Child     |
|  7 |      2 | Fourth Child    |
|  8 |      4 | First Subchild  |
|  9 |      4 | Second Subchild |
+----+--------+-----------------+

因此脚本输出以下（冗长）信息：

Array
(
    [0] => Category Object
        (
            [id] => 1
            [parent] => 0
            [name] => First Top
            [children] => Array
                (
                    [0] => Category Object
                        (
                            [id] => 4
                            [parent] => 1
                            [name] => First Child
                            [children] => Array
                                (
                                    [0] => Category Object
                                        (
                                            [id] => 8
                                            [parent] => 4
                                            [name] => First Subchild
                                            [children] => Array
                                                (
                                                )

                                        )

                                    [1] => Category Object
                                        (
                                            [id] => 9
                                            [parent] => 4
                                            [name] => Second Subchild
                                            [children] => Array
                                                (
                                                )

                                        )

                                )

                        )

                    [1] => Category Object
                        (
                            [id] => 5
                            [parent] => 1
                            [name] => Second Child
                            [children] => Array
                                (
                                )

                        )

                )

        )

    [1] => Category Object
        (
            [id] => 2
            [parent] => 0
            [name] => Second Top
            [children] => Array
                (
                    [0] => Category Object
                        (
                            [id] => 6
                            [parent] => 2
                            [name] => Third Child
                            [children] => Array
                                (
                                )

                        )

                    [1] => Category Object
                        (
                            [id] => 7
                            [parent] => 2
                            [name] => Fourth Child
                            [children] => Array
                                (
                                )

                        )

                )

        )

    [2] => Category Object
        (
            [id] => 3
            [parent] => 0
            [name] => Third Top
            [children] => Array
                (
                )

        )

)

示例用法

如果您是，我建议创建某种递归函数将根据数据创建菜单：

function outputCategories($categories, $startingLevel = 0)
{
    $indent = str_repeat("    ", $startingLevel);

    foreach ($categories as $category)
    {
        echo "$indent{$category->name}\n";
        if (count($category->children) > 0)
            outputCategories($category->children, $startingLevel+1);
    }
}

$categories = Category::getTopCategories();
outputCategories($categories);

这将输出以下内容：

First Top
    First Child
        First Subchild
        Second Subchild
    Second Child
Second Top
    Third Child
    Fourth Child
Third Top

享受

Answer 3

我喜欢 icio 的答案，但我更喜欢数组的数组，而不是对象的数组。这是他的脚本，经过修改后可以在不创建对象的情况下工作：

<?php

require_once('mysql.php');

echo '<pre>';

$categories = Taxonomy::getTopCategories();
print_r($categories);

echo '</pre>';

class Taxonomy
{ 


public static function getTopCategories()
{
    return self::getCategories('parent_taxonomycode_id = 0');
}

public static function getCategories($where = '')
{
    if ($where) $where = " WHERE $where";
    $result = mysql_query("SELECT * FROM taxonomycode $where");

    $categories = array();
   // while ($category = mysql_fetch_object($result, 'Category'))
    while ($category = mysql_fetch_array($result)){
    $my_id = $category['id'];
    $category['children'] = Taxonomy::getCategories("parent_taxonomycode_id = $my_id");
            $categories[] = $category;
        }

    mysql_free_result($result);
    return $categories;
  }
 }

我认为公平地说，我的回答和 icios 都没有直接解决您的问题。它们都依赖于主表中的父 id 链接，并且不使用闭包表。但是，递归查询数据库绝对是可行的方法，但不是递归传递父 id，而是必须传递父 id 和深度级别（每次递归应增加 1），以便查询每个级别都可以使用父级+深度从闭包表中获取直接父级信息，而不是将其放在主表中。

哈特哈，
-FT

Answer 4

当您希望输出为无序列表时，您可以按如下方式更改 outputCategories 方法（基于数组中的 ftrotters 数组）：

public function outputCategories($categories, $startingLevel = 0)
{
    echo "<ul>\n";
    foreach ($categories as $key => $category)
    {
        if (count($category['children']) > 0)
        {
            echo "<li>{$category['name']}\n";
            $this->outputCategories($category['children'], $startingLevel+1);
            echo "</li>\n";
        }
        else
        {
            echo "<li>{$category['name']}</li>\n";
        }
    }
    echo "</ul>\n";
}

Answer 5

抱歉，但我认为您无法从（或任何）数据库查询中获取多维数组。