Server-side (dynamic) generation of menus

This document provides a sample code which dynamically creates menus based on a structure saved in a database on server. If you wish to learn more about the script functions used for dynamic creation, check out the FAQ question Q3049.

In our sample the whole structure is kept in a single database table with the following fields:

field name	field type	description
id	int	unique item identifier (cannot be zero)
parent_id	int	parent item (or zero for top-level items)
node_name	varchar	text of menu item
node_url	varchar	target URL

The above table is called sample_structure in the sample code. Please note that if you're using a different format for storing the structure, all you need to do is change the SQL queries in the sample code.

Note that you will need to remove the sample lines generated by DHTML Menu Studio. This means a few calls to SSAdd... functions that come after the comment: ' Add items to the menu here

Quick jump to implementation using: PHP | PHP (optimized) | ASP | ASP.NET | ColdFusion

Implementation using PHP + MySQL

This sample is only meant to clearly illustrate how dynamic menu creation can be accomplished using PHP. The problem with this sample is that there is a number of SQL queries required for creation of a menu. This is inefficient and we'd suggest using the other (optimized) version of PHP code presented further down on this page.

The following PHP code should be inserted just after the following comments generated by DHTML Menu Studio:

// --------------------------
// Add items to the menu here
// --------------------------

Please note that in order to get the menu to work with PHP dynamic creation code you have to:

make sure you have the Professional edition of the program
make sure the extension of your page where the PHP code is to be inserted is .php (or .php3 etc.)
make sure you chose dynamic creation before generating the code (in options / output / dynamic creation of menu structure -> set it to PHP code)

The sample code below is very simple. You will need to modify it in places which are preceeded by the comment: // MODIFICATION REQUIRED.

// --------------------------
// Add items to the menu here
// --------------------------

function getFindChildrenQuery ($db_id)
{
	// MODIFICATION REQUIRED
	// You might need to modify the query below
	// Its goal is to fetch all entries whose parent is identified by $db_id
	return "select * from sample_structure where parent_id=$db_id";
}

// The function hasChildren returns true if item identified by $db_id 
// has children or false otherwise
function hasChildren ($db_id)
{
	$query = getFindChildrenQuery ($db_id);
	$result = mysql_query ($query) or die ("SQL Query Failed: $query");
	return (mysql_num_rows ($result) > 0);
}

function createMenuLevel ($db_parentId, $menu_parentId)
{
	$query = getFindChildrenQuery ($db_parentId);
	$result = mysql_query ($query) or die ("SQL Query Failed: $query");
	while($row = mysql_fetch_array ($result))
	{
		// MODIFICATION REQUIRED
		// Adjust the following three lines so that the proper fields 
		// of the result rows get referenced
		$text = $row ['node_name'];
		$url = $row ['node_url'];
		$id = $row ['id'];
		if (hasChildren ($id))
		{
			if ($menu_parentId == null)
			{
				$menu_id = SSAddTopLevelMenu ($text, $url);
			}
			else
			{
				$menu_id = SSAddSubMenu ($text, $url, NULL, $menu_parentId);
			}
			createMenuLevel ($id, $menu_id);
		}
		else
		{
			SSAddMenuItem ($text, $url, NULL, $menu_parentId);
		}
	}
}

function createMenu ()
{
	// MODIFICATION REQUIRED
	// Open database
	// You'll need to change the values of server, user name, password
	// and database name below.
	// Note: very often the server should often be set to localhost
	$db_link = mysql_connect ("your_server","your_login","your_password") or die ("Unable to connect to SQL server"); 
	mysql_select_db ("database_name") or die ("Unable to select database");

	createMenuLevel (0, null);
	mysql_close ($db_link);
}

createMenu ();

Optimized implementation using PHP + MySQL

This is an optimized version of the PHP code above. The difference is that in this version, there is only one SQL query execution necessary to create the whole menu, where in the previous version the number of SQL queries depended on the number of popups in the menu.

The following PHP code should be inserted just after the following comments generated by DHTML Menu Studio:

// --------------------------
// Add items to the menu here
// --------------------------

Please note that in order to get the menu to work with PHP dynamic creation code you have to:

make sure you have the Professional edition of the program
make sure the extension of your page where the PHP code is to be inserted is .php (or .php3 etc.)
make sure you chose dynamic creation before generating the code (in options / output / dynamic creation of menu structure -> set it to PHP code)

