Welcome Guest, Not a member yet? Register   Sign In
sub-categories and sub-subcategories on a associative array with CI
#1

[eluser]MEM[/eluser]
After some long days,

I'm trying to create an associative array containing only sub-categories and sub-subcategories, from a self-reference table.

I'd like to have something like this:

[
SUBCATEGORY1 [
SUB_SUBCATEGORY1
SUB_SUBCATEGORY2
SUB_SUBCATEGORY3
]
SUBCATEGORY2
SUBCATEGORY3
]

I'm having troubles to get out an array like this from my model.
Now, my model looks like this:

Code:
function getSubCategorias($id_cat)
{
    $this->db->where('parent_id_cat', $id_cat);
            
    $this->db->where('activa_cat =', 1);
            
    $query=$this->db->get('categoria');
            
    if($query->num_rows()>0)
    {
        return $query->result();
    }
}

I'm retrieving the $id_cat because, I want to show each time, only the subcategories of a specific category. I mean, if the user is on page related with category 1, I only want to show subcategories and sub-subcategories of that main category 1.

What query should I have to get all subcategories and sub-subcategories of a specific category, in order to build the desired associative array?

Can I have your help on this... Sad

Always newbie,
Márcio
#2

[eluser]Johan André[/eluser]
You should use recursion.
With the model you are using there is no other way.

Usually I would go with the Modified Tree Preorder-model for the scenario you are describing.
Then you could get the whole tree with one query... Smile

There is a CI-model for it I think. MPTTree or something...
#3

[eluser]obiron2[/eluser]
Do you know how many levels deep your recursive array will go. If you know the extent it makes life a bit easier. The following would work as solutions where you know how deep (potentially) you have to go.

Either

a nested statement that links the table to itself and returns a flat data set
Code:
SELECT FistLevel,SecondLevel,ThirdLevel FROM (
(SELECT ID as 'FirstLevel' from myTable) 'L1'
Left Join (SELECT ID as 'SecondLevel' from myTable L2) on L2.ParentID = L1.'FirstLevel'  
Left Join (SELECT ID as 'ThirdLevel' from myTable L3) on L3.ParentID = L2.'SecondLevel'
)

You will then get as results set that looks like

1,2,3
1,2,4
1,2,5
1,6,7
1,6,8
2,9,10
2,9,11
2,9,12
2,13,NULL
14,NULL,NULL

which you would then handle the formatting, display and links through the controller and view

Or

Select all of the top level entries and return the result as an array, then loop through the array elements and query the database for each of the level 2 entries, using the element value as a filter and inserting the results array into the array as a new key->value pair (value is an array). You would need to repeat this for every level of the data set so is only useful where you know how far down you need to go. It is very db intensive because you have to hit the database once for each potentially nested results set.
#4

[eluser]MEM[/eluser]
Ok. To clear some things out, I'm using recursion (I believe) Big Grin

id_cat
name_cat
description_cat
parent_id_cat

The intended result is, more then a menu, some kind of filter navigation.
And I will have no more then 3 levels. I mean, one level on 1 menu, and two levels on
another menu. The first menu only for categories, the second only for subcategories and sub-subcategories.

Here is some sort of squeme on the navigation sequence:


If the user clicks on category1 it will show like this:
----------------------------------------------------------------------------------------
category1 category2 category3
----------------------------------------------------------------------------------------

--------------------
subcategory1 { product list of all subcategories and
subcategory2 sub-subcategories belonging to category1 }
subcategory3
--------------------
----------------------------------------------------------------------------------------


If the user clicks on subcategory 1, and that subcategorie has sub-subcategories:
----------------------------------------------------------------------------------------
category1 category2 category3
----------------------------------------------------------------------------------------

--------------------
subcategory1 sub-subcategory1 {product list of all sub-subcategorias
subcategory2 sub-subcategory2 belonging to subcategory1}
subcategory3
--------------------
-------------------------------------------------------------------------------------------------


If the user clicks on subcategory 1, and that subcategory doesn’t have any sub-subcategories:
----------------------------------------------------------------------------------------
category1 category2 category3
----------------------------------------------------------------------------------------

--------------------
subcategory1 {product list of subcategory1}
subcategory2
subcategory3
--------------------
-------------------------------------------------------------------------------------------------


Finally, if the user clicks on one sub-subcategory, then:
----------------------------------------------------------------------------------------
category1 category2 category3
----------------------------------------------------------------------------------------

--------------------
subcategory1 sub-subcategory1 {product list of
subcategory2 sub-subcategory2 sub-subcategory1}
subcategory3
--------------------
-------------------------------------------------------------------------------------------------



What do you advice according to this structure?


Thanks a lot,
Márcio
#5

