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