The sample code below is very simple. You will need to modify it in places which are preceeded by the comment: // MODIFICATION REQUIRED.

// --------------------------
// Add items to the menu here
// --------------------------

// The function hasChildren returns true if item identified by $db_id 
// has children or false otherwise
function hasChildren ($items, $db_id)
{
	if (isset ($items [$db_id]))
		return TRUE;
	else
		return FALSE;
}

function createMenuLevel (&$items, $db_parentId, $menu_parentId)
{
	while(list ($parent_id, $row) = each ($items [$db_parentId]))
	{
		// MODIFICATION REQUIRED
		// Adjust the following three lines so that the proper fields 
		// of the result rows get referenced
		$text = $row ['node_name'];
		$url = $row ['node_url'];
		$id = $row ['id'];
		if (hasChildren ($items, $id))
		{
			if ($menu_parentId == null)
			{
				$menu_id = SSAddTopLevelMenu ($text, $url);
			}
			else
			{
				$menu_id = SSAddSubMenu ($text, $url, NULL, $menu_parentId);
			}
			createMenuLevel ($items, $id, $menu_id);
		}
		else
		{
			SSAddMenuItem ($text, $url, NULL, $menu_parentId);
		}
	}
}

function getMenuItems ()
{
	// MODIFICATION REQUIRED
	// You might need to modify the query below
	// Its goal is to fetch all menu entries
	// You'll also need to replace 'parent_id' with the name 
	// of the row identifying the parent item
	$query = "select * from sample_structure";
	$result = mysql_query ($query) or die ("SQL Query Failed: $query");
	while($row = mysql_fetch_array ($result))
	{
		$items [$row ['parent_id']] []= $row;
	}
	
	return $items;
}

function createMenu ()
{
	// MODIFICATION REQUIRED
	// Open database
	// You'll need to change the values of server, user name, password
	// and database name below.
	// Note: very often the server should often be set to localhost
	$db_link = mysql_connect ("your_server","your_login","your_password") or die ("Unable to connect to SQL server"); 
	mysql_select_db ("database_name") or die ("Unable to select database");

	$items = getMenuItems ();
	createMenuLevel ($items, 0, null);
	mysql_close ($db_link);
}

createMenu ();

Implementation using ASP + ODBC

The following ASP code should be inserted just after the following comments generated by DHTML Menu Studio:

' --------------------------
' Add items to the menu here
' --------------------------

Please note that in order to get the menu to work with ASP dynamic creation code you have to:

make sure you have the Professional edition of the program
make sure the extension of your page where the ASP code is to be inserted is .asp
make sure you chose dynamic creation before generating the code (in options / output / dynamic creation of menu structure -> set it to ASP code)

The sample code below is very simple. You will need to modify it in places which are preceeded by the comment: ' MODIFICATION REQUIRED.

' --------------------------
' Add items to the menu here
' --------------------------

Function getFindChildrenQuery (db_id)
	' MODIFICATION REQUIRED
	' You might need to modify the query below
	' Its goal is to fetch all entries whose parent is identified by db_id
	getFindChildrenQuery = "select * from sample_structure where parent_id=" & db_id
End Function

' The function hasChildren returns true if item identified by db_id 
' has children or false otherwise
Function hasChildren (db_id, connection)
	query = getFindChildrenQuery (db_id)
	Set result = connection.Execute (query)
	If result.EOF = True Then
		hasChildren = False
	Else
		hasChildren = True
	End If
End Function

Function createMenuLevel (db_parentId, menu_parentId, connection)
	query = getFindChildrenQuery (db_parentId)
	Set result = connection.Execute (query)
	Do While Not result.EOF
		' MODIFICATION REQUIRED
		' Adjust the following three lines so that the proper fields 
		' of the result rows get referenced
		text = result.Fields ("node_name")
		url = result.Fields ("node_url")
		id = result.Fields ("id")
		bFolder = hasChildren (id, connection)
		If bFolder = True Then
			If menu_parentId = Null Then
				menu_id = SSAddTopLevelMenu (text, url, Null, Null, Null)
			Else
				menu_id = SSAddSubMenu (text, url, Null, menu_parentId, Null, Null)
			End If
			createMenuLevel id, menu_id, connection
		Else
			SSAddMenuItem text, url, Null, menu_parentId, Null, Null
		End If
		result.MoveNext
	Loop
