Organising categories under one unique parent

[Dorza]

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

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:

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

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:

Mammal >
Mammal > Dog
Mammal > Dog > Poodle
Reptile >
Reptile > Turtle

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

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?

[StarLion]

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;

$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." ");
   }
} 

[Dorza]

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.

[StarLion]

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.)

[Dorza]

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?

[StarLion]

Ok. Line by line.

$out[0] = "";

Initialize the 'root' of my tree.

while($row = fetch_row($result)) {

this is a Foreach on the resultset.

   $out[$row['category_id']] = ucfirst($row['category_name']);

Define the node within the node array. ucfirst makes the first letter capital.

   $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.

}
recurse(0);

Endforeach and start recurse to walk the tree from the root node (0).

function recurse($id,$prefix = "") {

Define function recurse. Required Parameter $id, Optional Parameter $prefix (default = null).

   global $out,$children;

Gonna need my arrays for referencing.

   echo $prefix.$out[$id];

Echo out the current element. (For 0, this will echo nothing, as both $prefix and $out[0] are null.)

   foreach($children[$id] AS $child) {

For each child of the current node

     recurse($child,$prefix." ");

call this function using the child's ID and add a space character to the existing $prefix.

   }
}

EndForeach, EndFunction.

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

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