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?