If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

If you can have an intermediate XML, I have a server-side "Black Box" which
has the capability of creatng a dynamic HTML tree of infinite levels of hierarchy,
simply depending on the XML levels of hierarchy.

The Black-Box is nothing but a COM DLL written in VB, which parses the XML
and creates an Tree-like HTML code which is aware of JavaScript events. The
complimentary event-handler JS file is also written by me.

HTH :-)

Biswa.

"Aaron Coombs" <a.coombs@home.com> wrote:
>Hi,
>
>I am trying to display records from a database in the form of a tree. The
>Parent must have unlimitted children and the childrens, children must also
>have unlimitted children.
>
>The table looks like this.
>
>ID (Autonumber - unique)
>Name (text)
>ParentID(Number) // if this number is a 0 then it is a parent record
>
>some records:
>ID NAME ParentID
> 1 Parent 0
> 2 1st child 1
> 3 1st childs child 2
> 4 1st childs 2nd child 2
>
>
>What is the best way to display this records in a treeview look without
>using a control.
><ol>
> <li>Parent</li>
> <ol>
> <li>child</li>
> </ol>
></ol>
>The loop could be endless.
>
>Thanks,
>
>Aaron
>
>

Re: treeview without using ActiveX

Hi Aaron, Biswa

Getting the XML is about as tricky as rendering tree-structure HTML.
The problem is fetching the actual records to display.
>>Aaron said:
>>I am trying to display records from a database in the form of a tree.
The
>>Parent must have unlimitted children and the childrens, children must also
>>have unlimitted children.
>>The table looks like this.
>>
>>ID (Autonumber - unique)
>>Name (text)
>>ParentID(Number) // if this number is a 0 then it is a [root] record

Good.
I'm not sure what language Aaron is working in.
But in VBScript/ASP the simplest workable approach
is roughly as follows (assuming the ID columns, etc are
numbers):

'
'Notes
'1... Others may have parentID of NULL to indicate
' root nodes. I've allowed for this here.
'2... This rather weird bit is intended to filter out
' the IDs of any nodes that have already appeared
' as parents. This means that Aaron's worry about
' "endless loops" isn't a problem. Though strictly
' you'd want to trap these and report them, it's
' easier to "sweep them under the carpet", as I've
' done here.
'3... I've allowed an ExtraSQL parameter in case there
' are additional WHERE conditions (which you'd
' supply thus: extraSQL=" AND fieldX=valueY ..."
' or if you want the children below a given node
' in a particular order, " ORDER BY thingyName",
' for example).
'4... This line dumps the SQL used to find the children
' for each node, so you can see how the routine
' does its stuff. The SQL's the important bit!
'5... Another weird decision: we ensure that we only
' ever have one recordset open at a time, by
' fetching back all the data (via getRows) and
' *closing* the recordset at the current level
' before descending (via recursion).
' Without this it is easy to run out of recordsets,
' if you have a deep tree structure.
' Using getRows also improves performance.
'6... If this hasn't been set by getRows it will be
' Empty. See also note#5.
'7... This bit, along with #2, ensures we don't go
' into an infinite recursive loop and blow our
' stack. Every time we go down we add the ID of
' the node we're searching for to a list of
' "forbidden" nodes we're not allowed to fetch.
' See the code for #2, which tells the SQL to
' ignore these nodes.

'
'.... An example call ....
' to dump a Page table in one of my web applications.
'

Note that everything is done with response.write, NOT string concatentation.
String concatenation would blow out execution time by a factor of at least
2,
and possibly by *hundreds*, in a big tree.

The above will work regardless of how deep the tree gets, and regardless
of
how many children there are for each node. But it won't perform.
The problem is that a SQL query has to be thrown to fetch the children
for each individual record in the tree. If there are 200 records, that's
200
queries.

It *is* possible to cut that back somewhat, but you need *much* fancier
logic to do so. The first trick cuts back the query count to
[maximum depth of tree + 1]. Basically instead of searching on
parentID=x you search on ParentID In (x,y,z,...) where x,y,z... were the
IDs at the next level up in the tree. But then you have to sort the results
of the query according to parent IDs. It's rather complex.
But in typical real-world situations it improves performance about 10-fold.

If you're feeling *really* ambitious you can write a single-query version,
but then you have to "construct the tree" in VBScript/ASP. Which is a *lot*
of work. A single-query version is blisteringly quick. Usually at least
hundreds of times faster, for a large and deep tree.

But the solution I've provided here will work okay for reasonably large,
reasonably deep trees. Just don't use it if you've got more than about 150
records in the tree. It'll suck.

If anyone needs a higher performance version that will scale for
much bigger trees... (and allows you to display tree-structure info
based on many-to-many as well as one-to-may child-to-parent
relationships) I'd be happy to part with the ASP source code. For a price.