将树形层次结构在MySQL/PHP中转换为数组/JSON

Question

考虑到下面的文件夹层次结构，并将其保存到数据库表中：

         top-
            |- subtop1
            |- subtop2
               |- subsubtop1

查询返回：

      ID NAME      PARENT ID
      1, top,       null
      2, subtop1,      1
      3, subtop2,      1
      4, subsubtop1,   3

我想将其转换为数组，然后转换为以下JSON格式：

      {
          "folder":
          {
                "fold_id": 1, "fold_name": "top", "fold_parent_fold_id": null,
                "sub_folders":
                {
                      "folder":
                      {
                            "fold_id": 2, "fold_name": "subtop1", "fold_parent_fold_id": 1,
                            "sub_folders": {}
                       },
                      "folder":
                      {
                            "fold_id": 3, "fold_name": "subtop2", "fold_parent_fold_id": 1,
                            "sub_folders":
                            {
                                  "folder":
                                  {
                                        "fold_id": 4, "fold_name": "subsubtop1", "fold_parent_fold_id": 3,
                                        "sub_folders":
                                        {
                                        }
                                  }
                            }
                       }
                }
          }
      }

这是我的代码（一个类的一部分）：

    public function buildTree ( $tree, $dir ) : array
    {
        $retarr = array();
        foreach ($tree as $tnode)
        {
        var_dump($tnode);
            if ( $tnode['folder']['fold_id'] == $dir['fold_parent_fold_id'])
            {
                if ( count($tnode['folder']['fold_id']['subfolders']) == 0 )
                {
                    // Next line errors with " Cannot use a scalar value as an array "
                    $tnode['folder']['fold_id']['subfolders'] =  array($dir['fold_id']=>$dir);
                }
                else
                {
                    array_push($tnode['folder']['fold_id']['subfolders'], array($dir['fold_id']=>$dir)  );
                }
            }
            else
            {
               $tnode = $this->buildTree ($tnode, $dir);
            }
        var_dump($tnode);
            array_push($retarr, $tnode);
        }
        return ($retarr);
    }

这是从数据库获取扁平层次列表并调用buildTree函数逐步构建树形数组的函数：

    public function getFolderTree ( $foldID ) : array
    {
        $foldContent = "";

        $sql =<<<SQL

        SELECT  1 oby,
                fold_id,
                fold_name,
                fold_parent_fold_id
        FROM    (SELECT * FROM folders
                 ORDER BY fold_parent_fold_id, fold_id) items_sorted,
                (SELECT @iv := ?) initialisation
        WHERE   find_in_set(fold_parent_fold_id, @iv)
        AND     length(@iv := concat(@iv, ',', fold_id))
        UNION
        SELECT  0 oby,
                fold_id,
                fold_name,
                fold_parent_fold_id
        FROM    folders
        WHERE   fold_id = ?
        ORDER BY 1, 4, 3
SQL;

        $rows = $this->db->query($sql, $foldID, $foldID)->fetchAll();
        $tree_array = array();
        $dirs = array();

        foreach ($rows as $row)
        {
            $subs = array();
            //var_dump ( $row );
            if ( $row['fold_parent_fold_id'] == null || count($dirs) == 0 )
            {
                $dirs[$row['fold_id']] = array ( "folder" => $row, "subfolders" => $subs );
                $tree_array = $dirs;
            }
            else
            {
               $tree_array = $this->buildTree ($tree_array, $row);
            }
            var_dump ( $tree_array );
        }
        //var_dump($rows);
        return ( json_encode($tree_array) );
    }

如上所述，在buildTree函数中我遇到了一个错误：

                    // Next line errors with " Cannot use a scalar value as an array "
                    $tnode['folder']['fold_id']['subfolders'] =  array($dir['fold_id']=>$dir);

然而，我也不确定我是否走的是正确的道路。我尝试使用从数据库中查询的方式将数据提取为JSON格式，但没有成功。

- TenG

---

看起来是一个很好的 [mcve]，也有良好的编码尝试。 很久没有点赞问题了，我都快忘记怎么点赞了。 ;) - mickmackusa

预期结果无效。在第一个sub_folders级别中，您有多个名为folder的属性。属性名称必须在同一对象内唯一。 - M. Eriksson

Answer 1

一种方法是利用 OPP 结合构建自定义列表的标准方法。为了不使用任何 sql 查询，需要使用一个中间数据结构来保存初始关联。同时假设该关联已经正确设置且无环。请保留 HTML 标签。

//for initial storage and to avoid db-query(but here can be adjusted even with sql)
class MyList
{
    public $arr=array();

    public function addNode($node)
    {
       $this->arr[$node->id]=$node;
    }
    
    public function getChilldren($pid)
    {
        $list=array();
        foreach($this->arr as $node)
        {
            if($node->pid==$pid)
            {
                $list[]=$node;
            }
        }
        return $list;
    }
}

//base component
class Node
{
    public $id;
    public $name;
    public $pid;
    public function __construct($id,$value,$pid)
    {
        $this->id=$id;
        $this->name=$value;
        $this->pid=$pid;
    }
    public function __toString()
    {
        return $this->id.":".$this->name.":".$this->pid;
    }
}

