By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 920 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Generic MySQL/PHP Tree Schema?

P: n/a
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

May 14 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
On May 14, 12:52 pm, ashore <shor...@gmail.comwrote:
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>

May 14 '07 #2

P: n/a
ZeldorBlat wrote:
On May 14, 12:52 pm, ashore <shor...@gmail.comwrote:
>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);

}
May 16 '07 #3

P: n/a
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

May 19 '07 #4

P: n/a
>
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

May 20 '07 #5

P: n/a
The application area I have in mind here is a crisis management/
tracking/dispatch operation, in which there's a hierarchy of component
steps comprising the collection of responses. (Ex: a storm, in which
roads, bridges, waterways, telecommunications facilities, hospitals,
etc., are affected, with distinct problems associated with each.)

Kind of like a work breakdown structure, but somewhat more dynamic. I
looked at some project management applications, but a cursory scan
showed nothing like what we're discussing here -- although I can't
imagine we're breaking new ground.

-AS

May 20 '07 #6

P: n/a
ashore wrote:
The application area I have in mind here is a crisis management/
tracking/dispatch operation, in which there's a hierarchy of component
steps comprising the collection of responses. (Ex: a storm, in which
roads, bridges, waterways, telecommunications facilities, hospitals,
etc., are affected, with distinct problems associated with each.)

Kind of like a work breakdown structure, but somewhat more dynamic. I
looked at some project management applications, but a cursory scan
showed nothing like what we're discussing here -- although I can't
imagine we're breaking new ground.
It's ancient technology, but look at MUMPS, the Massachusetts
General Hospital system from the 1970s.

John Nagle
May 21 '07 #7

P: n/a
On 14 Mai, 18:52, ashore <shor...@gmail.comwrote:
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
Look at this: "Managing Hierarchical Data in MySQL" (
http://dev.mysql.com/tech-resources/...ical-data.html )

May 21 '07 #8

P: n/a
Perfect! Thanks,

-AS

On May 21, 6:50 am, maruerru <m...@mrumpf.dewrote:
>
Look at this: "Managing Hierarchical Data in MySQL" (http://dev.mysql.com/tech-resources/...ical-data.html)

May 21 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.