Tree Hierarchy

Anon

How do you generate a tree from a table a bit like is done here with the with the message threads. I guess you need to reference a parentID. How do you do this without many queries?

Thanks

Will

[deleted]

Make sure every child has a parentid.
Use parentid=0 for children without parents.
If you have several 'master' parents, like threads in a forum,
it's a good idea to also store a 'grandparentid' which is the id of the first parent in the thread.

Then fetch all the records that belong to the thread (that have the same grandparentid).

Now comes the hard part: recursion.
Create a function that takes a parentid,
then goes through the array and and prints
each entry in the array that has a parentid as specified.
Every time this function finds a match, is should call itself, with the id of the found record as a parameter.
That will print all the children of the current record.
And everytime a child is found, all it's children will be printed, etc.

BUT: be carefull. Make sure that the function skips records where for example, the parentid=id, or it will become an endless loop.

tashbee

Hope this helps I wrote this having read vincents reply

DB structure is where id_category is unique and auto incrementing

id_category | sub_cat_assign | cat_name
data example
1 0 accesories
2 0 lamps
3 1 hand bag
4 1 gloves
5 3 small handbags
6 0 home

you should get
accesories
-hand bags
--small handbags
-gloves
home

Hope this helps

<?php

$conID = mysql_connect ("localhost","username","password");
mysql_select_db("DATABASE", $conID);

// define spacer and call function
$space = "";
tree($conID,0,$space);

function tree($conID,$id_cat,$spacer)
{// Start of function

// select grand father ie with sub_cat_assign set to 0
$select_cat = "select id_category,
sub_cat_assign,
cat_name
from og_category
where sub_cat_assign = \"$id_cat\"";

$sub_cat_query = mysql_query($select_cat,$conID);
while ($cat_row = mysql_fetch_array($sub_cat_query))
{
$id_category = $cat_row['id_category'];
$sub_cat_assign = $cat_row['sub_cat_assign'];
$cat_name = $cat_row['cat_name'];

// Get results and echo
echo("$spacer $cat_name<br>");

// Check to see if category has any children
$check_cat_assign = "select id_category
from og_category
where sub_cat_assign = \"$id_category\"";

$check_query = mysql_query($check_cat_assign,$conID);
$check_row = mysql_num_rows($check_query);

// If cat has child then call function
if ($check_row)
{
// Add some -- to show the indenting
$space .= $spacer;
$space .= "-";
tree($conID,$id_category,$space);

}// end if $check row

}// end of while $cat row loop

} // end of function tree

Anon

I was trying to do exactly the same thing, and playing with Thomas's code I came up with the following that uses only 1 MySQL query call:

$query = mysql_query("SELECT catid, name, parentid FROM Categories ORDER BY listorder");
if (mysql_num_rows($query) > 0 ) {
while ($myrow = mysql_fetch_row($query)) {
$catid[] = $myrow[0];
$name[$myrow[0]] = stripslashes($myrow[1]);
$parent[$myrow[0]] = $myrow[2];
}
getcatlist(&$content,$catid,$name,$parent,0,'');
echo $content;
}