creating a dynamic menu using two mysql tables

Question

I have two mysql tables called "parents" and "childs"

in my parents table i have 4 columns (id,link,lable,have_childs)

in my childs table also i have 4 columns (id, c_link, c_lable, parent_id)

and i get the values using a query like this

"SELECT parents.*, childs.* FROM parents, childs WHERE parents.id = childs.p_id;"

then using a foreach loop i got this result

array(7) { ["id"]=> string(1) "1" ["link"]=> string(3) "veg" ["lable"]=> string(9) "Vegitable" ["childs"]=> string(1) "1" ["c_link"]=> string(6) "carrot" ["c_lable"]=> string(6) "carrot" ["p_id"]=> string(1) "1" } 
array(7) { ["id"]=> string(1) "2" ["link"]=> string(3) "Fru" ["lable"]=> string(6) "Fruits" ["childs"]=> string(1) "1" ["c_link"]=> string(6) "grapes" ["c_lable"]=> string(6) "grapes" ["p_id"]=> string(1) "2" } 
array(7) { ["id"]=> string(1) "3" ["link"]=> string(3) "veg" ["lable"]=> string(9) "Vegitable" ["childs"]=> string(1) "1" ["c_link"]=> string(5) "beeat" ["c_lable"]=> string(5) "beeat" ["p_id"]=> string(1) "1" }

then i did this

<?php
foreach($result as $myresult){ ?>
    <ul>
    <li><a href="<?php echo $myresult['link']; ?>"><?php echo $myresult['lable']; ?></a>
        <?php 
            if($myresult['childs'] == 1){
                echo '<div><ul>';
                echo '<li><a href="'.$myresult['c_link'].'">'.$myresult['c_lable'].'</a></li>';
                echo '</div></ul>';
            }
        ?>

<?php   
}
?>

then i got this result

.Vegitable
   carrot
.Fruits
   grapes
.Vegitable
   beet

but this is not the result i looking for i need both carrot and beet items go under vegetable.

is there any way to do this?

All you're currently doing is iterating over the result set, the items in your foreach loop and spitting out the relevant elements within that row. A possible solution is to look over your result set and build the proper menu container, in another variable, based on the information within that result set. — aztechy
– aztechy, Commented Aug 25, 2013 at 4:44

Sajan Parikh · Accepted Answer · 2013-08-25 05:07:08Z

I've create the following tables.

parents

+----+------+-----------+
| id | link | label     |
+----+------+-----------+
|  1 | veg  | Vegetable |
|  2 | fru  | Fruit     |
+----+------+-----------+

childs

+----+-----------+--------+---------+
| id | parent_id | link   | label   |
+----+-----------+--------+---------+
|  1 |         1 | beets  | Beets   |
|  2 |         1 | carrot | carrots |
|  3 |         2 | apple  | Apples  |
+----+-----------+--------+---------+

One way to do this is to select all the parents, and loop through each one getting their childs. Here is some pseudocode. I may have messed up the ul/li nesting, but you should get the idea.

// Start by getting all the parents.
$parents = // SELECT * FROM PARENTS;

// Loop through all the parents.
@foreach ($parents as $parent) {
    echo "<ul>";
    echo "<li>" . $parent['label'];

    // Get all the childs of the current parent.
    $childs = // SELECT * FROM CHILDS WHERE parent_id = $parent['id'];

    // Loop through all the childs of the current parent.
    @if ($childs has results) {
        echo "<ul">;

        @foreach ($childs as $child) {
            echo "<li>" . $child['label'] . "</li>";
        @endforeach

        echo "</ul>";
        echo "</li>";
    @else
        echo "</li>";
    @endif

    echo "</ul">;

@endforeach;

aztechy · Accepted Answer · 2013-08-25 05:11:31Z

Here's an example of what I was talking about:

// Your results set emulated.
$dbResults = array(
    array(
        "id" => 1,
        "link" => "veg",
        "label" => "Vegitable",
        "childs" => 1,
        "c_link" => "carrot",
        "c_label" => "carrot",
        "p_id" => 1
    ),
    array(
        "id" => 2,
        "link" => "fru",
        "label" => "Fruit",
        "childs" => 1,
        "c_link" => "Apple",
        "c_label" => "Apple",
        "p_id" => 2
    ),
    array(
        "id" => 3,
        "link" => "veg",
        "label" => "Vegitable",
        "childs" => 1,
        "c_link" => "beet",
        "c_label" => "beet",
        "p_id" => 1
    )
     );

// Your actual menu, built from the above result set.
$menu = array();

// A simple illustration of building up your menu
foreach ($dbResults as $menuItem) {
    // For clarity
    $menuIndex = $menuItem['label'];
    $menu[$menuIndex][] = $menuItem['c_label'];
}

$output = '<ul>';
foreach ($menu as $parentLabel => $children) {
    $output .= "<li>{$parentLabel}</li>";

    if (!empty($children)) {
        $output .= "<ul>";
        foreach ($children as $childLabel) {
            $output .= "<li>{$childLabel}</li>";     
        }
        $output .= "</ul>";
    }   
}
$output .= "</ul>";

print($output);

Yields:

Vegitable

carrot
beet

Fruit

Apple

chiliNUT · Accepted Answer · 2013-08-25 05:14:50Z

0

if your result is sorted by lable, then

$lastLable="";
foreach($result as $myresult){ 
$lable=$myresult['lable']
?>
<ul>
    <li>
    <?php
    if ($lable!=$lastLable) 
    {
    ?>
    <a href="<?php echo $myresult['link']; ?>"><?php echo $myresult['lable']; ?></a>
        <?php 
        }
        if($myresult['childs'] == 1){
        echo '<div><ul>';
        echo '<li><a href="'.$myresult['c_link'].'">'.$myresult['c_lable'].'</a></li>';
        echo '</div></ul>';
        echo "</li>";
        $lastLable=$lable;
        }
        ?>

        <?php   
        }
        ?>

answered Aug 25, 2013 at 5:14

chiliNUT

19.4k14 gold badges72 silver badges116 bronze badges

Collectives™ on Stack Overflow

creating a dynamic menu using two mysql tables

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related