使用SQL和PHP从不同的引用表中获取分层树

I have multiple tables all with a similar basic structure:

biopsy_p0
id | biopsy_id    | introduced

biopsy_p1
id | biopsy_p0_id | introduced

biopsy_p2
id | biopsy_p1_id | introduced

My goal is to get a treeview of the dependencies bewteen

biopsy_p0.id->biopsy_p1.biopsy_p0_id->biopsy_p2.biopsy_p1_id

I tried to do it only with SQL but as you can see from my question I am not very experienced in it. Everything I could find so far are references about about hierarchical trees. But those use always only one table with internal references.

--- Update: I now got it working with PHP it is really not a nice solution and i hoped I could make it with SQL so that would be a bit better to extend:

PHP code:

  $database = DatabaseFactory::getFactory()->getConnection();
      // Get all p0 element asociated with the biopsy
      $sql = "SELECT *
              FROM biopsy_p0
              WHERE biopsy_id = :id";
      $query = $database->prepare($sql);
      $query->execute(array(':id' => $id));
      $p0 = $query->fetchAll();

      // Get all p1 elements
      $sql="SELECT *
            FROM biopsy_p0 as p0
            RIGHT JOIN biopsy_p1 as p1
            ON p0.id=p1.biopsy_p0_id
            WHERE biopsy_id = :id;";

      $query = $database->prepare($sql);
      $query->execute(array(':id' => $id));
      $p1 = $query->fetchAll();

      for ($i=0; $i < count($p0); $i++)
      {
        $p1Array = new ArrayObject();
        foreach ($p1 as $key => $value)
        {
          if ($value->biopsy_p0_id == $p0[$i]->id)
          {
             $p1Array->append($value);
          }
          $p0[$i]->p1 = $p1Array;
        }
        unset($p1Array);
      }
      if ($p0 != NULL){
        return $p0;
      }
      return FALSE;

Result: This is exactly what i need but the PHP is messy and it complexity increases with each child level I would like to check.

SQL Data:

CREATE TABLE IF NOT EXISTS `biopsy` (
  `id` int(11) unsigned NOT NULL,
  `creation_date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy` (`id`, `creation_date`) VALUES
(226, '2015-03-08'),
(227, '2015-03-08');

CREATE TABLE IF NOT EXISTS `biopsy_p0` (
`id` int(11) unsigned NOT NULL,
  `biopsy_id` int(11) unsigned NOT NULL,
  `introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy_p0` (`id`, `biopsy_id`, `introduced`) VALUES
(1, 226, '2014-12-31'),
(2, 226, '2014-12-31'),
(3, 226, '2014-12-31'),
(4, 227, '2015-03-14'),
(5, 255, '2015-03-10'),
(6, 255, '2015-03-12');

CREATE TABLE IF NOT EXISTS `biopsy_p1` (
`id` int(11) unsigned NOT NULL,
  `biopsy_p0_id` int(11) unsigned NOT NULL,
  `introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy_p1` (`id`, `biopsy_p0_id`, `introduced`) VALUES
(1, 1, '2015-03-18'),
(2, 2, '2015-03-31'),
(3, 1, '2015-03-17'),
(4, 1, '2015-03-18'),
(5, 3, '2015-03-11'),
(6, 2, '2015-03-01');

Maybe it would be better to have one hierarchical relation table that references via ID to another table that stores the actual data... so that I have only two tables involved and would be more flexible if new elements are added...

Let's start by declaring the keys:

CREATE TABLE IF NOT EXISTS `biopsy` (
  `id` int(11) unsigned NOT NULL primary key,
  `creation_date` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

INSERT INTO `biopsy` (`id`, `creation_date`) VALUES
(226, '2015-03-08'),
(227, '2015-03-08');

CREATE TABLE IF NOT EXISTS `biopsy_p0` (
`id` int(11) unsigned NOT NULL primary key,
`biopsy_id` int(11) unsigned NOT NULL,
`introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

alter table biopsy_p0 add constraint fk_biopsy
    foreign key (biopsy_id)
    references biopsy (id)
        on update cascade
        on delete cascade;

INSERT INTO `biopsy_p0` (`id`, `biopsy_id`, `introduced`) VALUES
(1, 226, '2014-12-31'),
(2, 226, '2014-12-31'),
(3, 226, '2014-12-31'),
(4, 227, '2015-03-14');

-- violates the f.k. introduced
-- (5, 255, '2015-03-10'),
-- (6, 255, '2015-03-12');

CREATE TABLE IF NOT EXISTS `biopsy_p1` (
  `id` int(11) unsigned NOT NULL primary key,
  `biopsy_p0_id` int(11) unsigned NOT NULL,
  `introduced` date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

alter table biopsy_p1 add constraint fk_biopsy_p0
    foreign key (biopsy_p0_id)
    references biopsy_p0 (id)
        on update cascade
        on delete cascade;

INSERT INTO `biopsy_p1` (`id`, `biopsy_p0_id`, `introduced`)     
VALUES
(1, 1, '2015-03-18'),
(2, 2, '2015-03-31'),
(3, 1, '2015-03-17'),
(4, 1, '2015-03-18'),
(5, 3, '2015-03-11'),
(6, 2, '2015-03-01');

I would suggest that you name thing for what they are, i.e. don't name id columns id and change there name elsewhere in the model. Example:

CREATE TABLE IF NOT EXISTS biopsy (
  biopsy_id int unsigned NOT NULL primary key,
  creation_date date NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=228 DEFAULT CHARSET=latin1;

but I'll leave that aside. Now that we know that the data is consistent:

select x.id as biopsy_id, x.creation_date
     , y.id as biopsy_p0_id, y.introduced as biopsy_p0_introduction
     , z.id as biopsy_p1_id, z.introduced as biopsy_p1_introduction 
from biopsy as x 
left join biopsy_p0 as y 
    on y.biopsy_id = x.id 
left join biopsy_p1 as z 
    on z.biopsy_p0_id = y.id 
order by x.id, y.id, z.id;

+-----------+---------------+--------------+------------------------+--------------+------------------------+
| biopsy_id | creation_date | biopsy_p0_id | biopsy_p0_introduction | biopsy_p1_id | biopsy_p1_introduction |
+-----------+---------------+--------------+------------------------+--------------+------------------------+
|       226 | 2015-03-08    |            1 | 2014-12-31             |            1 | 2015-03-18             |
|       226 | 2015-03-08    |            1 | 2014-12-31             |            3 | 2015-03-17             |
|       226 | 2015-03-08    |            1 | 2014-12-31             |            4 | 2015-03-18             |
|       226 | 2015-03-08    |            2 | 2014-12-31             |            2 | 2015-03-31             |
|       226 | 2015-03-08    |            2 | 2014-12-31             |            6 | 2015-03-01             |
|       226 | 2015-03-08    |            3 | 2014-12-31             |            5 | 2015-03-11             |
|       227 | 2015-03-08    |            4 | 2015-03-14             |         NULL | NULL                   |
+-----------+---------------+--------------+------------------------+--------------+------------------------+
7 rows in set (0.00 sec)

What remains is purely presentation and that is better done in php.

For your general question whether it is better to keep the structural info in one table I would say that if you have a small number of fixed levels your solution is fine.

For a large number of levels or if the number is unknown you need some kind of recursive structure (note that you will also need means to ask that kind of questions, most DBMS:s have Recursive Common Table expressions these days but MySQL does not. You can solve certain things with variables, but it gets messy rather soon). Troels Arvin has a collection of links at:

http://troels.arvin.dk/db/rdbms/links/#hierarchical

that you might find useful.