PHP MySQL Loop on two-level data

I am trying to extract two layer data from mysql database with a single mysql query and then display it grouped by top-layer with a php while loop.

<?php $org = "1";

$row1 = mysqli_prepare($db,"SELECT * FROM menu WHERE org = ? ORDER BY mid DESC"); 
$row1->bind_param("s", $org);
mysqli_stmt_execute($row1); $row2 = mysqli_stmt_get_result($row1);

while($row = mysqli_fetch_array($row2)){ ?>

<div class="category"><?php echo $row["item"]; /* GROUPED*/?>
<div class="item"><?php echo $row["item"]; /* INDIVIDUAL ITEM*/?></div>
</div>

<?php }; ?>
midparentorgitemprice
11Category 1
211Item 1$10
311Item 2$12
41Category 2
541Item 3$12
641Item 4$90

PHP MySQL Loop on two-level data

Obviously each item will appear only once but also the category will appear as many times as the underlying items. In the code however i need to group all items under the same parent category. Any ideas?

Desired Result

List
Category 1
– Item 1
– Item 2
Category 2
– Item 3
– Item 4

Or in terms of the HTML code output:

<div class="category">Parent Category 1<br>
<div class="item">Item 1</div>
<div class="item">Item 2</div>
</div>
<div class="category">Parent Category 3<br>
<div class="item">Item 3</div>
<div class="item">Item 4</div>
</div>

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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 } ?>

See example

Method 2

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

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>";


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x