Dynamic menu items from a database

scargin · Post by **scargin** » Mon Jun 09, 2003 2:03 am

Is it possible to load menu Url's and attributes from a database?

I am building a site that will incorporate dynamic news sections and it would be great to include new news items into the menu without having to re-edit the menu.

I am using Access and ASP.

Thanks,

Stuart Cargin

Greg Squire · Post by **Greg Squire** » Mon Jun 09, 2003 6:34 pm

Yes it is.

You could write an ASP page that generates the menu definition file (the JS file that has all of the "addmenu" lines in it). Or more precisely, that ASP file generates the text that would go in that file, but you'd include that ASP into your main ASP file.

However, If that data doesn't change too often it might still be better to have a background program generate a JS file, that is then included in your main page, as this would reduce the load on your app server(s).

Milo · Post by **Milo** » Tue Jun 10, 2003 1:20 pm

I've currently finished an ASP - SQL2000 driven menu. I run it off one table and one query.

The table will need at the minimum: -
ID (INT)
label (char or varchar - the text that appears on the page)
sub (BIT or Boolean - if true then it has submenus)
level (the parent link - for submenus only)
link (char or varchar - what happens when you click!)
seq (INT - to order the menus and submenus)

The SQL or Query for the root menu items: -
SELECT [id],label,[level],sub,link FROM TOC where level=0 order by seq

Then: -
SELECT [id],label,[level],sub, [link] FROM TOC WHERE [level]<>0 order by [level],seq

For the submenus..

Finally the ASP (oMenuRoot is the recordset for the ROOT menus, oMenuSub is the recordset for the SUB menus: -

<%do until oMenuRoot.eof%>
,"<%=oMenuRoot("label")%>","<%
if oMenuRoot("sub") then
response.write "show-menu=" & oMenuRoot("id")
else
response.write Application("homePage") & "?" & oMenuRoot("link")
end if
%>",,"",1<%=vbcrlf%>
<%intRoots=intRoots+1
oMenuRoot.movenext
loop

do until oMenuSub.eof
if intOldTree<>oMenuSub("level") then
blnNewTree=true
response.write "])" & vbcrlf
else
blnNewTree=false
end if
if blnNewTree then
response.write "addmenu(menu=["""
response.write oMenuSub("level")
response.write """," & vbcrlf
response.write ",,120,1,"""",style1,,""left"",effect,,,,,,,,,,,," & vbcrlf
end if
response.write ",""" & oMenuSub("label") & ""","""
if oMenuSub("sub") then
response.write "show-menu=" & oMenuSub("id")
else
response.write Application("homePage") & "?" & oMenuSub("link")
end if
response.write """,,,1" & vbcrlf
intOldTree=oMenuSub("level")
oMenuSub.movenext
loop
response.write "])" & vbcrlf
%>

This is draft working model, but works without problems, you could improve by using one SQL query and writing all the output inside ASP markup.

If you want the full source then email milesATimadeitlastDOTcom

scargin · Post by **scargin** » Wed Jun 11, 2003 12:41 am

Thanks,

This looks like what I was after.

Stuart

Milo · Post by **Milo** » Wed Jun 11, 2003 10:00 am

I've knocked up a DB driven example. You can adapt for MySQL, MS-SQL2000 whatever you like.

This access version is good for 100 or so, co-current users, then performance will suffer.

Download from http://www.imadeitlast.com/temp/monmenu.zip

I haven't included any admin, if you require I can knock those up as well.

Have fun....

xMANIGHTx · Post by **xMANIGHTx** » Wed Jun 11, 2003 8:07 pm

I guess the ASP code should use a recursive function to build the menu, unless you want to be tied to just 1 level subs.
Are you sure this example works with multiple submenus?
I didn't analyze the full code step by step, but at first glance it has something... "odd", like it shouldn't work.

Milo · Post by **Milo** » Wed Jun 11, 2003 9:23 pm

The way the Jscript is structured it doesn't need the recursive function. It groups main menus, then ALL the sub menus. Not main menu1 then menu1 children, main menu2 then all menu2 children.

To be fair, that is how 90% of menus are structured. But not Milonic.

It works, as many sub menus as your PC can handle. Try it.[/b]

xMANIGHTx · Post by **xMANIGHTx** » Thu Jun 12, 2003 9:24 am

You have in the code:

>response.write "addmenu(menu=["""
>response.write oMenuSub("level")

Where level it's the parentID, but what if you have 2 sub-menus for the same parent? You would have 2 addmenu(menu= with the same name. Wouldn't you? Maybe I'm missing something here because I have the recursive way imprinted in my mind

But what is the point to have the menu in a DB? I guess it's useful if you create menu based upon users permissions.
This forces you to think recursively. Let's think you have a link nested inside 2 sub menus that way: Main>Sub1>Sub2>Link and your Sub1>Sub2 point only to that link.
If your user doesn't have permission to access "Link" you shouldn't insert neither Sub1 or Sub2 infact they would be empty.

Milo · Post by **Milo** » Thu Jun 12, 2003 12:09 pm

I'll post a working version on the internet later on today, which outputs the ID into the menu, so you see more visually how the system hangs together.... the recursion isn't required as I "ORDER BY" the results, then if the parent (level) changes, I can assume there is a new submenu.

The example would produce this code (view source)
==========================
,"Main A","show-menu=1",,"",1
,"Main B","show-menu=2",,"",1
,"Main C","#",,"",1
,"Main D","#",,"",1
])
addmenu(menu=["1",
,,120,1,"",style1,,"left",effect,,,,,,,,,,,,
,"Sub A","#",,,1
])
addmenu(menu=["2",
,,120,1,"",style1,,"left",effect,,,,,,,,,,,,
,"Sub B","show-menu=6",,,1
])
addmenu(menu=["6",
,,120,1,"",style1,,"left",effect,,,,,,,,,,,,
,"Sub B1","show-menu=7",,,1
])
addmenu(menu=["7",
,,120,1,"",style1,,"left",effect,,,,,,,,,,,,
,"Sub B1A","#",,,1
])
==========================
The example only show one level submenu, but a menu with two submenus would look like...
====================
addmenu(menu=["7",
,,120,1,"",style1,,"left",effect,,,,,,,,,,,,
,"Sub B1A","#",,,1
,"Sub B1B","#",,,1
,"Sub B1C","#",,,1
,"Sub B1D","#",,,1
])
==========================

