treeview without using ActiveX

[Aaron Coombs]

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

[Biswa]

Hi

First I need to know whether you are having or can have the data in XML -
somewhere inbetween. What I mean to say is, if U have a recordset, can your
system support something like this :

Recordset (at Server-side) --> XML (at Server-side) --> [Black Box] (at Server-side)-->
DHTML Tree at client side with supporting JavaScript.

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
>
>

[James Barbetti]

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):

x--- start of code snippet ---x

Public Sub DumpListHTML(byval Conn, byval tableName _
, byval IDColumn , byval ParentColumn _
, byval nameColumn, byval topID, byval indent _
, byval extraSQL, byval parentIDList)
dim sql
Dim RS 'recordset
Dim dataArray 'holds result
Dim r 'row number

sql = "select " & IDColumn & ", " & parentColumn & ", " & nameColumn
sql = sql & " From " & tableName & " Where ("

if topID=0 Then sql = sql & "(" & parentColumn & " is NULL OR "
if not isnull(topID) Then
sql = sql & parentColumn & "=" & topID
else
sql = sql & parentColumn & " is NULL"
end if
if topID=0 then sql = sql & ")"
'Note#1

if parentIDList>"" Then
sql = sql & " And not " & IDColumn & " IN (" _
& mid(parentIDList,2) & ")" 'Note#2
End If
sql = sql & ")"
sql = sql & " " & extraSQL 'Note#3
Set RS = Server.CreateObject("ADODB.Recordset")
Response.Write "<BR><FONT SIZE=-2>" & server.htmlencode(sql) _
& "</FONT><BR>" 'Note#4

RS.Open sql, Conn
If not RS.EOF Then dataArray = RS.GetRows(-1) 'Note#5
RS.Close
Set RS=Nothing
If not isEmpty(dataArray) Then 'Note#6
Response.write space(indent) & "<BR><OL>" & vbCrlf
For r=0 to ubound(dataArray,2)
Response.Write space(indent+2) & "<LI>"
Response.write dataArray(2,r) & vbCrlf 'Name
'ID is in dataArray(0,r), parentID is in dataArray(1,r)
DumpListHTML Conn, tableName, idColumn, parentColumn _
, nameColumn, dataArray(0,r), indent+4, extraSQL _
, parentIDList & "," & dataArray(0,r)
'Note#7
Response.Write "</LI>" & vbCrlf
Next
Response.write space(indent) & "</OL>" & vbCrlf
End If
End Sub

'
'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.
'

Public Sub DumpWebPages
DumpListHTML DBConn, "Page", "PageID", "FK_ParentPageID" _
, "PageName", 0, 0, " order by pageName", ""
End Sub

x--- end of code snippet ---x

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.

Hope this helps.
Seeya,
James