Generic MySQL/PHP Tree Schema?

Folks, any url's around re subject matter? Performance isn't
necessarily a problem, and the depth will be under, say, twenty.

Thanks, all.

--AS

On May 14, 12:52 pm, ashore <shor...@gmail.com> wrote:
> Folks, any url's around re subject matter? Performance isn't
> necessarily a problem, and the depth will be under, say, twenty.
>
> Thanks, all.
>
> --AS

<http://www.google.com/search?q=sql+tree+structure>

ZeldorBlat wrote:
> On May 14, 12:52 pm, ashore <shor...@gmail.com> wrote:
>> Folks, any url's around re subject matter? Performance isn't
>> necessarily a problem, and the depth will be under, say, twenty.
>>
>> Thanks, all.
>>
>> --AS
>
> <http://www.google.com/search?q=sql+tree+structure>
>

If you're looking to create a menu type tree you need to look into
recursion, i use a function like below, not saying this is the best as
I'm a newbie but it work and creates a menu tree, I then use another
function to parse this tree into the format i need (usually html) using
another similar recursing function.

public function makeTree( $menu_seq, $depth ) {

$sql = "SELECT * FROM menu WHERE menu_parent=" . $menu_seq . "
ORDER BY menu_dspseq ASC;";
$ds = mysql_query( $sql ) or die(mysql_error());
if (mysql_num_rows($ds)>0) {
while ( $dr = mysql_fetch_assoc($ds) ) {
$sql = "SELECT COUNT(menu_seq) FROM menu WHERE
menu_parent = " . $dr['menu_seq'];
$dsc = mysql_query($sql);
$drc = mysql_fetch_array($dsc);
$array[$dr['menu_seq']]['menu_text'] =
htmlentities($dr['menu_text']);
$array[$dr['menu_seq']]['menu_parent'] =
$dr['menu_parent'];
$array[$dr['menu_seq']]['menu_page'] =
$dr['menu_page'];
$array[$dr['menu_seq']]['depth'] = $depth;
if ($drc>0) {
$array[$dr['menu_seq']]['children'] =
$this->makeTree( $dr['menu_seq'], $depth+5 );
} else {
$array[$dr['menu_seq']]['children'] = null;
}
}
mysql_free_result($ds);
}
return (isset($array) ? $array : false);

}

Thanks, all - yr responses are appreciated.

Now, answering my own posting in case others are following this
thread: Do take a look at

http://freshmeat.net/projects/nodetree/

Pretty good, actually.

-AS

>
> If you're looking to create a menu type tree you need to look into
> recursion, i use a function like below, not saying this is the best as
> I'm a newbie but it work and creates a menu tree, I then use another
> function to parse this tree into the format i need (usually html) using
> another similar recursing function.
>


I think if you're going to have any kind of volume and depth at all,
it's probably better to stay away from recursive solutions, since you
could end up with an awful lot of queries per page load. Of course,
you could use caching or something to minimize this, but... I like
outline style solutions, where you keep all your data in one table and
have one column that keeps some data outline style ("1.12.5" or
similar) It looks like that nodetree project uses soemthing like
this...

Aerik