The reason I drive from a database, is so non-technical users and adminsitrators of the site can add, remove, modify menu items, without have to FTP, download, modify JS code, then FTP put back to the server. I've writen a content management system and I found the Milonic one of the most flexable and customisable around.

If you wanted to incorporate security, I would create a table like [ROLELOOKUP] for grouping permissions, and linking to your user table.

(A bit off the thread)
I have seen another example using session variables, thats ok, but each session object generates approx 8k each, if you have 100 users within the session timeout period (20 minutes), thats 800k of overhead, and if there is 2,000 visitors within 20 minutes, thats 16mb of overhead your IIS server is having to hold. Be carefull.

xMANIGHTx · Post by **xMANIGHTx** » Sun Jun 15, 2003 9:22 am

If you want to speed up performance and you have ONE and ONLY ONE menu for all your site usrs, just put it in an APPLICATION var instead of outputting it with Response.Write. Then each time you simply check the APP var. If it's empty then rebuild the menu from DB else simply Response.Write the APP var.
NOTE: You can use APPLICATION var even with SESSIONS disabled on the server.

Recursion.
Now I saw your example will work well, but again I guess recursion is a must if you want to be sure that your menu doesnt' have EMPTY elements.
Infact if a MENU has no links under it, you shouldn't draw the menu at all.
This is speciallt thru, if you bind it to user permissions.

Milo · Post by **Milo** » Sun Jun 15, 2003 10:34 pm

Application vars are a good point, there may be an issue with updating an application var, as this will momentarily lock the site, but that side a good idea. Was the point saying the menu wouldn't have to be drawn from the database each time? I think you might as "response.write" would have to be used either way.... but any ideas bypassing reading the database every visit are welcome, but then, that is the advantage of database driven content. I've finished load testing and all is rosey.

Regarding you other point of "recursion is a must" I can't agree, recursion needn't be used, if it isn't required. If a menu has no links (I guess you mean sub menus) under it, then is it a link itself. Or to put another way, a parent, can't be a link. It maybe a good idea to write one yourself and include recursion?

Can I request a new feature, so a item can be a parent AND a link.?

