PHP MySQL Loop on two-level data

Solution:

Add flow control to your code

<?php 
$org = "1";

$row1 = mysqli_prepare($mysqli,"SELECT * FROM menu WHERE org = ? ORDER BY mid ASC"); 
$row1->bind_param("s", $org);
mysqli_stmt_execute($row1); 
$row2 = mysqli_stmt_get_result($row1);
$controlfirst = 0;
while(  $row = mysqli_fetch_array($row2)) {
    if ( !isset($row["parent"])) { 
        if ( $controlfirst == 1) {  ?>          
        </div> 
    <?php } ?>
        <div class="category"><?php echo $row["item"]; /* GROUPED*/ 
     $controlfirst = 1;
    }
    else { 
            ?>
            
            <div class="item"><?php echo $row["item"];?> </div>/* INDIVIDUAL ITEM*/
    <?php  }
 }      
if ( $controlfirst == 1) {  ?>      


    </div> 
<?php } ?>
Copy code

See example

Solution:

This should do it;

SELECT          T.*, 
                IF(T.parent IS NULL, T.mid, T.parent) AS ParentGroup
FROM            mytable as T
WHERE           T.org = 1 # Comment out here to get for all org
ORDER BY        T.org ASC, ParentGroup ASC, T.parent IS NULL DESC, T.mid ASC
Copy code

Note: Please read the SQL injection policies carefully before using SQL statements with your code.

I hope you already know how to fetch data with PHP from MySQL; once all records are fetched, you can loop through like below;

... Here you already fetched all the records from database into $Recordset array

foreach($Recordset as $Data){ // Loop through the record set
    if($Data["parent"]){
        print "<li>{$Data["item"]}</li>"; // Item
    }
    else{
        print "<div class=\"Category\"><h1>{$Data["item"]}</h1><ul class=\"Item\">"; // Category
    }
}

print "</ul></div>";
Copy code