nesting in a php while loop using database values

Question

I have a menu in a database (phpmyadmin) which I am feeding into my header with a while loop. But one of the menu items has a dropdown. How can I alter my while loop below so that it integrates the submenu? In other words, Home, How It Works, Blog, About and Contact remain as links, while Services remains a dropdown that shows a separate while loop with pages Service1, Service2, Service3...Service nth?

Here is what I got working so far

My connection up top

<?php
require_once('db/db_database.php');

// These are menu items Home(id=1), Services(id=2), How It Works(id=3), Blog(id=4), About(id=5), & Contact(id=6)
$sql = " SELECT * FROM menu WHERE id IN (1, 2, 3, 4, 5, 6) ";
$result = $conn->query($sql);


// These are submenu items Svs1(id=10), Svs2(id=20), Svs3(id=30)
$sql2 = " SELECT * FROM menu WHERE id IN (10, 20, 30) ";
$result2 = $conn->query($sql2);
?>

My main menu loop which works fine without submenus

<?php
    if ($result->num_rows > 0) {
        // output data of each row
        while($row = $result->fetch_assoc()) {
            echo '<li class="nav-item dropdown active">';
            echo '<a href="' . $row["link"] . '" class="nav-link dropdown-toggle animated fadeIn animation-delay-7">' . $row["pagename"] . '</a>';
            echo '</li>';
        }
    } else {
        echo "0 results";
    }
?>

and my submenu structure that I would like nested in the loop above

<li class="nav-item dropdown">
    <a href="#" class="nav-link dropdown-toggle animated fadeIn animation-delay-7" data-toggle="dropdown" data-hover="dropdown" role="button" aria-haspopup="true" aria-expanded="false" data-name="services">
        Services
        <i class="zmdi zmdi-chevron-down"></i>
    </a>
    <ul class="dropdown-menu">
        <li>
            <a class="dropdown-item" href="">
                Service 1
            </a>
        </li>
        <li>
            <a class="dropdown-item" href="">
                Service 2
            </a>
        </li>
        <li>
            <a class="dropdown-item" href="">
                Service 3
            </a>
        </li>                                   
    </ul>
</li>

**Please notice the entire class group in the li is different than a regular link, hence why I am even more stumped. Also the Services dropdown comes second in the order in which it displays. So I can't just add it the end separately, hene why I am assuming that it should be nested to break the loop and restart the loop after it for the rest of the menu items.

p.s. I could run 3 queries (1 for the home link only, another for the services dropdown, and a third one for the rest of the menu items) but I know there has to be a better way.

Here is an idea of the desired look

Thanks in advance guys! Not that it would help because you need a connection, but here is a codepen with my code for this menu if you want to play with it

CODEPEN

EDIT

To make it a bit easier here is what I have now that works fine but too bloated. (3 queries)

<?php
    if ($resulthome->num_rows > 0) {
        // output data of each row
        while($row = $resulthome->fetch_assoc()) {
            echo '<li class="nav-item dropdown active">';
            echo '<a href="' . $row["link"] . '" class="nav-link dropdown-toggle animated fadeIn animation-delay-7">' . $row["pagename"] . '</a>';
            echo '</li>';
        }
    } else {
        echo "0 results";
    }
?>
<li class="nav-item dropdown active">
    <a href="#" class="nav-link dropdown-toggle animated fadeIn animation-delay-7" data-toggle="dropdown" data-hover="dropdown" role="button" aria-haspopup="true" aria-expanded="false" data-name="services">
        Services
        <i class="zmdi zmdi-chevron-down"></i>
    </a>
    <ul class="dropdown-menu">
        <?php
            if ($result2->num_rows > 0) {
                // output data of each row
                while($row = $result2->fetch_assoc()) {
                    echo '<li>';
                    echo '<a href="' . $row["link"] . '" class="dropdown-item">' .  $row["pagename"] . '</a>';
                    echo '</li>';
                }
            } else {
                echo "0 results";
            }
        ?>
    </ul>
</li>
<?php
    if ($result1->num_rows > 0) {
        // output data of each row
        while($row = $result1->fetch_assoc()) {
            echo '<li class="nav-item dropdown active">';
            echo '<a href="' . $row["link"] . '" class="nav-link dropdown-toggle animated fadeIn animation-delay-7">' . $row["pagename"] . '</a>';
            echo '</li>';
        }
    } else {
        echo "0 results";
    }
?>

Ravinder Reddy · Accepted Answer · 2017-12-01 21:06:24Z

1

You can use sub menu loop inside the main menu loop with condition.

 if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        if($row['id'] == 2){ // applies only for services
            echo '<li class="nav-item dropdown active">';
            echo '<a href="#" class="nav-link dropdown-toggle animated fadeIn animation-delay-7" data-toggle="dropdown" data-hover="dropdown" role="button" aria-haspopup="true" aria-expanded="false" data-name="services">';
           echo $row["pagename"];
           echo '<i class="zmdi zmdi-chevron-down"></i>';
           echo '</a>';
           // create a new list
            echo '<ul class="dropdown-menu">';
             // loop second/submenu query results
             while($sub_row = $result2->fetch_assoc()) { 
                echo '<li>';
                echo '<a href="' . $sub_row["link"] . '" class="dropdown-item">' . $sub_row["pagename"] . '</a>';

                echo '</li>';  
             }
             echo "</ul>";
             echo '</li>';   
        }else{ // for all other menu links
            echo '<li class="nav-item dropdown active">';
            echo '<a href="' . $row["link"] . '" class="nav-link dropdown-toggle animated fadeIn animation-delay-7">' . $row["pagename"] . '</a>';
            echo '</li>';
        }

    }
} else {
    echo "0 results";
}

edited Dec 1, 2017 at 21:06

answered Dec 1, 2017 at 20:44

Ravinder Reddy

3,8791 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

LOTUSMS Over a year ago

That didn't populate my submenu, and it created the services menu as another link menu. Notice the class group and attributes associated with that li that makes it a dropdown toggle. as well as an icon

Ravinder Reddy Over a year ago

This code will create a new list under services li list. please view the page source and don't forget to include css

LOTUSMS Over a year ago

yes, but it doesn't open because of the missing attributes in the li that toggles it to open. See my EDIT, I made 3 different queries that give me the desired result but it is too bloated. I'd like to simplify it. You're close, except the li for services is different than the other li's in the menu

Ravinder Reddy Over a year ago

Edited my answer

LOTUSMS Over a year ago

Bingo! Awesome! Now I know what to do with all the other stuff that requires a similar logic. I appreciate it man.

miknik · Accepted Answer · 2017-12-01 21:38:28Z

0

If you want to reduce your database queries you can use UNION to join them all together, then add a virtual column so you can identify which rows belong to which query.

Something like this:

SELECT *, 'first' AS query FROM menu WHERE id IN (1, 2, 3, 4, 5, 6) 
UNION
SELECT *, 'second' AS query FROM menu WHERE id IN (10, 20, 30)

This will return all your results in one query with an extra column called query which you then use in your foreach logic.

Rather than having a nested foreach loop I would probably order the query to return the submenu results first. Construct the HTML for that into a variable, then construct the main menu HTML and just drop in the variable for the submenu in place of your current nested loop.

answered Dec 1, 2017 at 21:38

miknik

5,9671 gold badge14 silver badges28 bronze badges

1 Comment

LOTUSMS Over a year ago

Unfortunately I can only pick one answer but I'm interested in your approach. Did the sake of learning and future visitors, care to provide a working code? If it works I'd at least up vote it

Collectives™ on Stack Overflow

nesting in a php while loop using database values

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related