Skip to content
Advertisement

PHP/SQL Dynamic menu depending on different tables

I’m trying to manage a dynamic menu based on results from tables in my database.

Code below is so far i have come.. But i can’t get it to display as i want to.

i have 3 tables in my database looking like this.

ws_categories

  • id
  • maincat (name of main-category)

ws_subcategories

  • id
  • subcat (Name of sub-category)
  • parentcat (id of main-category)

ws_subsubs

  • id
  • subsub (Name of 2nd-sub-category)
  • parentsub (id of sub-category)

What i want to achieve? Having a simple vertical menu, that outputs main categories, and onclick, submenue alternatives related to that main category will show, if a sub category has a 3rd submenu/submenues, they will show under..

Code below is so far i have come.. But i don’t seem to understand why it output main category several times and not just once..

To be clear, i do not understand how i should use join to achieve this. I want to be able to echo all main categories once, and subcategories once, and if there is one or more 2nd sub categories i want them to echo too.. How do i achieve this with join? is it even possible or am i looking the wrong way?

Thanks in advance.

PHP

<?php


echo '<div class="dl_parent">';

$results = mysqli_query($link, "SELECT * FROM `ws_categories` INNER JOIN `ws_subcategories` ON `ws_categories`.`id` = `ws_subcategories`.`parentcat`;") or die (mysqli_error($link));
    while($row = mysqli_fetch_array($results)){
    echo '
<div class="dl_parent">
    <div class="dl_link">'.$row['maincat'].'</div>
    <div class="dl_sub_dd">
        <ul>
            <li>'.$row['subcat'].'</li>
        </ul>
    </div>
</div>
    ';
   }
?>

Javascript

    $(window).on('load',function(){
//CLICK-HANDLERS=============================
    $('.dl_link').click(function(){
        var submenu = $(this).parent().children('.dl_sub_dd');
        if (submenu.css('display') == 'none') {
            $('.dl_sub_dd').hide(); //first hide any previously showing submenu's
            submenu.show(); //then show the current submenu
        } else {
            submenu.hide(); //hide the current submenu again
        }
    });
});

CSS

/*LINK------------------------*/
.dl_link {
    cursor:pointer;
}

/*DROPMENU--------------------*/
.dl_sub_dd {
    display:none;
}

Advertisement

Answer

Your SQL request will give you for each main category as many rows as it has sub categories:

row 1: maincat1 | subcat1
row 2: maincat1 | subcat2
etc...

You could make a request to select all maincats, and for each maincat, make another request to select all its subcats. Something like this:

PHP

<?php


$results = mysqli_query($link, "SELECT * FROM `ws_categories`;") or die (mysqli_error($link));
    while($row = mysqli_fetch_array($results)){
    echo '
<div class="dl_parent">
    <div class="dl_link">'.$row['maincat'].'</div>
    <div class="dl_sub_dd">
        <ul>';
             $query = mysqli_query($link, "SELECT * FROM `ws_categories` INNER JOIN `ws_subcategories` ON `ws_categories`.`id` = `ws_subcategories`.`parentcat` WHERE `ws_categories`.`id` = " . $row['id'] . ";") or die (mysqli_error($link));
            while($row2 = mysqli_fetch_array($query)) {
                echo '<li>'.$row2['subcat'].'</li>';
            }
        echo '</ul>
    </div>
</div>
    ';
   }
?>
User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement