PhP auto generate table

[neo_philiac]

Hey guys,

I have weird problem,

Somewhere in my DB I have a table that had items like this:

id | Name | Sub_of
1 | A | 0
2 | B | 1
3 | C | 1
4 | D | 2
5 | E | 2

All I am trying to do is automatically generate a table so that I can see all the sub_of like this (A,B,C... are table headers I will be writing the table row by row. e.g. <tr>...</tr> ):

|------------------------------------|
|----------------A-------------------|
--------------------------------------
|-----------B---------- |------C-----|
--------------------------------------
|----D--| ----- E ------|------------|
--------------------------------------
|--------| --------------|------------|
|--------| --------------|------------|
|--------| --------------|------------|
...


I have tried to get the depth of the final table header and the number of final set of columns like this :


function tree_span($category_id,$find) {
$q = "SELECT category_id, name, id from ".$table_prefix."categories where sub_of = ".$category_id." order by name";
$query = mysql_query($q);

if (!$query) echo "Database Error : ".$q;
else {
$depth = mysql_num_rows($query);
if($depth !=0) {
$category_depth = $category_depth+1;
}
while ($row=mysql_fetch_row($query)) {
if ($row[0] != $id) {
$category_span = $category_span +1;
tree_span($row[0],$find);
}
}
}

I hope this makes sense about what I am looking for. Please help.

[bluewalrus]

You mean generate an html table from a db table?

The ., -, and _ aren't clear to me.

[neo_philiac]

Well the . - _ are just for formatting purposes. To show that from the mysql table that has the data (sub_of) and then there should be 2 columns under A, the under B there should be two columns and since C has no child, it should be its own column. Does that make sense?

[traq]

databases can't do that. they're fairly flat.
this is a fairly common method of dealing with multi-dimensional relationships in databases:


|--table---------------|
|--col A---------------|
------------------------

|--table A-------------|
|--col B----|--col C---|
------------------------

|--table B-------------|
|--col D----|--col E---|
------------------------

[neo_philiac]

OK. I guess I didn't make myself clear enough. I am making an html table from the relationship you see from the table. It should be like traversing thru the binary tree but I just dont know how to. I can get the depth and the nodes like this by recursion but not the rest of it:


function tree_span($category_id,$find) {
$q = "SELECT category_id, name, id from ".$table_prefix."categories where sub_of = ".$category_id." order by name";
$query = mysql_query($q);

if (!$query) echo "Database Error : ".$q;
else {
$depth = mysql_num_rows($query);
if($depth !=0) {
$category_depth = $category_depth+1;
}
while ($row=mysql_fetch_row($query)) {
if ($row[0] != $id) {
$category_span = $category_span +1;
tree_span($row[0],$find);
}
}
}

Hope this makes things clear. And 'traq' is right but how do I align the cells? Because I will be writing the cells row by row? To do that we need the end column numbers. Any thoughts?

[neo_philiac]

Any ideas?