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. |
Save This Page