Server-side (dynamic) generation of site maps

This document provides a sample code which dynamically creates sitemaps based on a structure saved in a database on server. 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 tree 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.

Implementation using PHP + MySQL

The following PHP code should be inserted just after the following comments generated by SiteXpert:

 

// -----------------------------
// Add items to the sitemap here
// -----------------------------

 

Please note that in order to get the site map 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 / tree properties / dynamic creation of 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 sitemap 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 createTreeLevel ($db_parentId, $tree_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'];
		$bFolder = hasChildren ($id);
		$tree_id = SSInsertItem ($bFolder, $tree_parentId, $text, "", "", $url);
		if ($bFolder)
		{
			createTreeLevel ($id, $tree_id);
		}
	}
}

function createTree ()
{
	// 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
	mysql_pconnect ("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");

	createTreeLevel (0, null);
}

createTree ();

Implementation using ASP + ODBC

The following ASP code should be inserted just after the following comments generated by SiteXpert:

 

' -----------------------------
' Add items to the sitemap here
' -----------------------------

 

Please note that in order to get the site map 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 / tree properties / dynamic creation of 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 sitemap 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 createTreeLevel (db_parentId, tree_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)
		tree_id = SSInsertItem (bFolder, tree_parentId, text, "", "", url)
		If bFolder = True Then
			createTreeLevel id, tree_id, connection
		End If
		result.MoveNext
	Loop
End Function

Function createTree ()
	' MODIFICATION REQUIRED
	' Open database
	' You'll need to change the connection parameters below

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

createTree

In case of the other server-side creation methods supported by the program (ASP.NET, JSP) you should have no problem with appropriate changes in the code syntax as the general algorithm is the same.