Jump to content
Larry Ullman's Book Forums
Jacques

Populating a mega dropdown menu across columns with database results

Recommended Posts

Hi all. I need some assistance with dynamically populating a mega drop-down menu across 6 columns (Bootstrap 4) with the languages retrieved from a database query (Forum project - Chapter 17). I have searched the internet without any success. Any help will be much appreciated. My code is included below.

Regards

// Select a language:
echo '<li class="nav-item nav-item-icon hidden-md-down dropdown megamenu">
    <a class="nav-link dropdown-toggle" href="#" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">' . $words['lang'] . ' <span><i class="fa fa-globe"></i></span></a>
    <div class="dropdown-menu">
        <div class="mega-dropdown-menu row row-no-padding">
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li>';
                        // RetrRetrieve all the languages and add to the pull-down menu:
                        $q = "SELECT id, lang FROM languages WHERE status='Active' ORDER BY lang ASC";
                        $r = mysqli_query($dbc, $q);
                        if (mysqli_num_rows($r) > 0) {
                            while ($menu_row = mysqli_fetch_array($r, MYSQLI_NUM)) {
                                echo '<a class="dropdown-item" href="?lid=' . $menu_row[0] . '">' . $menu_row[1] . '</a>';
                            }
                        }
                        mysqli_free_result($r);
                    echo '</li>
                </ul>
            </div>
        </div>
    </div>
</li>';

 

Share this post


Link to post
Share on other sites

How is the code you're currently using not working? What debugging steps have you taken and what were the results?

Share this post


Link to post
Share on other sites

Hi Larry,

Thank you for your prompt response.  The code works perfectly except that it only populates the first column instead of populating across 6 columns.

Regards.

Share this post


Link to post
Share on other sites

Ah, thanks for explaining. Unfortunately I'm not a pro-debugger when it comes to Bootstrap (or CSS) so it's not obvious to me what the cause is. In such situations I'd normally find a working example some place, copy that in, and then replace the key bit with my PHP code. 

Share this post


Link to post
Share on other sites

The code I included above has been updated to the code below so that the languages now populate the mega drop-down menu perfectly - alphabetically and horizontally - across six columns.  However, I want the languages to populate the menu alphabetically but vertically across the six columns.  Is it possible to manipulate the MySQL result accordingly?  I couldn't find a working example anywhere.

<!-- Navbar links -->
<?php // Select a language:
echo '<li class="nav-item nav-item-icon hidden-md-down dropdown megamenu">
    <a class="nav-link dropdown-toggle" href="#" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">' . $words['lang'] . ' <span><i class="fa fa-globe"></i></span></a>
    <div class="dropdown-menu">
        <div class="mega-dropdown-menu row row-no-padding">';  
            // Retrieve all the languages and add to the pull-down menu:
            $q = "SELECT id, lang FROM languages WHERE status='Active' ORDER BY lang ASC";
            $r = mysqli_query($dbc, $q);
            if (mysqli_num_rows($r) > 0) {
                while ($menu_row = mysqli_fetch_array($r, MYSQLI_NUM)) {

                echo '<div class="col-md-4 col-lg-2 ml-lg-auto">
                    <ul class="megadropdown-links">
                        <li><a class="dropdown-item" href="?lid=' . $menu_row[0] . '">' . $menu_row[1] . '</a>
                        </li>
                    </ul>
                </div>';
                }
            }
            mysqli_free_result($r);
        echo '</div>
    </div>
</li>';

 

Edited by Jacques

Share this post


Link to post
Share on other sites

Glad you got this working and thanks for letting us know. If I understand you correctly, what you'll want to do is fetch the results from MySQL in the right order and then use PHP logic to output them in the desired manner. You'd want to fetch all the results into one array and then pull from it accordingly. For example, you pull in all the rows and then output, maybe, row 0, 4, 1, 5, 2, 6, 3, 7.

Share this post


Link to post
Share on other sites

Yes, the last code snippet above displays the language results alphabetically and horizontally across the 6 columns in the drop down menu as follow:

Deutsch            English (UK)          English (US)          Español          Français          Italiano

Nederlands      Português              Pусский                Ελληνικά       中文                  日本の

 

But I want the language results to display alphabetically and vertically across the 6 columns as follow:

Deutsch             English (US)          Français          Nederlands          Pусский           中文

English (UK)      Español                 Italiano            Português            Ελληνικά          日本の

The result should also provide for additional languages to be added to the database at a later stage.

Thank you and regards.

Edited by Jacques

Share this post


