Problem With Recursive Database Driven Navigation

I almost did it in CF but I am stuck at the end!

Page 1 of 1

3 Replies - 4735 Views - Last Post: 09 August 2009 - 06:48 PM Rate Topic: -----

#1 ComboAlex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 31-July 09

Problem With Recursive Database Driven Navigation

Post icon  Posted 07 August 2009 - 02:17 PM

Usually I don't write new topics unless it's really necessary. Now, my lack of experience with ColdFusion made me loose a couple of days figuring out how to make a relatively easy application: a recursive, database driven navigation menu. Those who laugh at me now and say: "pff, that's sooo easy!", PLZZZZ HEEEEELP! Have mercy on me, cause I am getting crazy. :crazy:

You see, I already have the application written in PHP. My friend did it. Look at the end of this post. What I am trying to do now is translating this PHP code into CF code. I really need to have it in CF.

I am giving you here all I've got until now and pray that someone will end my misery by helping me finding out the solution.

So here a website that shows how is the navigation suppose to work: click... so that you actually see what I have in mind as a final result. I don't care about the look, because once is finished, it is then easy to apply CSS styles for the outlook. What I am interested now is only that it works properly.

Here is again the database definition in picture and I also exported and attached a file of the database table content if you want to try it out. When I was using PHPMyadmin I knew exactly how to export a database and it's content, but now I have to apologize but I an MySQL GUI that it's totally new for me. :) So I attached a .CVF file. I will try to find out a better export format, so you can easily take it and try it.

Posted Image


And here is my well-commented code, so I won't waste more time. There's one function that's bothering me a lot. Look at the comments in the code. Under my CF code there is also the properly working PHP versio of the code that I am trying here to translate to CF code. Anyone that is willing to help me will receive a ton of positive energy and if you somehow get in this part of the world I buy you dinner! Or beer... :D

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Navigation</title>
</head>

<body>

<!-------------------------------------------------------------------->
<!------------------------ functions only----------------------------->
<!-------------------------------------------------------------------->


<!--- The function accepts a given aray, reverses it and returns the reversed array - WORKS OK --->
<!--- Why the heck CF doesn't have a built-in function that reverses a given array? :) --->
<cffunction name="reverseArray" access="public" returntype="array">
	<CFARGUMENT name="inputArray" type="array" required="yes" default=0 >	
	<CFSCRIPT>			
		For (i = 1; i LTE (ArrayLen(inputArray)/2); i++)
		{	
			temp1 = i;
			temp2 = ArrayLen(inputArray) + 1 - i; 					
			newArray[i] = inputArray[temp2];
			newArray[temp2] = inputArray[temp1];											
		}
		if (ArrayLen(inputArray) mod 2 != 0)
			newArray[(ArrayLen(inputArray)+1)/2] = inputArray[(ArrayLen(inputArray)+1)/2];		
		return newArray;		
	</CFSCRIPT>   
</cffunction>