The site should be live in 4 weeks or so, I'll post the address.

xMANIGHTx · Post by **xMANIGHTx** » Mon Jun 16, 2003 12:16 am

Can I request a new feature, so a item can be a parent AND a link.?

There is already such a feature, using the ALT_URL property. You can have both a "show-menu" link and a real link too.

About recursion, I actually said "recursion is a MUST", when you don't want to come up with empty menus. When would this occur and what's the point of having menus without links underneath?
This is most common if you tie your DB driven menu to user permission.
Infact in that case you wolud tie each link elment to some users or users groups.
Let's have an example:
Main Menu
HOME (link itself)
COMMUMITY > link1 / link2
UTILITY > link3 / UTILITY-SUB > link4 / link5
Let's say this is the whole menu stored in the db. Now you want to tie menu to user permissions, so that the menu elements shows up, based on wich pages your users could actually go.
You have 2 users groups: Admin and Guest
Let's say Admin group can access all links, from link1 to link5. In that case recursion would be of no use.
Now let's say Guest group can access only link1, link2 and link3, this means that the whole UTILITY-SUB menu shouldn't be displayed at all, since link4 and link5 are not accessible from Guest users.
That's where recursion (both iterative or classic) is a "must".

It maybe a good idea to write one yourself and include recursion?

I already have this done, but it's heavily integrated in the whole autentication/skin system and I should change some coding to have this be "of common use".
It actually it's tied tho users/groups permissions, has a "SMART MENU" function wich draws a dynamic menu with the most visited links for each users and is part of a modular "skin" system wich lets each user choose gfx/layout he likes most.
An example is already up at http://www.evagroup.net/default_splash.asp but I have to create some dummy groups/users for you to test all this.
Infact it has a "security" system wich detects duplicate users and log off them, so I can't simply give you here just 1 access.
I'll make them in the next days and soon I hope to put here the recursive functions modified enough to be of common use.[/quote]

xMANIGHTx · Post by **xMANIGHTx** » Mon Jun 16, 2003 2:00 pm

I guess this topic (wich seems to be of much interest) should be moved in the Milonic v3 forum.

By the way..

I created 3 access for milonic users to test (you can try some hack around too, and let me know... LOL)
URL
http://www.evagroup.net
USERS
milonic1/milonic1
milonic2/milonic2
milonic3/milonic3

