Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Jun 2010
    Location
    Enschede, Netherlands
    Posts
    34
    Thanks
    18
    Thanked 0 Times in 0 Posts

    Question Making a category tree with array

    Dear friends,

    I am trying to make a category tree out of a database table. let me make it simple. I will only have 2 type of categories, PARENT and CHILD . parents are not going to host articles, so they are only a way to sort the childeren. I want something like this"

    - Electronics //PARENT 1
    - Academic //CHILD 1
    -Microprocessors //CHILD 2
    - Hardware //PARENT 2
    - Video cards //CHILD 1
    - CPU //CHILD 2

    and so on...

    I want it in an array, and it should contain all the table content for each row.

    and my table is:


    Any help/suggestion is appriciated!

  • #2
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Heh, this is something I've been throwing around for a while now, and I've just settled on a method I like.

    First and simplest method is to keep with what you have right now. If you know the maximum depth (of children) just self join once per level and process the order in PHP (processing the order is a little harder, I haven't quite figured it out yet, but processing should be done with PHP anyway, not the DB).

    Second method is one I used for a while. It makes working with existing forums very easy, but when you change a forum's location/order, or you add or delete a forum, it requires a large number of updates (one command, but a lot of updating). You basically nest forums by giving them a left and right bound, and children have bounds within the first (ie, a parent would be 1, 6, and 2 children within would be 2, 3 and 4, 5. If you find everything between 1 and 6, you have the children.) You can find information on it here
    http://dev.mysql.com/tech-resources/...ical-data.html
    http://articles.sitepoint.com/articl...data-database/

    I've stopped using that method due to its upkeep issues. If you have a more or less static forum though, I personally like it a lot (very clean, easy to understand code, not much processing).

    The last method, given to me by our very own Old Pendent is to store a left padded hierarchy in each row, ie, if we assume each of the example categories' ids are sequential in order:
    Electronics: 001
    Academic: 001-002
    Microprocessors: 001-003

    You can pull the hierarchy at any time or do a LIKE %001% to find the categories on the tree related to electronics.

    I gave these methods in case you want to expand beyond 2 levels. If you're going to stick to 2 levels, I'd do something like this
    Code:
    SELECT * FROM `table` ORDER BY pid, `sort`
    Then when you're extracting each row, you'll get the parent categories then the children categories. Store the parents into an array, then what I did is stored children as an element of the parent array (ie, $forums['children'] = arrayinfo).

    I at least know of no direct way to sort it in mysql directly, but again, its a processing issue, which should be done in PHP.

  • Users who have thanked Keleth for this post:

    Saeid (06-22-2010)

  • #3
    New Coder
    Join Date
    Jun 2010
    Location
    Enschede, Netherlands
    Posts
    34
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks a lot Keleth, it helped me to understand it better. but would you please give some codes as well so I can get the idea better?!

  • #4
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Quote Originally Posted by Saeid View Post
    Thanks a lot Keleth, it helped me to understand it better. but would you please give some codes as well so I can get the idea better?!
    For which method?

  • #5
    New Coder
    Join Date
    Jun 2010
    Location
    Enschede, Netherlands
    Posts
    34
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Keleth View Post
    For which method?
    Well in my case I know that I will have only two type of categories, Parents, and childs. and a child only belongs to one parent. and there is no sub-child and also I am limited somehow to follow that kind of MySQL table I posted at first. whats your suggestion?

    Thanks.
    Last edited by Saeid; 06-23-2010 at 01:34 AM.

  • #6
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    I would select the details you want and order first by pid then sort.

    Then as you retrieve the columns, you'll first get all the parents: store them into an array (these are the ones with pid = 0). Then you'll get the children. I would just add a array element that contains the child arrays.

  • Users who have thanked Keleth for this post:

    Saeid (06-23-2010)

  • #7
    New Coder
    Join Date
    Jun 2010
    Location
    Enschede, Netherlands
    Posts
    34
    Thanks
    18
    Thanked 0 Times in 0 Posts
    Thanks again, I get what do you mean but it's a bit funny that I can't figure the php code out for this task yet!

  • #8
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    Quote Originally Posted by Saeid View Post
    Thanks again, I get what do you mean but it's a bit funny that I can't figure the php code out for this task yet!
    Basically
    PHP Code:
    $result mysql_query('SELECT * FROM `table` ORDER BY pid, `sort`');
    while (
    $info mysql_fetch_array($result)) {
    if (
    $info['pid'] == 0$categories[] = $info;
    else 
    $categories[$info['pid']]['children'] = $info;

    I'll leave you to figure out what to do with the results, should be obvious from here :-)

  • Users who have thanked Keleth for this post:

    Saeid (06-24-2010)

  • #9
    New Coder
    Join Date
    Jun 2010
    Location
    Enschede, Netherlands
    Posts
    34
    Thanks
    18
    Thanked 0 Times in 0 Posts
    After some thinking, I came to this Idea. I post my soloution here maybe it help other people. I also still have some question at the end of this post maybe somone can help!



    and here is the code, note that I used a MySQL connection class.

    PHP Code:
        $parentCats      $_DB->Query("SELECT * FROM category WHERE parent IS NULL ORDER BY parent_sort ASC");
        
    $parentCatsCount $_DB->GetResultNumber($parentCats);
        
        
    $childCats      $_DB->Query("SELECT * FROM category WHERE parent IS NOT NULL ORDER BY child_sort, parent_sort ASC");
        
    $childCatsCount $_DB->GetResultNumber($childCats);
        
        
    $totalCats      $_DB->Query("SELECT * FROM category");
        
    $totalCatsCount $_DB->GetResultNumber($totalCats);
        
        
    $articleCats = array();


        
        
    // GET PARENT CATEGORIES
        
    for($i=0$i<$parentCatsCount$i++) 
        {   
            
    $parents[$i]["category_id"] = $_DB->GetResultValue($parentCats$i"category_id");       
            
    $parents[$i]["name"] = $_DB->GetResultValue($parentCats$i"name");
            
    $parents[$i]["parent"] = $_DB->GetResultValue($parentCats$i"parent");  
            
    $parents[$i]["parent_sort"] = $_DB->GetResultValue($parentCats$i"parent_sort");          
            
    $parents[$i]["child_sort"] = $_DB->GetResultValue($parentCats$i"child_sort");          
            
    $parents[$i]["desc"] = $_DB->GetResultValue($parentCats$i"desc");
            
    $parents[$i]["url"] = $_DB->GetResultValue($parentCats$i"url"); 
            
    $parents[$i]["img"] = $_DB->GetResultValue($parentCats$i"img");
            
    $parents[$i]["forum"] = $_DB->GetResultValue($parentCats$i"forum");
            
            
    // Counting number of childs of the same paret
            
    $query $_DB->Query("SELECT * FROM category WHERE parent=".$parents[$i]["category_id"]." ORDER BY child_sort");        
            
    $parents[$i]["childs"] = $_DB->GetResultNumber($query);
            
            if( (
    $parents[$i]["childs"] = $_DB->GetResultNumber($query)) != )
            {       
                for(
    $j=0$j $_DB->GetResultNumber($query); $j++)
                {
                
                
    $parents[$i][$j]["category_id"] = $_DB->GetResultValue($query$j"category_id");       
                
    $parents[$i][$j]["name"] = $_DB->GetResultValue($query$j"name");
                
    $parents[$i][$j]["parent"] = $_DB->GetResultValue($query$j"parent");  
                
    $parents[$i][$j]["parent_sort"] = $_DB->GetResultValue($query$j"parent_sort");          
                
    $parents[$i][$j]["child_sort"] = $_DB->GetResultValue($query$j"child_sort");          
                
    $parents[$i][$j]["desc"] = $_DB->GetResultValue($query$j"desc");
                
    $parents[$i][$j]["url"] = $_DB->GetResultValue($query$j"url"); 
                
    $parents[$i][$j]["img"] = $_DB->GetResultValue($query$j"img");
                
    $parents[$i][$j]["forum"] = $_DB->GetResultValue($query$j"forum");            
                }
            }                        
        } 
    Now can you tell me how to get the data out of this array? I want to sort them and make every category name a hyperlink. like this:

    - Electronics
    - Academic
    -Microprocessors
    - Hardware
    - Video cards
    - CPU

    I think foreach will come handy here but how to do that for multidimensional arrays!

    Thanks.

  • #10
    Senior Coder
    Join Date
    Jun 2008
    Location
    New Jersey
    Posts
    2,546
    Thanks
    45
    Thanked 259 Times in 256 Posts
    I think the methodology is the same... I know of no way to do a subsort (select elements under a particular selection). Changing the sort just requires rewording. Again, I think the best way to do it is in PHP. Basically you've changed it to 3 queries now instead of 1, and you still have a decent chunk of php involved.

    Also, I wouldn't start your ids at 0 then have a reference id as NULL (you're obviously not using int). Start at 1 then when there is no reference, go 0. This is however just a personal opinion (I think). If you're storing integers, have an integer col.


  •  

    Tags for this Thread

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •