Newbie need help with tree menu 2

[Zoom1177]

Hi all

I want to build a tree menu in my asp driven from a table, I looked at some scripts and I managed to get the parent nodes to work but I am failing to get the childs to work at all especially the nested repeat regions …I am working with Dreamweaver as my editor and it doesn’t support nested repeat region.

This is a sample o my table in SQL Server

Building Types 01000000
education 01010000
healthcare 01020000
industrial 01030000
office building 01040000
Specialized Exp 02000000
substructure 02010000
foundations 02020000
Process Exp 03000000
pro exe 03010000
superstruc 03020000
Product Exp 04000000
shell1 04010000
interiors2 04020000
Construction Sys 05000000
shell 05010000
interiors 05020000

And here is a picture how I want them sort in a tree node.

http://usera.imagecave.com/me72/categories.JPG

I had a hard time writing a T-SQL to pull them in categories so I thought just pull the whole table in one record set and filter it into separate record sets on the ASP

But then I got the problem of putting those separate categories in separate nodes on the tree and repeat region … I mean the UL/IL

I am lost here and any help will be greatly appreciated
Thanks

 

[Sheco]

It might be easier if your table looked like this:
[tt]
Item Name ID ParentID
Building Types 1 1
education 2 1
healthcare 3 1
industrial 4 1
office building 5 1
Specialized Exp 6 6
substructure 7 6
foundations 8 6
Process Exp 9 9
pro exe 10 9
superstruc 11 9
Product Exp 12 12
shell1 13 12
interiors2 14 12
Construction Sys 15 15
shell 16 15
interiors 17 15
[/tt]

 

[Zoom1177]

Good Idea
I'll try it

Thanks

 

[Tarwn]

It probably won't be much easier to change to that method, as your SQL or query logic will then need to get much more complicated to handle a (possibly) unknown number of levels, etc. While we could probably make the assumption that there are, at maximum, four levels, due to the length of the strings, this would still be difficult to pull out in a single query, in order.

I can think of a couple methods of handling the original data, but some of them would take a little while to explain. Perhaps the easiest method would be to accept that only 4 levels are possible and declare 4 variables to keep track of what level you last operated on. This would allow you to open and close new lists on each level as you loop through the data.
Basically you have 4 levels, each of them 2 digits. It might be easier to operate on if you had used a delimiter between each set, like a ".", but we can work around that.

Each time we start processing a new record we want to determine the highest level that has changed since the previous value. We then open lists based on how many sub-levels have a value other than "00". Hopefully this will only ever be a single sub-level or we will end up with a subtree with no label. So if our previous record was 01 00 00 00 and our current one is 01 01 00 00, then we see that the levels are the same in the first element, then the next one changes from 00 to 01, so we start a new ul element. We assume, at this point, that the tree is well-made and any necessary closures were handled by the code coming later in the loop.

We then output the value for the record with the beginning of an list item tag.

The final step in the loop is to set the previous values to the current values, advance to the next record and determine how many things need to be closed.

To determine how many li's and ul's to close, we need to compare the previous levels to the new current ones. Starting at the highest level, we look to find the first level that changes. Once we find the level that changed we have a few options:
1) If the level that changed is lower (ie, 01010000 vs 01010100) then we need to output a ul to start a sublist
2) If the level that changed is the same (ie, 01010000 vs 01020000) then we just need to end the last li tag
3) If the level that changed is a higher level(ie, 01010000 vs 02000000) then we assume that the previous value was in a sub-list and end that ul and li.

Unfortunately I am on my way to a mandatory work dinner, or I would be able to provide an example to better explain what I meant.