The first 2 users have same access, the third one has pseudo-admin access.
Note how the links disappear (and the parent menu empty of any links) based on user access.
If you move around the site, then log out and then log in again, you will have a special SMART MENU updated with all your most used links.
Note also the date time format up right wich vary for each user and vary on the fomrs too where you have to put in dates (this has nothing to do with menu but I think it's kool LOL)

Have a nice trip!
PS. If you get logged off, someone else just logged in with your account!

Ultrawuft123 · Post by **Ultrawuft123** » Wed Oct 22, 2003 12:17 pm

xMANIGHTx wrote:
Can I request a new feature, so a item can be a parent AND a link.?
There is already such a feature, using the ALT_URL property. You can have both a "show-menu" link and a real link too.

About recursion, I actually said "recursion is a MUST", when you don't want to come up with empty menus. When would this occur and what's the point of having menus without links underneath?
This is most common if you tie your DB driven menu to user permission.
Infact in that case you wolud tie each link elment to some users or users groups.
Let's have an example:
Main Menu
HOME (link itself)
COMMUMITY > link1 / link2
UTILITY > link3 / UTILITY-SUB > link4 / link5
Let's say this is the whole menu stored in the db. Now you want to tie menu to user permissions, so that the menu elements shows up, based on wich pages your users could actually go.
You have 2 users groups: Admin and Guest
Let's say Admin group can access all links, from link1 to link5. In that case recursion would be of no use.
Now let's say Guest group can access only link1, link2 and link3, this means that the whole UTILITY-SUB menu shouldn't be displayed at all, since link4 and link5 are not accessible from Guest users.
That's where recursion (both iterative or classic) is a "must".
It maybe a good idea to write one yourself and include recursion?
I already have this done, but it's heavily integrated in the whole autentication/skin system and I should change some coding to have this be "of common use".
It actually it's tied tho users/groups permissions, has a "SMART MENU" function wich draws a dynamic menu with the most visited links for each users and is part of a modular "skin" system wich lets each user choose gfx/layout he likes most.
An example is already up at http://www.evagroup.net but I have to create some dummy groups/users for you to test all this.
Infact it has a "security" system wich detects duplicate users and log off them, so I can't simply give you here just 1 access.
I'll make them in the next days and soon I hope to put here the recursive functions modified enough to be of common use.

[/quote]

Hi,

i ran into the problem that my menu's get empty if i attach user permissions to it. You said here that you'd post your recursive function after you adapted it. Did you already adapt the function?

If so, could you post it here?
It's a bit above my programming skills to create one myself actually.

xMANIGHTx · Post by **xMANIGHTx** » Wed Oct 22, 2003 1:00 pm

I answered you via email and sent you the code, since the function is not a "generic" function and it needs heavy modifications to fit into anyone coding scheme, so it would be useless here. Also it's very long.

Ultrawuft123 · Post by **Ultrawuft123** » Tue Nov 04, 2003 11:40 am

xMANIGHTx wrote:I answered you via email and sent you the code, since the function is not a "generic" function and it needs heavy modifications to fit into anyone coding scheme, so it would be useless here. Also it's very long.

Yes indeed it's very very long

It's also only good for some new ideas, it needs heavy rewriting to be of any use.
I've postponed the implementation of this function to the end of my project. Deadlines are comming closer every day now.

I'll just update the cms and instruct the users of it, to make sure they make a extra usergroup if they want to make a complete menu private.

xMANIGHTx · Post by **xMANIGHTx** » Tue Nov 04, 2003 11:47 am

If you refere to the "smart menu" voice that appear in my function, it gives the most used links for each user.

Ultrawuft123 · Post by **Ultrawuft123** » Thu Nov 20, 2003 12:19 pm

Ok, i've put something together dat cleans up empty menu's.

It's can also be used for common use.

This is how it works.

You must load you menu's en menuitems into an Array instead of directly writing it as a javascript variable.
Then you check for empty menuitems. If it finds an empty menuitem it searches for the menu name. then it will delete that menu name from the array (it will write an "" instead over the value in the array). then it searches again for a an empty menuitem so forth. It stops searching when it can't find an empty menuitem anymore.

It isn't the best code. But it get's the job done pretty good.
Ok, here it comes. some names and variables are in dutch. Sorry about that.
xMANIGHTx's menu function gave me some inspiration though!
z = 0
x = 0
c = i
Do until c = 0

If MenuItemArray(c)= "" Then
ClearMenu=(MenuArray(c))
MenuArray(c) = ""
ClearMenu=mid(ClearMenu,16)
temp = instr(clearmenu,CHR(34))
ClearMenu = LEFT(ClearMenu,temp)
ClearMenu = replace(ClearMenu,chr(34),"")
Doel = "show-menu="& Clearmenu
strSQLcheck = "SELECT naam FROM menuitems where doel ='" & Doel & "'"
Set rsCheck = adocon.Execute(strSQLcheck)
If NOT rscheck.EOF Then
FilterString = "," & chr(34) & rscheck("Naam") & chr(34) & "," & chr(34) & Doel & chr(34) & ",,,1"
NieuweMenuItemArray = Filter(MenuItemArray,Filterstring,True,vbTextCompare)
Tempvar = join(NieuweMenuItemArray)
NieuweArrayWaarde = Replace(Tempvar,Filterstring,"")

Do While x <= i

If MenuItemArray(x) = NieuweMenuItemArray(0) Then
MenuItemArray(x) = NieuweArrayWaarde
End if

x = x +1
Loop
x=0

End If
End if

c = c -1

Loop

'Het genereren van het javascript bestand

count = 0

Do While count < i

Response.write vbCrlf & MenuArray(count)
Response.write MenuItemArray(count)

If MenuArray(count) <>"" then

closemenu
End if

count = count +1

Loop

Ultrawuft123 · Post by **Ultrawuft123** » Thu Nov 20, 2003 12:20 pm

damn. pushed the wrong button.

xMANIGHTx · Post by **xMANIGHTx** » Thu Nov 20, 2003 6:44 pm

Does this works for menu with multiple submenus? Or does it work just for a first level of subs?

Milonic JavaScript Menu

Dynamic menu items from a database

Dynamic menu items from a database

Database driven menu

DataBase driven example