Categories and subcategories array and dynamic dropdown list

[z4z07]

Hey guys,

 

I'm struggling for a while to 2 things for my website.

 

1st - In my db i have 2 main categories - with 10 subcategories and another 10 subsubcategories.

From now i have a main menu wich i want to display dynamic the subcategories and subsubcategories like in the code i've provided below

<li><a href="marci.php"><i class="fa fa-building" aria-hidden="true" style="font-size: 14px;"></i> Marci</a>
                  <ul class="dropdown">
                    <li>
                    <a href="marci.php">Auto</a>
                      <ul>
                        <li><a href="">Masini</a></li>
                        <li><a href="">Piese auto</a></li>
                        <li><a href="">Accesorii auto</a></li>
                        <li><a href="">Acumultori auto</a></li>
                        <li><a href="">Anvelope Auto</a></li>
                      </ul>
                    </li>
                    <li>
                    <a href="marci.php">Constructii</a>
                      <ul>
                        <li><a href="">Acoperisuri</a></li>
                        <li><a href="">Ferestre si usi</a></li>
                        <li><a href="">Zidarie</a></li>
                        <li><a href="">Gleturi si adezivi</a></li>
                        <li><a href="">Termoizolatie</a></li>
                        <li><a href="">Sanitare</a></li>
                        <li><a href="">Alte categorii </a></li>
                      </ul>
                    </li>
                  </ul>
                </li>

2nd - In my register form i have 3 select inputs with categories, subcategories and subsubcategories, now i want when a user select a category display the 2 selects with subcat and subsubcat, and if a subcat is selected to display the right categories and so on. I've posted a picture below to see how it looks

 

How can i achive this two things! Thank you very much!

[benanamen]

How can i achive this two things

 

 

You can start with a proper database design. There is no such thing as sub categories. They are ALL categories. Some are Parents, some are Children.

 

Look this over. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

Edited February 22, 2017 by benanamen

[z4z07]

You can start with a proper database design. There is no such thing as sub categories. They are ALL categories. Some are Parents, some are Children.

 

Look this over. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

The database has a proper design with catID in subcat table etc.

The problem i have is how can i dynamic bring the to front.

Edited February 22, 2017 by z4z07

[Barand]

When the first category menu changes, send an AJAX request with the selected category id.

 

On the server, on receipt of the request, retrieve from the database those categories whose parent is the received id. Send back the options required to populate the second category menu.

 

Repeat the process with the second menu to retrieve the options for the third.

[benanamen]

The database has a proper design with catID in subcat table etc.

 

 

The fact that you have more than one table says you're wrong, but you know better right?

[z4z07]

For the 1st problem i have the following code to display the subcategories and categories in the menu, but this code will return me the result as you see in the screen i attached. http://tinypic.com/r/2ue2zxl/9

              <?php
                    $result = mysqli_query($conn, "SELECT * FROM domeniu_activitate INNER JOIN subdomeniu_activitate ON domeniu_activitate.id_domeniu = subdomeniu_activitate.id_domeniu WHERE categorie = 'servicii'");
                    while($row = mysqli_fetch_array($result))
                    {
                      echo '<li>';
                      echo '<a href="">' . $row['nume_domeniu'] . '</a>';
                      echo '<ul>';
                      echo '<li><a href="servicii.php">' . $row['nume_subdomeniu'] . '</a></li>';
                      echo '</ul>';
                      echo '</li>';
                   }
                   ?>

[gizmola]

The fact that you have more than one table says you're wrong, but you know better right?

Please keep in mind that english is not his primary language either, so what you interpret as a terse dismissive response, is most likely not meant to convey dismissal but rather just be matter of fact.

 

Having 2 tables is a bit wonky, but I've seen it done in many places. Small dataset and a join, but then anything static like a menu benefits greatly from caching. It's not egregiously incorrect as in having repeating groups etc. so I tend to try and work with people rather than demand they conform to my design, unless it is so hopelessly wrongheaded I don't see the point in continuing. In those cases, I think it's best to just make your point, and if you feel your good advice is being ignored, just move on to the next thread.

[z4z07]

Ok, i appologies for my english because i'm a little bit tired, so let me explain more clearly.

 

So lets say like this i have 2 main categories ( IT and MOBILE ) these 2 categories they are not in a table in database because the are only two and are prestabilited. For every category i have the same subcategories and subsubcategories like IT -> Computers --> HDD's and MOBILE -> Iphone --> Iphone7 (hope i'm clear).

 

In the database i store two tables one for IT (subcategory_table) and one for Mobile (subsubcategory_table) In these tables i have the following structure:

 

1. subcategory: id_subcat, name, url, it (or mobile - the name of the main category)

2. subsubcategory: id_subsubcat, name, url, id_subcat

 

Now base on these i wanna solve the problems from my main post. Hope i was very cleary and you understood my english!

Edited February 22, 2017 by z4z07

[z4z07]

Hey guys i manage to put something together for my secound problem, but i encounter i little issue. When i execute the form to post in my db the subcat and the subsubcat it stores the subcat and subsubcat id's, and i want to store only the url of the subcat and subsubcat. I will post bellow my php and javascript code:

$query = "SELECT id_domeniu, nume_domeniu, url_domeniu FROM domeniu_activitate WHERE categorie = 'servicii'";
$result = mysqli_query($conn, $query);

while($row = mysqli_fetch_assoc($result)){
  $categories[] = array("id" => $row['id_domeniu'], "val" => $row['nume_domeniu']);
}
$query = "SELECT id_subdomeniu, id_domeniu, url_subdomeniu, nume_subdomeniu FROM subdomeniu_activitate";
$result = mysqli_query($conn, $query);

while($row = mysqli_fetch_assoc($result)){
  $subcats[$row['id_domeniu']][] = array("id" => $row['id_subdomeniu'], "val" => $row['nume_subdomeniu']);
}
$jsonCats = json_encode($categories);
$jsonSubCats = json_encode($subcats);

      <?php
      echo "var categories = $jsonCats; \n";
      echo "var subcats = $jsonSubCats; \n";
      ?>
      function loadCategories(){
        var select = document.getElementById("categoriesSelect");
        select.onchange = updateSubCats;
        for(var i = 0; i < categories.length; i++){
          select.options[i] = new Option(categories[i].val, categories[i].id);          
        }
      }
      function updateSubCats(){
        var catSelect = this;
        var catid = this.value;
        var subcatSelect = document.getElementById("subcatsSelect");
        subcatSelect.options.length = 0; //delete all options if any present
        for(var i = 0; i < subcats[catid].length; i++){
          subcatSelect.options[i] = new Option(subcats[catid][i].val,subcats[catid][i].id);
        }
      }

[gizmola]

The main problem you have is that you are starting a new list for every row, rather than only when the outer category changes.

 

This type of code is tricky when you have to wrap things inside tags without knowing for sure what the complete data set is.

 

My advice to you would be to build an array which matches the exact structure and hierarchy. In order to do this you will need to read all the data from the query in first. Use a nested array to accomplish the structure.

 

So code like this will work:

 

$categories = array();
while($row = mysqli_fetch_array($result)) {

if (!array_key_exists($row['nume_domeniu']) {
$categories[$row['nume_domeniu']] = array();
}
$categories[$row['nume_domeniu']][] = $row['nume_subdomeniu'];
}
var_dump($categories);

 

This should give you an array that fits your desired menu hierarchy. All that is left is to create a routine that will foreach through the structure outputting the outer and inner UL's, and required LI's.