Dynamic menu items from a database

Poster: scargin
Dated: Monday June 9 2003 - 2:03:42 BST

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

Poster: Greg Squire
Dated: Monday June 9 2003 - 18:34:56 BST

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

Database driven menu

Poster: Milo
Dated: Tuesday June 10 2003 - 13:20:40 BST

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

Poster: scargin
Dated: Wednesday June 11 2003 - 0:41:43 BST

Thanks,

This looks like what I was after.

Stuart

DataBase driven example

Poster: Milo
Dated: Wednesday June 11 2003 - 10:00:18 BST

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

Poster: xMANIGHTx
Dated: Wednesday June 11 2003 - 20:07:39 BST

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.

Poster: Milo
Dated: Wednesday June 11 2003 - 21:23:50 BST

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]

Poster: xMANIGHTx
Dated: Thursday June 12 2003 - 9:24:58 BST

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.

Poster: Milo
Dated: Thursday June 12 2003 - 12:09:12 BST

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.

Poster: xMANIGHTx
Dated: Sunday June 15 2003 - 9:22:34 BST

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.

Poster: Milo
Dated: Sunday June 15 2003 - 22:34:30 BST

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.

Poster: xMANIGHTx
Dated: Monday June 16 2003 - 0:16:23 BST



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

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]

Poster: xMANIGHTx
Dated: Monday June 16 2003 - 14:00:42 BST

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!

Poster: Ultrawuft123
Dated: Wednesday October 22 2003 - 12:17:14 BST

[/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.

Poster: xMANIGHTx
Dated: Wednesday October 22 2003 - 13:00:15 BST

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.

Poster: Ultrawuft123
Dated: Tuesday November 4 2003 - 10:40:56 GMT



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.

Poster: xMANIGHTx
Dated: Tuesday November 4 2003 - 10:47:10 GMT

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

Poster: Ultrawuft123
Dated: Thursday November 20 2003 - 11:19:41 GMT

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

Poster: Ultrawuft123
Dated: Thursday November 20 2003 - 11:20:50 GMT

damn. pushed the wrong button.

Poster: xMANIGHTx
Dated: Thursday November 20 2003 - 17:44:51 GMT

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

Poster: Ultrawuft123
Dated: Friday November 21 2003 - 8:46:35 GMT

i've tested it about 3 levels deep.

I had a menu, with a submenu, with a submenu wit a menu item.

I made the menuitem private, so it disappeared. It deleted the submenu, submenu and the menu.

Poster: xMANIGHTx
Dated: Friday November 21 2003 - 9:11:52 GMT

Good job Ultrawuft123

Poster: Ultrawuft123
Dated: Friday November 21 2003 - 9:51:23 GMT

I hope it still works when 1000 users use it

Re: DataBase driven example

Poster: mortency
Dated: Friday November 28 2003 - 12:40:28 GMT

Hi Milo!

Ive seen your meny and like it very much. Yoy say you can knock up an admin to the meny. Is that possible???

Morten

Admin to http://www.imadeitlast.com/temp/monmenu.zip

Poster: mortency
Dated: Friday November 28 2003 - 12:50:04 GMT

Hi!

Does anyone have made an admin to the http://www.imadeitlast.com/temp/monmenu.zip

//Morten

Dynicmally Generate the Main Menu

Poster: cletus88
Dated: Friday January 16 2004 - 22:58:04 GMT

I have my menu database driven using ASP, everything works fine, but i would like to be able to have it so that i can dynamically decide which level the menu starts with, ie
level1
----level2
----level2
---------level3
level1

this is the main structure, so then when i click on one of the level1 items, it only displays the menu as being from it's children down, so if i clicked on the top level1, i would see

level2
level2
-----level3

I could really use some suggestions.
Thanks in advance

ASP + Access

Poster: Milo
Dated: Thursday April 26 2007 - 19:19:09 BST

I'm not suggesting you use access for big sites, but its a good demo.




http://01792.org

Re: Dynamic menu items from a database

Poster: Ruth
Dated: Wednesday April 23 2008 - 17:53:32 BST

Hi Milo,

Thanks so much for the reply and the link. Have a good one

Ruth

Re: Dynamic menu items from a database

Poster: micnie2020
Dated: Wednesday April 23 2008 - 2:05:21 BST

Dear All,

I'm tryinbg to download http://www.imadeitlast.com/temp/monmenu.zip. It's seem this zip file has been removed. Can anyone resend it again at this forum?

Appreciate for your kindly help.

Thank you.

Regards,
Michelle

Re: Dynamic menu items from a database

Poster: Ruth
Dated: Wednesday April 23 2008 - 7:07:20 BST

Hi,

We wouldn't be able to give you any help. The only menu on this site or supported here is Milonic DHTML Javascript Menu.

You'd have to contact that site and ask them for whatever that monmenu is

EDIT: I found the info, that is something that some user put together and they no longer have it up. In the Milonic download there is an extras folder, that folder has asp, php, database menus so you might want to download the menu from here. Whatever was up back in 2003 would probably not work with the new version.

Also, there's a database menu example here http://support.milonic.com/datamenu/ but again, I believe that stuff is in the Milonic download now.

Ruth

Re: Dynamic menu items from a database

Poster: Milo
Dated: Wednesday April 23 2008 - 9:36:05 BST

I still have it, I get about 5 emails a week about doing this.. I don't mind, nice to see folk getting to grips with it.


http://01792.org/halo/template.asp?c=252

In the package,

Access database,
Classic asp page,
Install video.

Everything you need.

Re: Dynamic menu items from a database

Poster: milotheGreat
Dated: Tuesday November 24 2009 - 9:41:18 GMT

I've been getting a few re-requests for this item, and as I've rebuild my website about4 times since, I thought I'ld repost the new URL..

http://01792.org/blog/post/2009/11/18/D ... -menu.aspx

Thanks guys...

Re: Dynamic menu items from a database

Poster: Andy
Dated: Tuesday November 24 2009 - 10:02:19 GMT

What did you use to record the demo with?

I need something like that for Webplugs

Re: Dynamic menu items from a database

Poster: milotheGreat
Dated: Tuesday November 24 2009 - 12:23:15 GMT

Camtasia, expensive, but honestly the best one out there...

Otherwise try Screen toaster, or the humble Microsoft media encoder.

Enjoy.