[eluser]MEM[/eluser]
Despite the fact that I'm using the same table, I'm having a method on my model for retrieving the categories, and another method on my model for retrieving the subcategories and sub-subcategories.

How would a query like this, help me to return an array where my firstlevel elements should be my subcategories, and my secondlevel elements my sub-subcategories?

Code:
SELECT FistLevel,SecondLevel FROM (
(SELECT ID as 'FirstLevel' from myTable) 'L1'
Left Join (SELECT ID as 'SecondLevel' from myTable L2) on L2.ParentID = L1.'FirstLevel'  
)

Anyway, I'm having a hard time trying to understand this query.
The FirstLevel and SecondLevel are aliases? What are L1 and L2 ?
Can you or anyone else, properly describe in text what's going on on this query so that I can understand it?

I will then have something like this:

1,2
1,6
2,9
10,NULL

On the first row I will get an id of 2 and a parent_id of 1. So 1 should be my subcategory, and 2 my sub-subcategory... ?

On the second row I will get an id of 6, with a parent_id of 1, so 1 should be my subcategory, and 6 my sub-subcategory...

Later, on number 10
So 10 should be my subcategory and I will have no sub-subcategory here...


Am I near? Or Am I lost?


Regards,
Márcio
#6

[eluser]BrianDHall[/eluser]
I had a very similar problem on a recent project. There were top-level categories, sub-categories, sub-sub-categories, and possibly even deeper. I heard of recursion and all that but damn, I couldn't wrap my head around any of it.

So I think I found a rather simple solution instead that worked great and doesn't make my brain try to eat itself. I hope this helps as a guideline, the actual code I have is pretty site-specific.

Here's what I did as a basic guideline:

Get all the top-level categories, put them in an array.
---
Call a foreach on the array by reference, to make sure changes made to the array inside the loop are available on the next iteration.

Check to see if the category had sub-categories by calling a simple function like has_children(), a function I made that checked to see if a category had children and if so returned true.

If the category has children then add it to the end of the main array you created in step one.
---
In this way you will step through every category and subcategory. I used this method to define ever deepening levels of nested navigation for sub-categories.

Now, lets say you want to pull only sub-categories of one specific category. Then you just start the the above process with an array with only the category you want.

If you make this a function, with a little work you suddenly make it 'recursive' by making it call itself in certain instances.

So create an array, feed it to the function, if the function sees it has children then it makes an array and calls that same function - each should do some work on an outside variable.

I know this isn't exactly a full solution, but after weeks of sweating this issue it was this light-bulb moment of using an array by reference that made it all so much easier. I think I hard-coded a 4-deep loop instead of using recursive calling of a function, just because it hurt my head too much...but hey, whatever works.

You can see how it basically works by looking at the category system of the site it was done for - BukuBux.com. At the top click a category like "Professional Services". It will lead to a sub-menu system with it's subcategories - click something like "Automotive" and you'll get the top-level navigation, the sub-navigation, and then sub-sub-navigation, etc.

It's all dynamic from a database of categories that have a 'category' and a 'parent_category' field, and that's it. So I can re-arrange, add new categories, etc, and it all 'just works'.

The uri string is built similarly, for extra juicy search-engine friendly URLs.
#7

[eluser]MEM[/eluser]
Brian
Thanks a lot for your example. It's exactly a navigation like that that I'm looking for.

However, I'm very very newbie. I don't want to give up on this, but gash, this is getting hard. I thank you for all the process you have described, I will give a second and third read on that...

But...
Just to add another variable to this mess, I've found a query to return subcategories and sub-subcategories, ONLY. No categories involved.

Code:
SELECT DISTINCT t2.name_cat AS subcategory, t3.name_cat AS subsubcategory
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent_id_cat = t1.id_cat
LEFT JOIN category AS t3 ON t3.parent_id_cat = t2.id_cat
WHERE t1.id_cat =1


Dying,
Márcio
#8

[eluser]BrianDHall[/eluser]
Just incase it might be useful, I found and yanked out how I did it on the mentioned site. Obviously there are some custom functions, but none of them are very fancy, just basic database queries.

One thing though, if it makes you feel any better you are actually tackling something extremely hard that many systems handle simply by not supporting it. Joomla, for instance - their integrated category/menu system allows Categories, Subcategories...and that's all. If you want more, tough.

I believe the technical name for this is an unlimited nested taxonomical menu system, or something like that. It's considered a big deal, so don't feel bad that it's really hard. I dreaded making this work as it was one of the reasons I tossed Joomla aside to start with.

Anyhoo, just as food for thought incase it's useful, here's how I did it. I did it in a view actually, to tell you how mess it was - but damned if I'm going to do anything about it, it works and I'm willing to accept that Wink

The cellcount is because the page will break if there are too many categories, the table will get so wide that the screen goes screwy. The parts about active category image is to make the chosen upper level categories be highlighted, and to give the "open safe" image on my page instead of the closed, 'inactive' safe icon.

