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

Adjacency List Model Hierarchical Structure into HTML Structured List

P: n/a
I have been studying the Adjacency List Model as a means of achieving
a folder structure in a project I am working on. Started with the
excellent article by Gijs Van Tulder

http://www.sitepoint.com/article/hie...-data-database

My database has this basic structure:

Id
FolderName
ChildOf

where ChildOf is the Id of the Folder that the current folder is
nested within.

Although I can use this to make indented text lists - I want the
output to be a hierarchical HTML structured list using <ol><li>
structures.

So, for example, my folders are:

News
-Government Information
-BBC
-National Press
Books
LIS
-UKEIG
-NPRIE

I want the output to be:

<ol>
<li>News
<ol>
<li>Government Information</li>
<li>BBC></li>
<li>National Press</li>
</ol>
</li>
<li>Books</li>
<li>LIS
<ol>
<li>UKEIG</li>
<li>NPRIE</li>
</ol>
</li>
</ol>

and for this to work irrespective of the number of levels in the
hierarchy.

Any ideas?

Jun 6 '07 #1
Share this Question
Share on Google+
12 Replies


P: n/a

"Steve" <St***********@gmail.comschreef in bericht
news:11**********************@p77g2000hsh.googlegr oups.com...
>I have been studying the Adjacency List Model as a means of achieving
a folder structure in a project I am working on. Started with the
excellent article by Gijs Van Tulder

http://www.sitepoint.com/article/hie...-data-database

My database has this basic structure:

Id
FolderName
ChildOf

where ChildOf is the Id of the Folder that the current folder is
nested within.

Although I can use this to make indented text lists - I want the
output to be a hierarchical HTML structured list using <ol><li>
structures.

So, for example, my folders are:

News
-Government Information
-BBC
-National Press
Books
LIS
-UKEIG
-NPRIE

I want the output to be:

<ol>
<li>News
<ol>
<li>Government Information</li>
<li>BBC></li>
<li>National Press</li>
</ol>
</li>
<li>Books</li>
<li>LIS
<ol>
<li>UKEIG</li>
<li>NPRIE</li>
</ol>
</li>
</ol>

and for this to work irrespective of the number of levels in the
hierarchy.

Any ideas?
Recursion my friend. I've done something simular recently, but a bit
verbose; in every recursion I used an SQL query.

However, yesterday I stumbled across an example that does the job far more
efficient:

http://www.tagarga.com/blok/on/061029

Courtesy of newsgroup contributor gosha bine.

HTH
Jun 6 '07 #2

P: n/a
Rik
On Wed, 06 Jun 2007 15:11:23 +0200, Steve <St***********@gmail.comwrote:
I have been studying the Adjacency List Model as a means of achieving
a folder structure in a project I am working on. Started with the
excellent article by Gijs Van Tulder

http://www.sitepoint.com/article/hie...-data-database

My database has this basic structure:

Id
FolderName
ChildOf

where ChildOf is the Id of the Folder that the current folder is
nested within.

Although I can use this to make indented text lists - I want the
output to be a hierarchical HTML structured list using <ol><li>
structures.

So, for example, my folders are:

News
-Government Information
-BBC
-National Press
Books
LIS
-UKEIG
-NPRIE

I want the output to be:

<ol>
<li>News
<ol>
<li>Government Information</li>
<li>BBC></li>
<li>National Press</li>
</ol>
</li>
<li>Books</li>
<li>LIS
<ol>
<li>UKEIG</li>
<li>NPRIE</li>
</ol>
</li>
</ol>
mysql_query()/mysql_fetch_assoc() etc. could be in the flavour of your
choice, childof is assumed to be NULL in rootnodes:

function treelist($parent = 0,$level = 0){
//failsafe:
if($level 30){
trigger_error('function treelist(): nesting too deep ( 30), self
referencing item?', E_USER_ERROR);
exit;
}
$where = ($id == 0) ? ' IS NULL ': ' id = '.intval($parent);
$res = mysql_query('SELECT id,foldername,childof FROM table WHERE
childof '.$where);
if(!mysql_num_rows($res)) return '';
$return = "\n".str_repeat(' ',$level).'<ol>';
while($row = mysql_fetch_assoc($res)){
$return .= "\n".str_repeat('
',$level).'<li>'.$row['foldername'].treelist($row['id'],$level+1).'</li>';
}
$return .= "\n</ol>\n";
if($level 1) $return .= str_repeat(' ',$level-1);
return $return;
}
--
Rik Wasmus
Jun 6 '07 #3

P: n/a

"amygdala" <no*****@noreply.comschreef in bericht
news:46***********************@news.kpnplanet.nl.. .
>
"Steve" <St***********@gmail.comschreef in bericht
news:11**********************@p77g2000hsh.googlegr oups.com...
>>I have been studying the Adjacency List Model as a means of achieving
a folder structure in a project I am working on. Started with the
excellent article by Gijs Van Tulder

http://www.sitepoint.com/article/hie...-data-database

My database has this basic structure:

Id
FolderName
ChildOf

where ChildOf is the Id of the Folder that the current folder is
nested within.

Although I can use this to make indented text lists - I want the
output to be a hierarchical HTML structured list using <ol><li>
structures.

So, for example, my folders are:

News
-Government Information
-BBC
-National Press
Books
LIS
-UKEIG
-NPRIE

I want the output to be:

<ol>
<li>News
<ol>
<li>Government Information</li>
<li>BBC></li>
<li>National Press</li>
</ol>
</li>
<li>Books</li>
<li>LIS
<ol>
<li>UKEIG</li>
<li>NPRIE</li>
</ol>
</li>
</ol>

and for this to work irrespective of the number of levels in the
hierarchy.

Any ideas?

Recursion my friend. I've done something simular recently, but a bit
verbose; in every recursion I used an SQL query.

However, yesterday I stumbled across an example that does the job far more
efficient:

http://www.tagarga.com/blok/on/061029

Courtesy of newsgroup contributor gosha bine.

HTH
Forget what I said, although useful, not exactly what you asked for of
course. Rik's example was more on point.
Jun 6 '07 #4

P: n/a
On 6 Jun, 18:07, Rik <luiheidsgoe...@hotmail.comwrote:
>
mysql_query()/mysql_fetch_assoc() etc. could be in the flavour of your
choice, childof is assumed to be NULL in rootnodes:

function treelist($parent = 0,$level = 0){
//failsafe:
if($level 30){
trigger_error('function treelist(): nesting too deep ( 30), self
referencing item?', E_USER_ERROR);
exit;
}
$where = ($id == 0) ? ' IS NULL ': ' id = '.intval($parent);
$res = mysql_query('SELECT id,foldername,childof FROM table WHERE
childof '.$where);
if(!mysql_num_rows($res)) return '';
$return = "\n".str_repeat(' ',$level).'<ol>';
while($row = mysql_fetch_assoc($res)){
$return .= "\n".str_repeat('
',$level).'<li>'.$row['foldername'].treelist($row['id'],$level+1).'</li>';
}
$return .= "\n</ol>\n";
if($level 1) $return .= str_repeat(' ',$level-1);
return $return;}
Hi thanks for this. I don't seem to be able to make it work. I have
made the root nodes in the database have a NULL ChildOf but each time
I get the "nesting too deep ( 30), self referencing item" error. Hi
have checked and double checked - and there are no self-referencing
items.

I've tried to debug by checked what $where contains and on each
iteration it just contains IS NULL. What do the ($id==0) and the
'id=" . intval($parent) parts do? There doesn't seem to be an $id
variable declared or set anywhere in the php. I tried changing the
$id==0 to $parent==0 and this produces only the top level list. If I
specify a different "level" this only has the effect of inserting gaps
between each item but it still only returns the top level items.

What I am hoping to achieve is the ability to display the fully
expanded hierarchy.

Any further thoughts???

Best regards, and many thanks,

Steve

Jun 6 '07 #5

P: n/a
On 6 Jun, 17:54, "amygdala" <nore...@noreply.comwrote:
Recursion my friend. I've done something simular recently, but a bit
verbose; in every recursion I used an SQL query.

However, yesterday I stumbled across an example that does the job far more
efficient:

http://www.tagarga.com/blok/on/061029
Hi there. Thanks for the suggestion - but I am already able to produce
the fully expanded and indented list. What I want to do is convert
this into a HTML compliant hierarchical list using nested <oland
<litags.

Best regards
Steve

Jun 6 '07 #6

P: n/a
Rik
On Wed, 06 Jun 2007 19:07:30 +0200, Rik <lu************@hotmail.comwrote:
$where = ($id == 0) ? ' IS NULL ': ' id = '.intval($parent);
Obviously:
$where = ($id == 0) ? ' IS NULL ': ' = '.intval($parent);
--
Rik Wasmus
Jun 6 '07 #7

P: n/a
Rik
On Wed, 06 Jun 2007 19:47:42 +0200, Rik <lu************@hotmail.comwrote:
On Wed, 06 Jun 2007 19:07:30 +0200, Rik <lu************@hotmail.com
wrote:
> $where = ($id == 0) ? ' IS NULL ': ' id = '.intval($parent);

Obviously:
$where = ($id == 0) ? ' IS NULL ': ' = '.intval($parent);
DOH!!!!
Obviously:
$where = ($parent == 0) ? ' IS NULL ': ' = '.intval($parent);

And use it without arguments to get the tree from the root:

treelist();

Only give arguments (the first being the id which you'd like to expand) if
you're only interested in a sub-tree.
--
Rik Wasmus
Jun 6 '07 #8

P: n/a
Rik
On Wed, 06 Jun 2007 19:37:41 +0200, Steve <St***********@gmail.comwrote:
I've tried to debug by checked what $where contains and on each
iteration it just contains IS NULL.
Yup, little bug in the code (haven't tested it, almost never do for
newsposts :P ), should be solved now.
--
Rik Wasmus
Jun 6 '07 #9

P: n/a
On 6 Jun, 18:54, Rik <luiheidsgoe...@hotmail.comwrote:
Yup, little bug in the code (haven't tested it, almost never do for
newsposts :P ), should be solved now.
--
Rik Wasmus
Hey thanks very much. Works a treat!!!

You're a star.

Steve

Jun 6 '07 #10

P: n/a
On 06.06.2007 15:11 Steve wrote:
I have been studying the Adjacency List Model as a means of achieving
a folder structure in a project I am working on. Started with the
excellent article by Gijs Van Tulder

http://www.sitepoint.com/article/hie...-data-database

[snip]
Any ideas?
Doesn't this article also explain why you should NOT use adjacency
lists? With recursive queries you're doing N selects to fetch N
elements, that makes using database essentially pointless. From my
experience, ALs are only suitable for very small sets, which can be read
once completely and then processed in memory.
--
gosha bine

extended php parser ~ http://code.google.com/p/pihipi
blok ~ http://www.tagarga.com/blok
Jun 6 '07 #11

P: n/a
On 6 Jun, 19:16, gosha bine <stereof...@gmail.comwrote:
Doesn't this article also explain why you should NOT use adjacency
lists? With recursive queries you're doing N selects to fetch N
elements, that makes using database essentially pointless. From my
experience, ALs are only suitable for very small sets, which can be read
once completely and then processed in memory.
Hi there. Yes it does - and recommends the "Modified Preorder Tree
Traversal" method.

I couldn't get this to work when the tree has several starting nodes
as mine does.

Would welcome any hints about finding a solution to this and producing
a HTML structured list from the results.

Best regards.
Steve

Jun 6 '07 #12

P: n/a
Rik
On Wed, 06 Jun 2007 20:59:03 +0200, Steve <St***********@gmail.comwrote:
On 6 Jun, 19:16, gosha bine <stereof...@gmail.comwrote:
>Doesn't this article also explain why you should NOT use adjacency
lists? With recursive queries you're doing N selects to fetch N
elements, that makes using database essentially pointless. From my
experience, ALs are only suitable for very small sets, which can be read
once completely and then processed in memory.
Well, updating serveral hundreds of thousands of left & right values also
puts some strain on the database. I would not like to use a Nested Set for
a regularly changing gigantic tree.
Hi there. Yes it does - and recommends the "Modified Preorder Tree
Traversal" method.

I couldn't get this to work when the tree has several starting nodes
as mine does.
What problem are you facing then? Several startnodes are absolutely no
problem at all....
Would welcome any hints about finding a solution to this and producing
a HTML structured list from the results.
It might look something like this, not thoroughly tested though...

function treelist_nested_set($id = 0,$limit = -1){
$strLimit = ($limit -1) ? " HAVING depth <= ".intval($limit): '';
if($id==0){
$qry = "SELECT node.id,node.name, (COUNT(parent.name) - 1) AS depth
FROM `table` AS node,
`table` AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.id
{$strLimit}
ORDER BY node.lft";
} else {
$qry = "SELECT node.id,node.name, (COUNT(parent.name) -
(sub_tree.depth + 1)) AS depth
FROM `table` AS node,
`table` AS parent,
`table` AS sub_parent,
(
SELECT node.id,node.name, (COUNT(parent.name) - 1) AS depth
FROM `table` AS node,
`table` AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.id = {$id}
GROUP BY node.id
ORDER BY node.lft
)AS sub_tree
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.lft BETWEEN sub_parent.lft AND sub_parent.rgt
AND sub_parent.id = sub_tree.id
GROUP BY node.id
{$strLimit}
ORDER BY node.lft";
}
$res = mysql_query($qry);
if(!mysql_num_rows($res))
trigger_error('Could not build tree');
return '';
}
$depth = -1;
$return = '';
while($row = mysql_fetch_assoc($res)){
if($row['depth'] < $depth) $return .=
str_repeat("\n</ol>\n</li>",$depth - $row['depth']);
if($row['depth'] $depth) $return .=
str_repeat("\n<ol>\n",$row['depth'] - $depth);
if($row['depth'] == $depth) $return .= '</li>';
$depth = $row['depth'];
$return .= "\n<li>".$row['name'];
}
$return .= str_repeat("\n</ol>\n</li>",$depth)."\n</ol>";
return $return;
}
--
Rik Wasmus
Jun 6 '07 #13

This discussion thread is closed

Replies have been disabled for this discussion.