PHP - Nested tree of data from SQL Table

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?

Can you post the response of $query, so that people can work on it, you can modify the data — BILAL MALIK, Feb 21 '19 at 09:13
Possible duplicate of [Connect By Prior Equivalent for MySQL](https://stackoverflow.com/questions/7631048/connect-by-prior-equivalent-for-mysql) — Eriks Klotins, Feb 21 '19 at 09:15
_Side note:_ When you're writing PHP-blocks, the code will be way easier to read if you open _once_ ` — M. Eriksson, Feb 21 '19 at 09:16
I have flagged this as a duplicate. Look into ways to create hierarchical queries with MySQL. — Eriks Klotins, Feb 21 '19 at 09:16
Using PHP business logic, you would create a table with `part_id int, part_number char, parent_part_id`. Top level parts have a parent NULL. If you have a predictable max. nesting level, you could do the select, grouping and sorting in SQL as well. — Pinke Helga, Feb 21 '19 at 09:35
"it is not duplicate it is something else" @EriksKlotins is right here this data is hierarchical tree more or less but you made it harder on yourself by allowing different datatypes in one string.. This datamodel is very questionable..there is not separation of anny kind between the types.. Don't understand me wrong here that iám suggest using comma separated values now.... — Raymond Nijland, Feb 21 '19 at 10:15
I have done it with query tested on MySQL maybe you will like it :D — Sayed Mohd Ali, Feb 21 '19 at 10:15

Sayed Mohd Ali · Answer 1 · 2019-02-21T10:12:22.270

0

You can use this query... to simply get the data in the way you want... WOW I have done this with query :D

   select t1.Part_Number as parent,t1.parts as g1 from table1 t2 right join (select Part_Number,group_concat(Sub_Part) as parts from table1 group by Part_Number) t1 on t1.Part_Number =  SUBSTRING(t2.Sub_Part, 1, CHAR_LENGTH(t2.Sub_Part)-1) group by t1.Part_Number

edited Feb 21 '19 at 10:12

answered Feb 21 '19 at 09:31

Sayed Mohd Ali

2,156
3
12
28

score 0 · Accepted Answer · answered Feb 21 '19 at 09:47

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');

PHP - Nested tree of data from SQL Table

2 Answers2