PHP: How do I run foreach loop on every foreach result?

Question

Im pulling content from my database and running through it with a foreach loop. Every item I pull has a column with an ID, and a column with PARRENT ID (among other columns). First, im pulling all the rows where my PARRENT ID column equals 0, and output them via my foreach loop, but for every result I get, I want to run a new query to check for rows where PARRENT ID is equal to its ID. For every result I get here, I want to do the same again and again, for as many results it may find (Potentially this could go on forever).

Let me try to visualize this

ID = 1, PARRENT ID = 0
ID = 2, PARRENT ID = 0
ID = 3, PARRENT ID = 0
   ID = 4, PARRENT ID = 3
   ID = 5, PARRENT ID = 3
      ID = 6, PARRENT ID = 5
    ID = 7, PARRENT ID = 3
    ID = 8, PARRENT ID = 3
ID = 9, PARRENT ID = 0
ID = 10, PARRENT ID = 0
   ID = 11, PARRENT ID = 10
      ID = 12, PARRENT ID = 11
         ID = 13, PARRENT ID = 12
            ID = 14, PARRENT ID 13

How do i go about this?

Answer 1

You could make a function which just returns all rows with a specific parent id. And then make a recursive function which prints out a result set and all children of every element. This is some quick pseudo code:

function getRowsForParentId($id){
    //build query with "WHERE parent = id", fetch all rows and return them
    ...
    return $rows;
}


function printResults($results, $level = 1){
    //loop over every result
    foreach($results as $row){
        //print an indented representation of that row
        echo sprintf("%sid: %s, parent: %s", str_repeat(" ", $level), $row->id, $row->parentId);
        //print out all childs one level deeper
        printResults(getRowsForParentId($row->id), $level++);
    }
}

$allRows = getRowsForParentId(0);//fetch all rows from db where parent = 0
printResults($allRows);

This should do what you want.

Also look up on storing hierarchical data in mysql, as this is what you are doing in here.

What are the options for storing hierarchical data in a relational database?

Answer 2

I figured out a solution myself, which works like I want it to, but I honestly don't know if is bad practice, but I'm very new to programming.

I made a function, in which I can pass in a value. Within this function, I select all rows with a PARRENT ID equal to my input value. Then I have a foreach loop, in which I get the ID of earch row. Within this foreach loop, I pass the function, and pass the ID as the value.

function get_parrents($input) {  

$sql = "SELECT * FROM table WHERE parrent_id = '" . $input . "'";
$result = mysqli_query($conn, $sql);
foreach($result as $item) {

    $new_item = $item['id'];

    get_parrents($new_item);
};

};

$a = 0;

catch_replies($a);