End Function

Function createMenu ()
	' MODIFICATION REQUIRED
	' Open database
	' You'll need to change the values of server, user name, password
	' and database name below.
	' Note: very often the server should often be set to localhost

	Dim connection
	Set connection = Server.CreateObject("ADODB.Connection")
	connection.Open "DSN=database_name; UID=your_user; PWD=your_password;"
	createMenuLevel 0, null, connection
	connection.Close
End Function

createMenu

Implementation using ASP.NET + ODBC

First of all, you'll need to add aspcompat="true" parameter to the following line:
<%@ Page Language="VB" %>
The updated version will look like this:
<%@ Page Language="VB" aspcompat="true" %>

The following VB code should be inserted just after the following comments generated by DHTML Menu Studio:

' --------------------------
' Add items to the menu here
' --------------------------

Please note that in order to get the menu to work with ASP.NET dynamic creation code you have to:

make sure you have the Professional edition of the program
make sure the code will be interpreted by the ASP.NET engine on the server (normally should have the .aspx extension)
make sure you chose dynamic creation before generating the code (in options / output / dynamic creation of menu structure -> set it to ASP.NET code)

The sample code below is very simple. You will need to modify it in places which are preceeded by the comment: ' MODIFICATION REQUIRED (click here for another sample submitted by one of our users using SQLDataClient instead of ADO)

' --------------------------
' Add items to the menu here
' --------------------------

%>
<script runat="server" language="VB">

Function getFindChildrenQuery (db_id)
	' MODIFICATION REQUIRED
	' You might need to modify the query below
	' Its goal is to fetch all entries whose parent is identified by db_id
	getFindChildrenQuery = "select * from tbl where parent_id=" & db_id
End Function

' The function hasChildren returns true if item identified by db_id 
' has children or false otherwise
Function hasChildren (db_id, connection)
	dim query = getFindChildrenQuery (db_id)
	dim result = connection.Execute (query)
	If result.EOF = True Then
		hasChildren = False
	Else
		hasChildren = True
	End If
End Function

Function createMenuLevel (db_parentId, menu_parentId, connection)
	dim query = getFindChildrenQuery (db_parentId)
	dim result = connection.Execute (query)
	Do While Not result.EOF
		' MODIFICATION REQUIRED
		' Adjust the following three lines so that the proper fields 
		' of the result rows get referenced
		dim text = result.Fields ("node_name").Value
		dim url = result.Fields ("node_url").Value
		dim id = result.Fields ("id").Value
		dim bFolder = hasChildren (id, connection)
		dim menu_id
		If bFolder = True Then
			If menu_parentId = "0" Then
				menu_id = SSAddTopLevelMenu (text, url, "", "", "")
			Else
				menu_id = SSAddSubMenu (text, url, "", menu_parentId, "", "")
			End If
			createMenuLevel (id, menu_id, connection)
		Else
			SSAddMenuItem (text, url, "", menu_parentId, "", "")
		End If
		result.MoveNext
	Loop
End Function

Function createMenu ()
	' MODIFICATION REQUIRED
	' Open database
	' You'll need to change the values of server, user name, password
	' and database name below.
	' Note: very often the server should often be set to localhost

	Dim connection 
	connection = Server.CreateObject("ADODB.Connection")
	connection.Open ("DSN=database_name; UID=your_user; PWD=your_password;")
	createMenuLevel (0, "0", connection)
	connection.Close
End Function

</script>

<%
createMenu

Response.Write (outCode)

Note: The line above was already generated by DHTML Menu Studio

Implementation using ColdFusion

Note that the current version of DHTML Menu Studio does not directly support dynamic generation for ColdFusion. This is why you will need to perform some manual modifications. We'll use the PHP code as the basis, then we'll convert this to ColdFusion. To get started, you'll need to set "options/output/dynamic creation of menu structure" to "PHP code". After menu generation, in the output code, you'll need to remove all code between the <?php and ?> tags, which will leave you with the following code:

<div id="menubar_m1"></div>

<script language="JavaScript" type="text/javascript">
<!--
//-->
</script>

Now, the following ColdFusion code should be inserted inside the script tag (just after "<!--") :

Please note that in order to get the menu to work with ColdFusion dynamic creation code you have to:

