OscQ OscQ - 3 months ago 14
PHP Question

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;
}

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>
    ';
   }
?>