<!--- The function makes an array of navigation "bread crumbs" (parentid numeric values) and returns it in
a correct order - WORKS OK --->
<cffunction name="MakeBreadCrumb" access="public" returntype="array">
	<CFARGUMENT name="id" type="numeric" required="yes" default=0 >
	<CFARGUMENT name="cnt" type="numeric" required="yes" default=0 >			
	
	<!--- we make a new array which will hold all the parents (parentid values), but we declare it only the
	first time in the recursive call --->	
	<cfif arguments.cnt IS 0>		
		<cfset breadCrumbArray=ArrayNew(1)>
	</cfif>		
	
	<!--- selecting the current parentid --->	
	<cfquery name="qryItem" datasource="test">
		SELECT parentid
		FROM testnavigation
		WHERE id = #arguments.id#
	</cfquery>
	
	<!--- saving the current parentid to the array --->
	<cfset breadCrumbArray[#arguments.cnt#]=arguments.id>
	
	<cfif qryItem.parentid IS NOT 0> <!--- if we reached the root level of the navigation --->
		<!--- HERE WE MAKE THE RECURSIVE CALL of "MakeBreadCrumb" function - the input argument
		becomes the current <parentid> from the <qryItem> query --->
		<cfset MakeBreadCrumb(id = qryItem.parentid, cnt = cnt + 1) >
	<cfelse> <!---  if we still haven't reached the root level --->
		<!--- we reverse the breadCrumb array so we can "follow the bread crumbs" from start to the end
		in the correct order --->
		<!--- here is where we finally use the above written "reverseArray" function --->		
		<cfset reversedBreadCrumbArray = reverseArray(inputArray = breadCrumbArray)>		
	</cfif>
	
	<cfreturn reversedBreadCrumbArray>	
</cffunction>


<!--- the function returns a boolean value (true or false) depending if the input argument is in
the specified array - WORKS OK --->
<cffunction name="inArray" access="public" returntype="boolean">
	<cfargument name="element" type="numeric">
	<cfargument name="inputArray" type="array">
	
	<cfset arrayLength = ArrayLen(arguments.inputArray)>			
	
	<cfloop from="1" to="#arrayLength#" index="i">		
		<cfif arguments.inputArray[i] EQ arguments.element>						
			<cfreturn true>
			<!--- if this happens only once it's enough to say that the element exists in the inputArray,
			so we exit the function here  --->		  
		</cfif>	 	
	</cfloop>	
	
	<!--- if we get until here it means that the <cfif> statement above was never true and it's content was every time
	skipped--->
	<cfreturn false> 
</cffunction>


<!--- the function returns a numeric value from a one-dimensional table on a given index - WORKS OK--->
<!--- had to make this "ugly hack" using a <cfscript>, since I couldn't figure out how to get this to work:
#inputArray[#inputCnt#]#... I am new to CF :) --->
<!--- it seems nesting variables (in array index doesn't work) - example: inputArray[#inputCnt#]) --->
<cffunction name="getNumberFromArray" access="public" returntype="numeric">
	<cfargument name="inputArray" type="array">
	<cfargument name="inputIndex" type="numeric"> 
	<cfscript>
		return inputArray[inputIndex];	
	</cfscript>
</cffunction>


<!--- PROBLEM HERE !!! --->
<!--- PROBLEM HERE !!! --->
<!--- PROBLEM HERE !!! --->

<!--- IF EVERYTHING IS OK AS I THINK IT IS, WE ONLY NEED TO REPAIR THIS FUNCTION IN ORDER TO PROPRERLY DISPLAY THE CHOSEN BRANCH - DOESN'T WORK PROPERLY --->
<cffunction name="displayBranch" access="public" returntype="void">
	<cfargument name="inputItem" type="numeric">
	<cfargument name="inputCnt" type="numeric">
	<cfargument name="inputArray" type="array">
	<cfargument name="inputRootId"   type="numeric">
	
	<!--- we make sure the variables in the function are local --->
	<cfset var parentCrumb = 0>
	<cfset var isInArray = false>
	
	<cfif inputCnt LTE ArrayLen(arguments.inputArray)>
		<!---
		Since we cannot get a number from the array in this way #inputArray[#inputCnt#]# (variables nesting in array index),
		I will make here a small hack in the "getNumberFromArray" function above. IF SOMEONE KNOWS A BETTER WAS, PLEASE TELL ME. I am new to CF.
		 --->
		<cfset parentCrumb = getNumberFromArray(inputArray = arguments.inputArray, inputIndex = arguments.inputCnt)>
		
		<cfquery name="qrySubMenu" datasource="test">
			SELECT *
			FROM testnavigation
			WHERE parentid = #parentCrumb#
			ORDER BY navorder
		</cfquery>
		
		<!--- <cfdump var="#inputArray#"> --->
		
		<cfloop query="qrySubMenu">
		
			<cfset isInArray = inArray(element = parentCrumb, inputArray = arguments.inputArray)> 	
			<!--- <cfdump var="#isInArray#"> --->
			<cfif (qrySubMenu.parentid EQ arguments.inputRootId) AND (isInArray IS true)>				
				<cfoutput>
					<div style="padding-left:#inputCnt*20#px">
						#qrySubMenu.id# <a href="alexnavigation.cfm?#qrySubMenu.link#">#qrySubMenu.navdescription# (#qrySubMenu.parentid#)</a>
					</div> 
				</cfoutput>	  
			   	<cfset displayBranch(inputItem = parentCrumb, inputCnt = inputCnt+1, inputArray = arguments.inputArray, inputRootId = arguments.inputItem)> 
			</cfif>
			
		</cfloop>
		
	</cfif> 
	 
</cffunction>

<!--- END OF PROBLEM --->
<!--- END OF PROBLEM --->
<!--- END OF PROBLEM --->



<!--- this function displays the main navigation menu, using other above written functions - SEEMS TO WORK OK --->
<cffunction name="displayMainNavigation" access="public" returntype="void">
	<cfargument name="inputArray" type="array">	
	
	<!--- we query the root items in the navigation (all with parentid = 0) --->
	<cfquery name="qryRootItems" datasource="test">
		SELECT *
		FROM testnavigation
		WHERE parentid = 0
	</cfquery>
	
	<!--- from here on we actually DISPLAY THE NAVIGATION --->
	<cfloop query="qryRootItems">
   		<cfoutput>
			<a href="alexnavigation.cfm?#qryRootItems.link#">#qryRootItems.navdescription#</a><br /> <!--- root items --->
		</cfoutput>
		<cfif inputArray[1] EQ qryRootItems.id> <!--- we have to choose under which root item we want to display the "family branch"... --->				   
			<!--- ...the recursive "displayBranch" function does the job... well... should do it, but is still not working properly :) PLEASE, HEEEELP!!! --->						
			<cfset displayBranch(inputItem = URL.item, inputCnt = 1, inputArray = arguments.inputArray, inputRootId = qryRootItems.id) >
		</cfif>				
	</cfloop>	
</cffunction>


<!-------------------------------------------------------------------->
<!--------------------------- "main program" ------------------------->
<!-------------------------------------------------------------------->


<!--- let's call the function that makes the breadCrumb --->
<cfset breadCrumbcArray = MakeBreadCrumb(id=URL.item, cnt=1)>

<!--- BreadCrumb Array is succesfully made, so now we can display the main navigation --->
<cfset displayMainNavigation(inputArray=breadCrumbcArray)>

</body>
</html>



Properly working PHP code
<?
$_GET['sklop'] = 0;
function find_parents($item,$parents = array()){
	$parent_q = mysql_query('SELECT * FROM navigation WHERE id = '.$item);
	$parent_r = @mysql_fetch_assoc($parent_q);
	
	if($parent_r['parent'] > 0) {
		$parents[] = $parent_r['parent'];
		return find_parents($parent_r['parent'],$parents);
	}
	else {	
		 return array_reverse($parents);
	}
}

function display_child($sub_id,$aa=1, $out='')
{
	global $parents;

	$sub_q = mysql_query('SELECT * FROM navigation WHERE parent = '.$sub_id.'  AND visible = "yes" ORDER BY sortorder');
	
	while($sub_r = mysql_fetch_assoc($sub_q))
	{
		if($sub_r['id'] == $_GET['item'])
		{
			$class = 'link_active';
 			$style = 'color:#ffffff; text-decoration:none; cursor:pointer; font-weight:bold;"';
		}
		else
		{
			$class = 'link_nav_'.$aa;
			$style = 'text-decoration:none; cursor:pointer;"';
		}		
		
		$out .= '<tr>'."\n"; 
		
		if($sub_r['link'] != '')
			$link = $sub_r['link'].'&';
		
		if(!strstr($sub_r['link'],'item'))
			$navlink = 'item='.$sub_r['id'].'&grp='.$sub_r['name'].'&sklop='.$_GET['sklop'];		
		else
			$navlink = '';
		
		if($aa == 1)
			$bullet = '<img src="'.$baseurl.'imgs/navigacija/bullet_celinka.jpg" border="0" style="margin-top:0px; margin-left:8px;">';
		elseif ($aa == 2)
			$bullet = '<img src="'.$baseurl.'imgs/navigacija/bullet_polovinka.jpg" border="0" style="margin-top:0px; margin-left:17px;">';
		elseif ($aa == 3)
			$bullet = '<img src="'.$baseurl.'imgs/navigacija/bullet_cetrtinka.jpg" border="0" style="margin-top:0px; margin-left:24px; margin-right:0px;">';
		else
			$bullet = '';
			
		$out .= '<td  class="level_'.$aa.'">'; 
		$out .= '<table width="100%"  border="0" cellpadding="0" cellspacing="0" style="margin-right:2px;">'."\n";
		$out .= '<tr>'; 
		$out .= '<td width="7" valign="top">'.$bullet.'</td>';
		if ($aa == 2)
		{
			$out .= '<td class="level_'.$aa.'" align="left" style="padding-left:'.(3 * $aa).'px;'.$style.'" onclick="location=\'index.php?'.$link.$navlink.'\'"><a href="#" class="'.$class.'">'.php_translate($sub_r['name'],$_SESSION['lang']).'</a></td>'."\n";
		}
		elseif ($aa == 1)
		{
			$out .= '<td class="level_'.$aa.'" align="left" style="padding-left:'.(7 * $aa).'px;'.$style.'" onclick="location=\'index.php?'.$link.$navlink.'\'"><a href="#" class="'.$class.'">'.php_translate($sub_r['name'],$_SESSION['lang']).'</a></td>'."\n";
		}
		else
		{
			$out .= '<td class="level_'.$aa.'" align="left" style="padding-left:'.(2 * $aa).'px;'.$style.'" onclick="location=\'index.php?'.$link.$navlink.'\'"><a href="#" class="'.$class.'">'.php_translate($sub_r['name'],$_SESSION['lang']).'</a></td>'."\n";
		}		
		
		$out .= '</tr>';
		$out .= '</table>';
		$out .= '</td>'."\n";		
		$out .= '</tr>'."\n";
		$out .= '<tr><td height="1"></td></tr>'."\n";

		if (in_array($sub_r['id'],$parents))
			$out = display_child($sub_r['id'],($aa+1), $out);
		
		unset($link);
		
	} // while	
	
	return $out;
	
} // function display_child


if(!isset($_GET['item']))
	$_GET['item'] = $_GET['sklop'];

$parents = find_parents($_GET['item']);
$parents[] = $_GET['item'];

if(isset($_GET['sklop']))
{
	$out = '<table width="100%"  border="0" cellpadding="0" cellspacing="0" bgcolor="#FFFFFF" tyle="margin-right:2px;">'."\n";
	$out .= display_child($_GET['sklop']);
	$out .= '</table>'."\n";
	$smarty->assign('sklop',$_GET['sklop']);
}
//var_dump($out);

$smarty->assign('navigacija',$out);
$smarty->assign('preload',$pre_l);


/* nav bar */
foreach($parents as $item)
	$sql[] = ' id = '.$item;

$sql2 = implode(' OR ',$sql);

$nav_bar_q = mysql_query('SELECT * FROM navigation WHERE '.$sql2);
$aa=1;

if($page != 'home')
	$nav_bar[0]['item'] = '<a href="'.$baseurl.'" class="verdana_10_787878">Domov</a>';

while($nav_bar_r = @mysql_fetch_assoc($nav_bar_q))
{
	$nav_bar[$aa] = $nav_bar_r;
	if($nav_bar_r['id'] != $_GET['item'])
	{
		if($nav_bar_r['link'] != '') $link = $nav_bar_r['link'].'&';
		$nav_bar[$aa]['item'] = '<a href="index.php?'.$link.'item='.$nav_bar_r['id'].'&grp='.$nav_bar_r['name'].'&sklop='.$_GET['sklop'].'" class="verdana_10_787878">'.ucfirst(strtolower(php_translate($nav_bar_r['name'],$_SESSION['lang']))).'</a>';
	}
	else
		$nav_bar[$aa]['item'] = ucfirst(strtolower(php_translate($nav_bar_r['name'],$_SESSION['lang'])));
	
	$aa++;
}

//var_dump($nav_bar);
$smarty->assign('navbarcnt',count($nav_bar));
$smarty->assign('navbar',$nav_bar);

// var_dump($parents);

?>



Is This A Good Question/Topic? 0
  • +

Replies To: Problem With Recursive Database Driven Navigation

#2 ComboAlex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 31-July 09

Re: Problem With Recursive Database Driven Navigation

Posted 08 August 2009 - 05:58 AM

Really noone is able / willing to help? :blink: What do I have to promise? Beer? Coke? :D

A professor's assistant at the university just told me that it's not necessary to use a recursive function and that I should do a function that uses an array as a stack (PUSH, POP) instead. He explained to me how it should work. I think this is a great idea. What do you think? Any ideas?

This post has been edited by ComboAlex: 08 August 2009 - 08:55 AM

Was This Post Helpful? 0
  • +
  • -

#3 xheartonfire43x  Icon User is offline

  • D.I.C Regular

Reputation: 46
  • View blog
  • Posts: 454
  • Joined: 22-December 08

Re: Problem With Recursive Database Driven Navigation

Posted 09 August 2009 - 05:29 PM

View PostComboAlex, on 8 Aug, 2009 - 04:58 AM, said:

Really noone is able / willing to help? :blink: What do I have to promise? Beer? Coke? :D

A professor's assistant at the university just told me that it's not necessary to use a recursive function and that I should do a function that uses an array as a stack (PUSH, POP) instead. He explained to me how it should work. I think this is a great idea. What do you think? Any ideas?


What you need to do is in your SQL you need to do a group by parentID and order by navorder. That will give you groups of nav buttons. Then you need to loop over the groups and say if the parentID is the current ID then this is the nav-set to show.
Was This Post Helpful? 0
  • +
  • -

#4 ComboAlex  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 34
  • Joined: 31-July 09

Re: Problem With Recursive Database Driven Navigation

Posted 09 August 2009 - 06:48 PM

Thank you for your help. I actually knew the logic, but was un succesful to put all the pieces together. However today I did it! B) . Here I made a new post. All comments and improovements are more than welcome.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1