make sure you have the Professional edition of the program
make sure the code will be interpreted by the ColdFusion engine on the server (normally should have the .cfm extension)
make sure you chose dynamic creation before generating the code (options / output / dynamic creation of menu structure)

The sample code below is very simple. You will only need to modify the code in red (data source, table name and row names).

<div id="menubar_m1"></div>
<script language="JavaScript" type="text/javascript">
<!--

<cfscript>

ssNextMenuId = 1;

function SSInsertItemToMenu (functionName, itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
{
	var currentId = "ssMnu" & ssNextMenuId;
	writeOutput ("var ssMnu" & ssNextMenuId & "=" & functionName & "(""" & itemText & """, """ & itemUrl & """");
	if (Len (targetFrame) gt 0 or Len (itemIcon) gt 0 or Len (nMenu) gt 0 or Compare (functionName, 'addTopLevelMenu') neq 0)
	{
		writeOutput (",""" & targetFrame & """");
		if (Compare (functionName, 'addTopLevelMenu') neq 0)
		{
			writeOutput ("," & (IIf (Len (parentMenu) gt 0, de (parentMenu), de ("null"))));
		}
		if (Len (itemIcon) gt 0 or Len (nMenu) gt 0)
		{
			writeOutput (",""" & itemIcon & """");
			if (Len (nMenu) gt 0)
			{
				writeOutput (",""" & nMenu & """");
			}
		}
	}
	writeOutput (");" & Chr(13));
	ssNextMenuId = ssNextMenuId + 1;
	return currentId;
}

function SSAddTopLevelMenu (itemText, itemUrl, targetFrame, itemIcon, nMenu)
{
	return SSInsertItemToMenu ("addTopLevelMenu", itemText, itemUrl, targetFrame, "", itemIcon, nMenu);
}

function SSAddSubMenu (itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
{
	return SSInsertItemToMenu ("addSubMenu", itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu);
}

function SSAddMenuItem (itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu)
{
	return SSInsertItemToMenu ("addMenuItem", itemText, itemUrl, targetFrame, parentMenu, itemIcon, nMenu);
}

function SSGetTopLevelMenu (nSubMenu, nMenu)
{
	var currentId = "ssMnu" & ssNextMenuId;
	writeOutput ("var ssMnu" & ssNextMenuId & "=getTopLevelMenu(" & nSubMenu);
	if (Len (nMenu) gt 0)
	{
		writeOutput (",""" & nMenu & """");
	}
	writeOutput (");" & Chr(13));
	ssNextMenuId = ssNextMenuId + 1;
	return currentId;
}

// --------------------------
// Add items to the menu here
// --------------------------


</cfscript>

<cffunction name="getFindChildrenQuery">
	<cfargument name="db_id" type="string" required="true">
	<cfquery name="query" datasource="dyn">
		select * from tbl where parent_id=#db_id#
	</cfquery>

	<cfreturn query>
</cffunction>

<cffunction name="hasChildren">
	<cfargument name="db_id" type="string" required="true">
	<cfset query = getFindChildrenQuery (db_id)>
	<cfif query.RecordCount gt 0>

		<cfreturn true>
	<cfelse>
		<cfreturn false>
	</cfif>
</cffunction>

<cffunction name="createMenuLevel">

	<cfargument name="db_parentId" type="string" required="true">
	<cfargument name="menu_parentId" type="string" required="true">
	<cfset query = getFindChildrenQuery (db_parentId)>
	<cfloop query = "query">
		<cfoutput>
		<cfset textValue = node_name>

		<cfset urlValue = node_url>
		<cfset idValue = id>
		
		<cfif hasChildren (idValue)>
			<cfif Len (menu_parentId) EQ 0>
				<cfset menu_id = '#SSAddTopLevelMenu (textValue, urlValue, "", "", "")#'>

			<cfelse>
				<cfset menu_id = '#SSAddSubMenu (textValue, urlValue, "", menu_parentId, "", "")#'>
			</cfif>
			#createMenuLevel (idValue, menu_id)#
		<cfelse>
			<cfset item = '#SSAddMenuItem (textValue, urlValue, "", menu_parentId, "", "")#'>
		</cfif>

		</cfoutput>
	</cfloop>
</cffunction>

<cfscript>
function createMenu ()
{
	createMenuLevel (0, "");
}

createMenu ();
</cfscript>

//-->

</script>