#StackBounty: #php #css #mysql #bootstrap-4 #navbar Build boostrap4 navmenu from multiple mysql tables

Bounty: 50

I want to make a bootstrap 4 nav-menu, I have the following SQL query and I have some codes below it but I can’t get my head around how to do this!

These are the tables

TABLE menu
--------------------------------------
| id | title       | url             |
| 1  | Home        | index.php       |
| 2  | Menu        | #               |
| 3  | Contact     | #               |
| 2  | Winkelwagen | winkelwagen.php |
--------------------------------------

TABLE categories
-------------------------------------
| id | title_cat | url | cparent_id |
| 1  | Auto's    | #   | 2          |
| 2  | Drank     | #   | 2          |
-------------------------------------

TABLE products
-------------------------------------
| id | product  | url  | pparent_id |
| 1  | Ferrari  | #    | 1          |
| 2  | Heineken | #    | 2          |
-------------------------------------

Here is the query:

$query =    "SELECT
            X.level,
            X.id,
            X.name,
            X.url,
            X.parent_id
        FROM
            (
            SELECT
                1 AS LEVEL,
                id AS id,
                title AS NAME,
                url AS url,
                0 AS parent_id,
                id AS id_1,
                -1 AS id_2,
                -1 AS id_3
            FROM
                menu
            WHERE
                1
            UNION
        SELECT
            2 AS LEVEL,
            id AS id,
            title_cat AS NAME,
            url AS url,
            cparent_id AS parent_id,
            cparent_id AS id_1,
            id AS id_2,
            -1 AS id_3
        FROM
            categories
        WHERE
            1
        UNION
        SELECT
            3 AS LEVEL,
            products.id AS id,
            products.product AS NAME,
            products.url AS url,
            products.pparent_id AS parent_id,
            categories.cparent_id AS id_1,
            categories.id AS id_2,
            products.id AS id_3
        FROM
            products
        LEFT JOIN categories ON products.pparent_id = categories.id
        WHERE
            1
        ) X
        WHERE
            1
        ORDER BY
            id_1,
            id_2,
            id_3";

Which gives the following table with levels (and I added the parent_id too, but with the parent_id buildTree($array) goes into a loop):

level   id  name        url             parent_id   
1       1   Home        index.php       0   
1       2   Menu        #               0   
2       1   Auto's      #               2   
3       1   Ferrari     #               1   
2       2   Drank       #               2   
3       2   Heineken    #               2   
1       3   Contact     contact.php     0   
1       4   Winkelwagen winkelwagen.php 0   

I want the nav-menu to look like this:

        <li class="nav-item">
            <a class="nav-link" href="index.php">Home</a>
        </li>
        <li class="nav-item dropdown">
            <a class="nav-link dropdown-toggle" href="#" id="navbarDropdown" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false">Menu</a>
            

I have the following codes, first we make a array from the query fetched which you already saw above:

$sql = $pdo->prepare($query);

function menu_builder($sql) {
    if ($sql->execute()) {
        while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
            $array[] = $row;
        }
        buildTree($array); // or menu_builder($sql);
    }
}

The next code doesn’t work because it goes into a infinite loop (and if it works I still need to make the html right :):

function buildTree($array, $parent_id = 0, $parents = array()) {
    if($parent_id == 0) {
        foreach ($array as $element) {
            if (($element['parent_id'] != 0) && !in_array($element['parent_id'], $parents)) {
                $parents[] = $element['parent_id'];
            }
        }
    }
    $menu_html = '';
    foreach($array as $element) {
        if($element['parent_id'] == $parent_id) {
            if(in_array($element['id'], $parents)) {
                $menu_html .= '<li class="dropdown">';
                $menu_html .= '<a href="'.$element['url'].'" class="dropdown-toggle" data-toggle="dropdown" role="button" aria-expanded="false">'.$element['name'].' <span class="caret"></span></a>';
            }
            else {
                $menu_html .= '<li>';
                $menu_html .= '<a href="' . $element['url'] . '">' . $element['name'] . '</a>';
            }
            if(in_array($element['id'], $parents)) {
                $menu_html .= '<ul class="dropdown-menu" role="menu">';
                $menu_html .= buildTree($array, $element['id'], $parents);
                $menu_html .= '</ul>';
            }
            $menu_html .= '</li>';
        }
    }
    return $menu_html;
}

And this one makes a normal <ul>/<li> menu which I don’t know how to get it working for me with bootstrap:

function menu_builder($sql) {
    $level = 0;
    if ($sql->execute()) {
        while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
            while($level < $row['level']) {
                echo "<ul>" . PHP_EOL;
                $level++;
            }
            while($level > $row['level']) {
                echo "</ul>" . PHP_EOL;
                $level--;
            }
            echo "    <li>#" . $row['id'] . "->" . $row['name'] . "</li>" . PHP_EOL;
        }
    }
    while($level-- > 0) {
       echo "</ul>" . PHP_EOL;
    }
}

If you need more information please ask me, I tried to make the question as clear as possible with the codes I’m trying and the table I’m using.

jQuery:

$('.dropdown-menu a.dropdown-toggle').on('click', function(e) {
    if (!$(this).next().hasClass('show')) {
        $(this).parents('.dropdown-menu').first().find('.show').removeClass("show");
    }
    var $subMenu = $(this).next(".dropdown-menu");
    $subMenu.toggleClass('show');
    $(this).parents('li.nav-item.dropdown.show').on('hidden.bs.dropdown', function(e) {
        $('.dropdown-submenu .show').removeClass("show");
    });
    return false;
});

CSS:

.dropdown-submenu {
  position: relative;
}

.dropdown-submenu a::after {
  transform: rotate(-90deg);
  position: absolute;
  right: 6px;
  top: .8em;
}

.dropdown-submenu .dropdown-menu {
  top: 0;
  left: 100%;
  margin-left: .1rem;
  margin-right: .1rem;
}


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.