duanfan1869
2011-12-08 03:16
浏览 20
已采纳

如何从数据库中提取和显示分层数据?

I have two tables.

The chapters table has the columns id and name.

The chapters_chapter table has columns id, master_id, and slave_id.

Lets say that the chapters table has 7 records:

id     name
1      test01
2      test02
3      test03
4      test04
5      test05
6      test06
7      test07

And in the chapters_chapters table I have these records:

id    master_id    slave_id
1     1            5
2     1            6
3     6            7
4     7            2

Given that data, how can I extract the hierarchy of that data so that it looks like this?

test01
  test05
  test06
    test07
      test02
test03
test04

图片转代码服务由CSDN问答提供 功能建议

我有两个表。

章节 table的列为 id name

chapters_chapter 表的列为 id master_id slave_id

让我们说章节表有7个 记录:

  id name 
1 test01 
2 test02 
3 test03 
4 test04 
5 test05 
6 test06 
7 test07 
   \  n 
 

chapters_chapters 表中,我有以下记录:

  id master_id slave_id 
1 1 5 
2 1 6 
3  6 7 
4 7 2 
   
 
 

鉴于这些数据,我如何提取该数据的层次结构,使其看起来像这样? \ n

  test01 
 test05 
 test06 
 test07 
 test02 
test03 
test04 
   
 
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duanhuan2301 2011-12-08 06:31
    已采纳

    So this was kind of a pain because of the fact that we had to have the hierarchy stored in the DB. Because of this, each item can have multiple children, and each child can have multiple parents.

    This second part means we cannot simply loop through the list once and be done with it. We might have to insert an item in multiple places in the hierarchy. While you probably won't actually structure your data that way, the database schema you've described supports this scenario, so the code must support it too.

    Here's a high-level version of the algorithm:

    1. Query both tables
    2. Create a map (array) of a parent (number) to its children (another array)
    3. Create a set of items that are children (array of numbers)
    4. Create a function that displays a single item, indenting it to the desired depth.
      If that item has children, this function increases the depth by one, and calls itself recursively
    5. Loop through all items that aren't children (root items).
      Call the function for each of those items, with a desired depth of 0 (no indent).

    Here's two hours work. Enjoy :)

    Note that I stuck it within a <pre> block, so you might have to mess with how the indentation is done (output something other than two spaces, mess with the style of the divs, etc).

    <?php
      $con = mysql_connect("localhost", "test_user", "your_password");
      if(!$con)
      {
        die("could not connect to DB: " . mysql_error());
      }
      mysql_select_db("your_db", $con);
    
      // get chapters
      $chapters = array();
    
      $result = mysql_query("SELECT * FROM chapters");
      while($row = mysql_fetch_array($result))
      {
        $id = $row["id"];
        $name = $row["name"];
        $chapters[$id] = $name;
      }
    
      // get chapters_chapters - We'll call it "parent/child" instead of "master/slave"
      $parent_child_map = array();
      $is_child = array();
    
      $result = mysql_query("SELECT master_id, slave_id FROM chapters_chapters");
      while($row = mysql_fetch_array($result))
      {
        $parent_id = $row["master_id"];
        $child_id = $row["slave_id"];
    
        $children = $parent_child_map[$parent_id];
        if($children == null)
        {
          $children = array();
        }
    
        $children[] = $child_id;
        $parent_child_map[$parent_id] = $children;
    
        $is_child[$child_id] = true;
      }
    
      // display item hierarchically
      $display_item_and_children = function($id, $name, $depth)
        use ($chapters, $parent_child_map, &$display_item_and_children)
      {
        echo "<div><pre>";
    
        // indent up to depth
        for($i = 0; $i < $depth; $i++)
        {
          echo "  ";
        }
    
        echo "id: " . $id
          . " name: " . $name
          . "</pre></div>";
    
        // if there are children, display them recursively
        $children = $parent_child_map[$id];
        if($children != null)
        {
          foreach($children as $child_id)
          {
            $child_name = $chapters[$child_id];
            $display_item_and_children($child_id, $child_name, $depth + 1);
          }
        }
      };
    
      // display all top-level items hierarchically
      foreach($chapters as $id => $name)
      {
        // if it is a top-level item, display it
        if($is_child[$id] != true)
        {
          $display_item_and_children($id, $name, 0);
        }
      }
    
      mysql_close($con);
    ?>
    

    And here's a screenshot:

    Output of the program

    点赞 评论
  • douxun1407 2011-12-08 03:27

    The question becomes how complex you want your solution to be. I'd do it with the following pseudo code.

    • SELECT all the chapters
    • SELECT all the *chapters_chapters*
    • loop over the chapters to create an array chapter objects
    • loop over the `chapters_chapters* and create the relationships using the chapter objects

    Essentially you're creating a link-list.

    点赞 评论

相关推荐 更多相似问题