Another way to look at this is to add a ul in the top part of a loop when a 00 turns into a 01, and end a ul and li pair in the bottom part of the loop every time any level goes from a value to a 00, and end an li anytime the lowest value simply increments by 1 (as long as it isn't 00 to 01).

-T

 

[Zoom1177]

Tarwn,

Thank you very very much. That's exactly what i was looking for. I posted this in SQL forum and no one could've exactly give me what i wanted, it was my mistake because i couldn't explain what i want besides there is no way T-SQL can give me record sets of my table to fit exactly all the UL's and IL's that need...

And you are right, they are 4 levels on the table (8 characters...2 digits each level)

Thank you really for this valuable post... i will try it tomorrow as i am home now, of course i would greatly appreciated if you could provide an example.

Thanks again!!

 

[mbiro]

SQL Server does have a @@nestlevel variable that can be used in conjunction with a stored procedure that calls itself recursively. I believe the limit is 32 levels. While the example above does accomplish your goal, it requires you to have a known limit of levels. I have written a stored procedure that works off a table like Sheco detailed above. Let me know if you want it.

 

[Zoom1177]

mbiro,

That would be great. Yes i would like to see it please

Thanks!

 

[mbiro]

Now, this example allows for a defined start point and therefore, you can have multiple tree maps in one table. The @account variable is the start record (home page). The @pgid, also the start record (home page), and @first are to keep track of recursive calls.

Using Sheco's table above, you would call it in Query Analyzer like pr_get_sitemap 1,1,true. Remember, 1 is the start page. As you may have noticed, I use this to build a sitemap for a site contained in a DB.

______________________________________________________

CREATE PROCEDURE dbo.pr_get_sitemap

@account int,
@pgid int,
@first bit

AS

DECLARE @errorID int

BEGIN
SET NOCOUNT ON
DECLARE @ModID int,@top int, @parentid int, @ModName varchar(100),@id int, @errorcode int, @tempid int, @id1 int, @id2 int, @pgid1 int, @newparent_tempid int
if @first = 1
BEGIN
create table #subs (id int identity,levels int, heading_id int,heading_desc varchar(100),parent_id int)
create table #heads (tempid int identity,heading_id int, heading_desc varchar(255) ,parent_id int, parent_tempid int)
insert into #heads (heading_id, heading_desc,parent_id) select heading_id,heading_desc,parent_id from tblheadings where status_id = 1 order by parent_id,sort_order,heading_desc
Select @top = max(tempid) From #heads
set @id = 1
set @errorcode = 0
while (@id <= @top) AND (@errorCode = 0)
begin
set @id1 = (select parent_id from #heads where tempid = @id)
set @id2 = (select tempid from #heads where heading_id = @id1)
BEGIN TRANSACTION
update #heads
set parent_tempid = @id2
where tempid = @id
SET @errorCode = @@ERROR
IF @errorCode = 0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION

set @id = @id +1
end
set @newparent_tempid = (SELECT tempid from #heads where heading_id = @account)
set @pgid = (SELECT tempid from #heads where heading_id = @pgid)
END

Set @ModName = (Select Heading_Desc FROM #heads Where tempid = @pgid)
Set @parentid = (Select parent_tempid FROM #heads Where tempid = @pgid)
Set @ModID = (Select MIN(tempid) FROM #heads WHERE Parent_tempId = @pgid and tempid>@pgid)
set @pgid1 = (select heading_id from #heads where tempid = @pgid)
BEGIN TRANSACTION
Insert into #subs (levels,heading_id,heading_desc,parent_id) values (@@nestlevel,@pgid1,@ModName,@parentiD)
SET @errorID = @@ERROR
IF @errorID = 0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION

While @ModID IS NOT NULL
BEGIN
exec dbo.pr_get_sitemap @account,@ModID,0
Set @ModID = (Select min(tempid) from #heads where parent_tempid = @pgid and tempid > @ModId)
END
--if @pgid = (Select max(tempid) from #heads where parent_tempid = @newparent_tempid and tempid > @newparent_tempid)
if @pgid = @newparent_tempid
BEGIN
select * from #subs
END

END



GO
__________________________________________


I loop through the recordset using the level to determine my layout. In my example, I have specific design for the first four layers and then a standard for levels beyond that. Here is my code, it is vb.net, but close enough to ASP for use as an example, I hope.

While dr7.Read
For A = 0 To dr7("levels")
htmlStringContent.Append("&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;")
Next
htmlStringContent.Append("<a href=""index.aspx?pgid=" + CStr((dr7("heading_id"))))
If dr7("levels") = 1 Then
htmlStringContent.Append("""><B>" + (dr7("heading_desc")) + " Library Home Page</B>")
ElseIf dr7("levels") = 2 Then
htmlStringContent.Append("""><B>" + (dr7("heading_desc")) + "</B>")
ElseIf dr7("levels") = 3 Then
htmlStringContent.Append("""><U>" + (dr7("heading_desc")) + "</U>")
ElseIf dr7("levels") = 4 Then
htmlStringContent.Append(""">&mdash; " + (dr7("heading_desc")))
ElseIf dr7("levels") = 5 Then
htmlStringContent.Append(""">&middot; " + (dr7("heading_desc")))
ElseIf dr7("levels") > 5 Then
htmlStringContent.Append(""">- " + (dr7("heading_desc")))
End If
htmlStringContent.Append("</a><BR>")
End While

 

[Zoom1177]

mbiro,

Great!

I'll try it right now. However, i wont be using Sheco's table so i'll do some changes to it.

thank you very much for your valuable post!!

I still like to see Tarwn's example as it return the whole table as is to the asp and sort the menus by categories there...I am not sure which one would be faster though, your's or his...But thanks guys for your help. I really appreciate it.