PHP - SQL表中的嵌套数据树

Question

I have an SQL table containing a list of parts with sub parts that go into them. I need to come up with a way of outputting the data into a tree to show what goes into what else.

The table is simply as below:

Part_Number | Sub_Part
1             1a
1             1b
1             1c
1a            1a0
1a            1a1
1b            1b0
2

So from that I would need to output something like this:

-1
--1a
---1a0
---1a1
--1b
---1b0
--1c
-2

I cannot think of a solution for this, I have tried using loops within loops to pull the information but the best I can get is a the first sub part of each layer: 1, 1a, 1a0.

<?php $lookup = ['1']; ?>
    <?php 
        x: 
        $i = $i . '-';
    ?>

    <?php foreach($lookup as $look) : ?>
        <p><?php echo $i . $look; ?></p>
        <?php $lookup = []; ?>

        <?php $query = mysqli_query($conn, "SELECT Sub_Part FROM `Bill_Of_Materials` WHERE Part_Number = '{$look}'"); ?>
        <?php while($search = mysqli_fetch_array($query)) : ?>
            <?php array_push($lookup, $search[0]); ?>
        <?php endwhile; ?>

        <?php goto x; ?>
    <?php endforeach; ?>

I am thinking that the best way to do this might be to create objects for each top level part then have a recursive loop to create objects for each sub part within it, and each within that etc.

Objects are quite new to me however, so is this possible?

Answer 1

I have managed to work this out myself, by creating an object which then searches the SQL table for sub parts and creates another object per sub.

class part {
    function __construct($partNumber, $layer = '-') {
        echo $layer . $partNumber . '<br>';

        $conn = mysqli_connect('localhost', 'user', 'pass', 'database');

        if(!$conn) {
            die('Connection Failed: ' . mysqli_connect_error());
        }

        $subs = mysqli_query($conn, "SELECT Sub_Part FROM `Bill_Of_Materials` WHERE Part_Number = '{$partNumber}'");

        while($sub = mysqli_fetch_assoc($subs)) {
            $sub = new part($sub['Sub_Part'], $layer . '-');
        }
    }
}

$a = new part('123');