//list implementation
class MyNode
{
  public $root;
  public $leafs=array();
  public function __construct($root)
  {
      $this->root = $root;
  }
  public function add(MyList $list, $pid)
  {
      if($list->arr[$pid]!=null)
      {
          $this->root = $list->arr[$pid];
          $this->leafs=array();
      }
      else
      {
          return null;
      }
      $data = array();
      $data[]=$this->root;
      
      $current = array_shift($data);
      while($current  !=null)
      {
          //echo $current."<br>";
          $children=$list->getChilldren($current->id);
          foreach($children as $child)
          {
              $data[]=$child;
              $parentList = [$this];
              $start = null;
              while($parentList != null)
              {
                  $start = array_shift($parentList);
                  if($start->root->id == $child->pid)
                  {
                      $start->leafs[] = new MyNode($child);
                      //end_loop
                      break;
                  }
                  
                  //next search through children
                  foreach($start->leafs as $next)
                  {
                      $parentList[] = $next;
                  }
              }    
          }
          $current = array_shift($data);
      }
     // var_dump($this);
  }
}

//run
//add associations
$arr = new MyList();
$arr->addNode(new Node(4,"n4",3));
$arr->addNode(new Node(5,"n4",4));
$arr->addNode(new Node(6,"n4",4));
$arr->addNode(new Node(7,"n4",4));
$arr->addNode(new Node(1,"n1",null));
$arr->addNode(new Node(2,"n2",1));
$arr->addNode(new Node(3,"n3",1));
//names have no importance when constructing the tree

var_dump($arr);

//make full-list
$run = new MyNode(null);
$run->add($arr,1);

echo "<pre>";
print_r($run);
echo "</pre>";

echo json_encode($run)."<br>";

//make partial-list from a subtree
$run2= new MyNode(null);
$run2->add($arr,4);
echo json_encode($run2)."<br>";

输出：

//run1-json
{
"root":{"id":1,"name":"n1","pid":null},
"leafs":[{"root":{"id":2,"name":"n2","pid":1},"leafs":[]},
         {"root":{"id":3,"name":"n3","pid":1},
          "leafs":[{"root":{"id":4,"name":"n4","pid":3},
                    "leafs":[{"root":{"id":5,"name":"n4","pid":4},"leafs":[]},
                             {"root":{"id":6,"name":"n4","pid":4},"leafs":[]},
                             {"root":{"id":7,"name":"n4","pid":4},"leafs":[]}]}]}]
}

//run1-array
MyNode Object
(
    [root] => Node Object
        (
            [id] => 1
            [name] => n1
            [pid] => 
        )

    [leafs] => Array
        (
            [0] => MyNode Object
                (
                    [root] => Node Object
                        (
                            [id] => 2
                            [name] => n2
                            [pid] => 1
                        )

                    [leafs] => Array
                        (
                        )

                )

            [1] => MyNode Object
                (
                    [root] => Node Object
                        (
                            [id] => 3
                            [name] => n3
                            [pid] => 1
                        )

                    [leafs] => Array
                        (
                            [0] => MyNode Object
                                (
                                    [root] => Node Object
                                        (
                                            [id] => 4
                                            [name] => n4
                                            [pid] => 3
                                        )

                                    [leafs] => Array
                                        (
                                            [0] => MyNode Object
                                                (
                                                    [root] => Node Object
                                                        (
                                                            [id] => 5
                                                            [name] => n4
                                                            [pid] => 4
                                                        )

                                                    [leafs] => Array
                                                        (
                                                        )

                                                )

                                            [1] => MyNode Object
                                                (
                                                    [root] => Node Object
                                                        (
                                                            [id] => 6
                                                            [name] => n4
                                                            [pid] => 4
                                                        )

                                                    [leafs] => Array
                                                        (
                                                        )

                                                )

                                            [2] => MyNode Object
                                                (
                                                    [root] => Node Object
                                                        (
                                                            [id] => 7
                                                            [name] => n4
                                                            [pid] => 4
                                                        )

                                                    [leafs] => Array
                                                        (
                                                        )

                                                )

                                        )

                                )

                        )

                )

        )

)

//run2-json
{"root":{"id":4,"name":"n4","pid":3},
 "leafs":[
            {"root":{"id":5,"name":"n4","pid":4},"leafs":[]},
            {"root":{"id":6,"name":"n4","pid":4},"leafs":[]},
            {"root":{"id":7,"name":"n4","pid":4},"leafs":[]}]}

- Traian GEICU

---

谢谢。我回到工作时会尝试这个，并让您知道结果。 - TenG

请告诉我它是如何工作的。它已经经过测试，应该是正确的，但仍然可以进行改进。 - Traian GEICU

非常感谢，这对我建立一个可行的模型帮助很大。 - TenG

Answer 2

看一下原生的 PHP 函数 json_encode()

https://www.php.net/manual/en/function.json-encode.php

它是一个内置的 PHP 函数，用于将 PHP 数组或对象转换为 JSON（JavaScript Object Notation）表示形式。

- chepakwak

---

这样做不会建立 OP 想要的层次结构，只会导致一个平面/一维的 JSON 对象。 - Markus AO

目前你的回答不够清晰，请编辑并添加更多细节，以帮助其他人理解它如何回答问题。你可以在帮助中心找到有关如何编写好答案的更多信息。 - Community