SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Organising categories under one unique parent

    Hello, I have a bunch of categories in my database which uses the adjacency model to reference parent categories e.g.

    Code:
    category_id | parent_id | category_name
    1           | 0         | mammal
    2           | 1         | dog
    3           | 2         | poodle
    4           | 0         | reptile
    5           | 4         | turtle
    My SQL for retriveing such information looks like this:

    Code SQL:
    SELECT root.category_name  AS root_name, 
            down1.category_name AS down1_name,
            down2.category_name AS down2_name,
            root.category_id AS root_id,
            down1.category_id AS down1_id,
            down2.category_id AS down2_id,
        FROM table_categories AS root
        LEFT OUTER JOIN table_categories AS down1 ON down1.parent_id = root.category_id
        LEFT OUTER JOIN table_categories AS down2 ON down2.parent_id = down1.category_id
        WHERE root.parent_id = 0
        ORDER BY root_name, down1_name, down2_name

    My php for displaying the fetched data looks like this

    Code PHP:
    foreach($returnData as $row)
    {
       echo $row['root_name'];
       echo ' > '.$row['down1_name'];
       echo ' > '.$row['down2_name'];
       echo '<br />';
    }

    The output of which looks something along the lines of this:

    Code PHP:
    Mammal >
    Mammal > Dog
    Mammal > Dog > Poodle
    Reptile >
    Reptile > Turtle

    However what I'm after is output that looks more like this:

    Code PHP:
    Mammal
     Dog
      Poodle
    Reptile
     Turtle

    As you can see the parent is always duplicated the way I have it at the moment. My head is going in cirlces trying to figure out how to combine each category under one unique parent. Is there a way of doing this?

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    I hate adjacency model for this reason. But; lets see if i can do it off the top of my head. (READ: UNTESTED.)

    SELECT * FROM table_categories ORDER BY parent_id;
    PHP Code:
    $out[0] = "";
    while(
    $row fetch_row($result)) {
       
    $out[$row['category_id']] = ucfirst($row['category_name']);
       
    $children[$row['parent_id']][] = $row['category_id'];
    }
    recurse(0);

    function 
    recurse($id,$prefix "") {
       global 
    $out,$children;
       echo 
    $prefix.$out[$id];
       foreach(
    $children[$id] AS $child) {
         
    recurse($child,$prefix."&nbsp;");
       }


  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for your suggestion starlion I will try it out. In the mean time, what would you suggest as a better way of doing this? I looked at lineages but in my application keeping the lineage integrity intact would be a real pain as the user has to be able to sort and move categories around so I let that one go and went to the adjacency model instead.

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Normally I would work within a Nested Set Model personally. It really is a matter of personal choice and what you need the data to do...

    Adjacency works fine if you want to use the whole dataset (as you do here), but if you want to use only a certain segment (Root To Node, for example, as in breadcrumbs), I find NSM much easier to code for. (Again, thats personal preference.)

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2005
    Posts
    70
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've been looking over your code StarLion and am having trouble trying to understand it. It works in part but then displays an undefined index error for the foreach.

    Would you mind explaining it the code to me a bit pelase. Specifically what does prefix do and should recruse always = 0?

  6. #6
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    71 Post(s)
    Tagged
    0 Thread(s)
    Ok. Line by line.

    Code:
    $out[0] = "";
    Initialize the 'root' of my tree.
    Code:
    while($row = fetch_row($result)) {
    this is a Foreach on the resultset.
    Code:
       $out[$row['category_id']] = ucfirst($row['category_name']);
    Define the node within the node array. ucfirst makes the first letter capital.
    Code:
       $children[$row['parent_id']][] = $row['category_id'];
    Inside the children array, this node's parent has a new child (this node.) Note that top level elements dont actually have a defined parent(ID: 0 does not exist in your record set.), so the root node is defined in the first line.
    Code:
    }
    recurse(0);
    Endforeach and start recurse to walk the tree from the root node (0).
    Code:
    function recurse($id,$prefix = "") {
    Define function recurse. Required Parameter $id, Optional Parameter $prefix (default = null).
    Code:
       global $out,$children;
    Gonna need my arrays for referencing.
    Code:
       echo $prefix.$out[$id];
    Echo out the current element. (For 0, this will echo nothing, as both $prefix and $out[0] are null.)
    Code:
       foreach($children[$id] AS $child) {
    For each child of the current node
    Code:
         recurse($child,$prefix."&nbsp;");
    call this function using the child's ID and add a space character to the existing $prefix.
    Code:
       }
    }
    EndForeach, EndFunction.

    Now, as to solving the undefined index, add this to the while loop after putting the child in the parent's node.

    PHP Code:
    $children[$row['category_id']] = array(); 


Bookmarks

Posting Permissions

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