[RESOLVED] ASP to output Nav menu with submenus from SQL

[jamesbcox1980]

Hi,

I have an augmented SQL output of:


id url text title current parent
-----------------------------------------------------------------------
0 / Home CSM Welcome Screen true 0
1 /calls Calls Customer Call Input false 1
2 /cases Cases Case Management false 2
3 /rgas RGAs RGA Management false 3
4 /reports Reports CSM Reporting false 4
5 /admin Admin Administration Area false 5
6 /calls/new New Call New Call Sheet false 1
7 /calls/edit Edit Call Edit Call Sheet false 1


I need to use VBScript to format this data and use it to output a Nav menu on my web page template. I have some simple script to do this from a multidimensional array, but it doesn't include the **sub-menu**.

How do I take this output from the SQL query and format it to HTML? Notice the column "current". This is a variable which is part of a statement that uses the current page id to determine whether or not a link goes to the current page. So this isn't actually pulled from SQL, but calculated during output.

So, assuming the current page id="0" (home), I need the above SQL data to be formatted into the following HTML:

<ul>
<li><a class="current" href="/" title="CSM Welcome Screen">Home</a></li>
<li><a href="/calls" title="Customer Call Input">Calls</a></li>
<li><a href="/Cases" title="Case Management">Cases</a>
<ul class="nav-submenu">
<li><a href="/calls/new">New Call</a></li>
<li><a href="/calls/edit">Edit Call</a></li>
</ul>
</li>
<li><a href="/rgas" title="RGA Management">RGAs</a></li>
<li><a href="/reports" title="CSM Reporting">Reports</a></li>
<li><a href="/admin" title="Administration Area">Admin</a></li>
</ul>


I could probably do this 100 ways from Sunday in PHP, but I'm totally lost in VBScript ASP. Here's what I was doing when I was just using a simple array in the config file and didn't have a sub navmenu (but now I want to use SQL):


<%
DIM company_name, page_title, header_nav_links(5)

company_name = "My Company"
page_title = "Customer Management System"

header_nav_links(0) = Array(0, "/", "Home", "CSM Welcome Screen", True, 0)
header_nav_links(1) = Array(1, "/calls", "Calls", "Customer Call Input", False, 1)
header_nav_links(2) = Array(2, "/cases", "Cases", "Case Management", False, 2)
header_nav_links(3) = Array(3, "/rgas", "RGAs", "RGA Management", False, 3)
header_nav_links(4) = Array(4, "/reports", "Reports", "CSM Reporting", False, 4)
header_nav_links(5) = Array(5, "/admin", "Admin", "Administration Area", False, 5)

%>
<ul>
<%
For Each i in header_nav_links
Response.write(" <li><a ")
If i(3) = 1 Then
Response.write("class=""current"" ")
End If
Response.write("href=""" & i(0) & """ title = """ & i(2) & """>" & i(1) & "</a>")
'#### code for sub-nave menu will go here ####
Response.write("</li>" & vbCr)
Next
%>
</ul>


I'm not so much looking for someone to program this for me, as for someone to guide me in the right direction on this for loop... I don't even know how to create a function in VBScript yet... haha:p Thanks!

[yamaharuss]

How do you expect your data "New Call" and "Edit Call" to be displayed as a submenu under "Cases"?? There is absolutely nothing about your data structure that ties those two subs to the "Cases" parent. If their Parent IDs were 2 instead of 1 it would make sense.

[jamesbcox1980]

Excuse me that was a mistype in the HTML, they were suppose to go under "calls". Thanks for catching that.

[jamesbcox1980]

Ok, so I figured this out after working it out in PHP first. Doing this in PHP was much easier, and I had to cheapen it to do it in VBScript with my limited knowlege. What I had to do was create a function that had an SQL query that returned the parent links, and then during the While loop to output them, I made another SQL query to check for child links for each parent.

Function DisplayMenu(sMenu_id, iCurrPage, sSpacing)
Dim oMainLinks, oSubLinks, sLinksMenu
Set oMainLinks = SelectDB("SELECT ('<a href=""' + href + '"" title=""' + title + '""' + ' class=""nobr' + (CASE WHEN link_id = " & iCurrPage & " THEN ' current' ELSE '' END) + '"">' + link_text + '</a>') AS links_items, link_id FROM links WHERE parent_id = 0 AND menu_id = " & sMenu_id & " ORDER BY sort_order")

sLinksMenu = ""
If Not oMainLinks.EOF Then
sLinksMenu = sLinksMenu & sSpacing & "<ul>" & vbCrlf

While Not oMainLinks.EOF
sLinksMenu = sLinksMenu & sSpacing & " <li>" & oMainLinks("links_items")
Set oSubLinks = SelectDB("SELECT ('<li><a href=""' + href + '"" title=""' + title + '"" class=""nobr"">' + link_text + '</a></li>') AS links_items, parent_id FROM links WHERE parent_id = " & oMainLinks("link_id") & " AND menu_id = " & menu_id & " ORDER BY sort_order")

If Not oSubLinks.EOF Then
sLinksMenu = sLinksMenu & vbCrlf & sSpacing & " <ul class=""nav-submenu"">" & vbCrlf
While Not oSubLinks.EOF
sLinksMenu = sLinksMenu & sSpacing & " " & oSubLinks("links_items") & vbCrlf
oSubLinks.MoveNext
WEnd
sLinksMenu = sLinksMenu & sSpacing & " </ul>" & vbCrlf & sSpacing & " "
End IF
sLinksMenu = sLinksMenu & "</li>" & vbCrlf
Set oSubLinks = Nothing
oMainLinks.MoveNext
WEnd
sLinksMenu = sLinksMenu & sSpacing & "</ul>" & vbCrlf
End If
DisplayMenu = sLinksMenu
End Function