heiracial site map

[ceaseanddesist]

i have a website that has main sections and subsections and i want to create
a hieracial site map e.g.

main
sub section 1
subsection 1 page1
subsection 1 page 2
sub section 2
subsection 2 page1
subsection 2 page 2

in my database i have 3 tables .. 1st is called tablemain which contains 2
fields the section name and the section id.. 2nd table is called tablesub
which which also contains 2 fields a section name and id .. the 2rd table is
called pages and contains 3 fields a maincatid a subcatid and pagecontent
field.

my question is how would i query these tables and loop through to create a
hieracial structure for a site map

any help / suggestions with the logic of it much appreciated as at a dead
end

[darrel]

> main

> sub section 1
> subsection 1 page1
> subsection 1 page 2
> sub section 2
> subsection 2 page1
> subsection 2 page 2
>
> in my database i have 3 tables .. 1st is called tablemain which contains 2
> fields the section name and the section id.. 2nd table is called tablesub
> which which also contains 2 fields a section name and id .. the 2rd table

is

> called pages and contains 3 fields a maincatid a subcatid and pagecontent
> field.

I do this but just use one table:

itemName itemID itemParent
main 1 0
subsection1 2 1
subsection1page1 3 2
subsection1page2 4 2
subsection2 5 1
etc.

Then, I do a recursive query:

makeMenu(0)

function makeMenu(parentID as int)
querydatabase to grab all items with a itemparent = parentID
for each record
makeMenu(theRecord's Item ID)
end
end function

This works great. The drawback is that it's recursive. So, on a large site,
this could take up a lot of processing power.

So, what i do is use this recursive call to create an XML file. I then load
the XML file to create the site map. Since the menu isn't changed that
often, it's not a big deal to call the function at that time...as opposed to
having to call it each and every time the site map page is loaded.

-Darrel

[ceaseanddesist]

still a little lost any chance u could send some sample code would be much
appreciated toocanx at hotmail dot com

appreciated


"darrel" <notreal@hotmail.com> wrote in message
news:d6t3em$hpp$1@forums.macromedia.com...

>> main
>> sub section 1
>> subsection 1 page1
>> subsection 1 page 2
>> sub section 2
>> subsection 2 page1
>> subsection 2 page 2
>>
>> in my database i have 3 tables .. 1st is called tablemain which contains
>> 2
>> fields the section name and the section id.. 2nd table is called tablesub
>> which which also contains 2 fields a section name and id .. the 2rd table

> is

>> called pages and contains 3 fields a maincatid a subcatid and pagecontent
>> field.

>
> I do this but just use one table:
>
> itemName itemID itemParent
> main 1 0
> subsection1 2 1
> subsection1page1 3 2
> subsection1page2 4 2
> subsection2 5 1
> etc.
>
> Then, I do a recursive query:
>
> makeMenu(0)
>
> function makeMenu(parentID as int)
> querydatabase to grab all items with a itemparent = parentID
> for each record
> makeMenu(theRecord's Item ID)
> end
> end function
>
> This works great. The drawback is that it's recursive. So, on a large
> site,
> this could take up a lot of processing power.
>
> So, what i do is use this recursive call to create an XML file. I then
> load
> the XML file to create the site map. Since the menu isn't changed that
> often, it's not a big deal to call the function at that time...as opposed
> to
> having to call it each and every time the site map page is loaded.
>
> -Darrel
>
>

[darrel]

> still a little lost any chance u could send some sample code would be much

> appreciated toocanx at hotmail dot com

Here's a trimmed down example of the functions I use. This is asp.net. Note
the URLs in the comments that will take you to more detailed tutorials.

-Darrel



Private Sub Button_Save_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button_Save.Click
if page.IsValid then
updateDatabase() 'this updates the database with all the new data
menuWriter.createXMLfileWithXmlWriter(0,0,0,True,v iewstate("district"))
'this creates the XML file
end if
End Sub


Public Class menuWriter

shared objXMLWriter As system.xml.XmlTextWriter

shared Sub createXMLfileWithXmlWriter(parentNode as integer, intLevel as
integer, rowCount as integer, createParentNode as boolean, district as
integer)

'retrieve all children of parentNode
try

' grab the data from the db
Dim DS As New DataSet
Dim strConnect As String
strConnect =
System.Configuration.ConfigurationSettings.AppSett ings("DBConn")
Dim strChk As String
strChk = "SELECT pageID, pageName, pageTitle, " & _
"FROM yourTable " & _
"WHERE ParentID = " & parentNode & " " & _
"ORDER BY sort"
Dim objConnect As New System.Data.OleDb.OleDbConnection(strConnect)
objConnect.Open()
Dim objCommand As New System.Data.OleDb.OleDbCommand(strChk, objConnect)
Dim objOleDbAdapter As New System.Data.OleDb.OleDbDataAdapter(strChk,
strConnect)
objOleDbAdapter.Fill(DS, "webPages")

' Create the parent node
'dim objXMLWriter As system.xml.XmlTextWriter
if createParentNode = true then

' xml writing adapted from
' [url]http://www.developerfusion.com/show/2646/[/url]
objXMLWriter = New
system.xml.XmlTextWriter(System.Web.HttpContext.Cu rrent.Server.MapPath("/dis
tricts/" & district & "/xml/siteMenu.xml").tostring, nothing)
objXMLWriter.Formatting = objXMLWriter.Formatting.Indented
objXMLWriter.Indentation = 3
objXMLWriter.WriteStartDocument()
objXMLWriter.WriteComment("Created on " & Now())
objXMLWriter.WriteStartElement("menuItems")
createParentNode = false
end if

' Walk through results whether it's a list or a single contact.
' based off of this tutorial:
' [url]http://www.wwwcoder.com/main/parentid/154/site/899/68/default.aspx[/url]
if not ds.Tables(0).rows.Count = 0 then 'ie, if there IS data, then...
while rowCount < ds.Tables(0).rows.Count
objXMLWriter.WriteStartElement("menuItem")
objXMLWriter.WriteElementString("pageID",
trim(ds.Tables(0).Rows(rowCount)("pageID").ToStrin g))
objXMLWriter.WriteElementString("pageName",
trim(ds.Tables(0).Rows(rowCount)("pageName").ToStr ing))
objXMLWriter.WriteElementString("pageTitle",
trim(ds.Tables(0).Rows(rowCount)("pageTitle").ToSt ring))

'now call the subroutine we're in to see if this value has
'any children and increase the indent, and so on...
createXMLfileWithXmlWriter(ds.Tables(0).Rows(rowCo unt).Item(0),
intLevel + 1, 0, false, district)
rowCount = rowCount + 1
objXMLWriter.WriteEndElement() 'close menuItem node
end while

'close parent node

if intLevel = 0 then
objXMLWriter.WriteEndElement() 'close menuItems node
objXMLWriter.Flush()
objXMLWriter.Close()
Dim strXMLResult As String

'show xml as html
Dim objSR As system.IO.StreamReader =
system.io.File.OpenText(System.Web.HttpContext.Cur rent.Server.MapPath("/your
pathtoyourfile/xml/siteMenu.xml"))
strXMLResult = objSR.ReadToEnd()
objSR.Close
objSR = Nothing
'label1.Text = "<pre>" & Server.HtmlEncode(strXMLResult) & "<pre>"

end if
else
end if


' clean up

objConnect.Close()
objOleDbAdapter.Dispose()
objCommand.Dispose()


catch ex as Exception
System.Web.HttpContext.Current.response.write ("Error writing XML
file<br />")
System.Web.HttpContext.Current.response.Write (ex.Message.tostring)
end try


End Sub

End Class