Link to post
Share on other sites

Right, so as I said before, you'll want to fetch the records in order and then pull them all into an array and then use PHP to display them accordingly. So with 6 columns and under 12 items, you'd display $row[0],  $row[2], $row[4], $row[6], $row[8], $row[10] in the first HTML row and then $row[1], $row[3], etc. in the second HTML row. 

To dynamically support additional languages in the future, you'd just need to change the logic accordingly. It'll be additionally complicated if the number of languages is not divisible by 6 (the number of columns). 

If you write this up and start playing with it, it should become pretty obvious pretty fast how to tweak and perfect it.

Share this post


Link to post
Share on other sites

Thank you for your response Larry.  I changed the code to the snippet below but it gives me an "Undefined offset" error for values 2 onward.  I tried to find a solution in the book as well as online without any success.

Regards.

...
<div class="dropdown-menu">
    <div class="mega-dropdown-menu row row-no-padding">';  
        // Retrieve all the languages and add to the pull-down menu:
        $q = "SELECT id, lang FROM languages WHERE status='Active' ORDER BY lang ASC";
        $r = mysqli_query($dbc, $q);
        if (mysqli_num_rows($r) > 0) {
            $menu_row = mysqli_fetch_array($r, MYSQLI_NUM);

            echo '<div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[0] . '">' . $menu_row[1] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[2] . '">' . $menu_row[3] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[4] . '">' . $menu_row[5] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[6] . '">' . $menu_row[7] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[8] . '">' . $menu_row[9] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[10] . '">' . $menu_row[11] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[12] . '">' . $menu_row[13] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[14] . '">' . $menu_row[15] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[16] . '">' . $menu_row[17] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[18] . '">' . $menu_row[19] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[20] . '">' . $menu_row[21] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[22] . '">' . $menu_row[23] . '</a>
                    </li>
                </ul>
            </div>';   
        }
        mysqli_free_result($r);
    echo '</div>
</div>';
...

 

Edited by Jacques

Share this post


Link to post
Share on other sites

You only fetched a single row, which is why you got that error. As I said previously, you need to fetch all the rows from MySQL first. 

Share this post


Link to post
Share on other sites

Hi Larry,

I updated the code to the snippet below but it still gives me an "Undefined offset" error for values 2 onward.  I had a look inside a number of your books that I have but can't find my mistake.  Any further comment to guide me will be appreciated.

Thank you and regards.

...
<div class="mega-dropdown-menu row row-no-padding">';  
        // Retrieve all the languages and add to the pull-down menu:
        $q = "SELECT id, lang FROM languages WHERE status='Active' ORDER BY lang ASC";
        $r = mysqli_query($dbc, $q);
        if (mysqli_num_rows($r) > 0) {
            while ($menu_row = mysqli_fetch_array($r, MYSQLI_NUM)) {

            echo '<div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[0] . '">' . $menu_row[1] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[2] . '">' . $menu_row[3] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[4] . '">' . $menu_row[5] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[6] . '">' . $menu_row[7] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[8] . '">' . $menu_row[9] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[10] . '">' . $menu_row[11] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[12] . '">' . $menu_row[13] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[14] . '">' . $menu_row[15] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[16] . '">' . $menu_row[17] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[18] . '">' . $menu_row[19] . '</a>
                    </li>
                </ul>
            </div>
            <div class="col-md-4 col-lg-2 ml-lg-auto">
                <ul class="megadropdown-links">
                    <li><a class="dropdown-item" href="?lid=' . $menu_row[20] . '">' . $menu_row[21] . '</a>
                    </li>
                     <li><a class="dropdown-item" href="?lid=' . $menu_row[22] . '">' . $menu_row[23] . '</a>
                    </li>
                </ul>
            </div>';
            }
        }
        mysqli_free_result($r);
    echo '</div>
</div>
...

 

Share this post


Link to post
Share on other sites

When you use mysqli_fetch_array(), you're fetching only a single record at a time. Therefore, in your loop, the $menu_row variable represents a single record. It does not represent every record. The elements within $menu_row are columns of the single record. The code you have is attempting to create ALL the navigation items once for each record in the database. This isn't what you want to do for several reasons.

What you need to do here is fetch all the MySQL records first (using, say, mysql_fetch_all()). Then you'll have a multidimensional array. Then you create all the navigation items just one time, referring to the proper array element in each spot. 

As a debugging and learning trick, I'd recommend you get in the habit of using print_r() on your variables to see what values they have at certain points. That might help you understand better what's going on. 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...