It's by no means the most elegant ways of doing it and I'm still not sure I understand why I did while($parent)...but again, it worked and I'm leaving that dog alone.

Hope it's useful!

Code:
<?php

$cellcount = 0;

foreach ($categories as $category)
{
    $cellcount = $cellcount + 1;

    // Control amount of cells per row so as not to break browsers width.
    if (in_array($category['uricode'], $urisegments) )
    {
        // Set active category image.
        $imgurl = site_url() . 'images/stories/home-on.png';
        $cell = ' bgcolor="#006600"';
    }
    else
    {
        $imgurl = site_url() . 'images/stories/home.png';
        $cell = '';
    }
    
    $parenturi = '';
    
    if ($category['ParentCategoryID'])
    {
        $parent = $this->Categories->getCategoryByID($category['ParentCategoryID']);
        
        while ($parent)
        {
            $parenturi = $parent['uricode'] . $parenturi;
            
            if ($parent['ParentCategoryID'])
            {
                $parent = $this->Categories->getCategoryByID($parent['ParentCategoryID']);
                $parenturi = '/' . $parenturi;
            }
            else
            {
                $parent = false;
            }
            
        }
    }

    echo '<td width="50"' . $cell . '><a href="' . site_url('pensacola_florida_discounts/coupons_by_category/' . $parenturi . '/' . $category['uricode']) . '" title="' . $category['CategoryPageTitle'] . '" class="submenu"><img src="' . $imgurl . '" alt="' . $category['CategoryPageTitle'] . '" title="' . $category['CategoryPageTitle'] . '" height="45" width="45" /></a></td><td width="65"' . $cell . '><a href="' . site_url('pensacola_florida_discounts/coupons_by_category/' . $parenturi . '/' . $category['uricode']) . '" title="' . $category['CategoryPageTitle'] . '" class="submenu">' . $category['CategoryMenuText'] . '&nbsp;</a></td>
    ';

    if ($cellcount >= 8)
    {
        echo '<td>&nbsp;</td></tr><tr>';
        $cellcount = 0;
    }

}

?&gt;
#9

[eluser]MEM[/eluser]
The fighting days are not over, I'm killing myself on this.

With the help of great gurus (credits to oddz on sitepoint forums), I was able to create a model that returns an array like this:

Code:
Array (
    [0] => Array (
        [id_cat] => 6
        [nome_cat] => Fitofármacos
        [parent_id_cat] => 1
        [children] => Array (

                [0] => Array (
                    [id_cat] => 9
                    [nome_cat] => Herbicidas
                    [parent_id_cat] => 6
                    [children] => Array ( )
                 )
    
                [1] => Array (
                    [id_cat] => 10
                    [nome_cat] => Insecticidas
                    [parent_id_cat] => 6
                    [children] => Array ( )
                )    
        
                [2] => Array (
                    [id_cat] => 11
                    [nome_cat] => Fungicidas
                    [parent_id_cat] => 6
                    [children] => Array ( )
                )
            )
    
    
     )

     [1] => Array (
        [id_cat] => 7
        [nome_cat] => Adubos
        [parent_id_cat] => 1
        [children] => Array ( )
               )

    [2] => Array (
         [id_cat] => 8
        [nome_cat] => Sementes
        [parent_id_cat] => 1
        [children] => Array ( )
    )

)


This exact structure I have passed to my view file. So, the "only" think I have to do is to play with this hierarchy and with the URI segment values.

This array is called $menucategorias

I'm having this:

Code:
foreach ($menucategorias as $mainitem)
{
  //hoping to get parent...      
  echo $mainitem['nome_cat'];
        
  if($CI->uri->segment(3)==$mainitem['parent_id_cat'])
  {
        //hopping to get parent and...  
        echo $mainitem['nome_cat'];
    
        //hoping to get childs...    
    foreach ($mainitem['children'] as $subitem)
    {
        echo $subitem['nome_cat'];
    }
  }
}

But I can only hope. Since I'm always getting the childrens only, and never the parents.

Big Grin~~~~~
#10

[eluser]markup2go[/eluser]
MEM can you share the model you're using to build that array? I'm having the same difficulty figuring how this functionality is SUPPOSED to be done. Can anyone add any tips or examples? I'm building a small online shop that requires unlimited subcategories as well as a many-to-many relationship between categories and products.

Here is how my categories table is setup:

Table 1: Categories
Columns: id, name, parent_id

In addition, here is my products tables

Table 2: Products
Columns: id, name, price, description (etc etc)

Table 3: Categories_Products
Columns: id, categories_id, products_id

Thanks for the help it is greatly appreciated.




Theme © iAndrew 2016 - Forum software by © MyBB