#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2006
    Posts
    13
    Rep Power
    0

    Variables overwritten in recursive functions


    Dear all

    To display a tree structure of productgroups, I developed a recursive funtion. If there are childs available of a productgroup, the function calls itself to append the string that contains the html of the treestructure. But there's a conflict with the query-variable. Here's the thing:

    Every time the function is called a query is done by a parentID. The first query contains of course all the parents in level 1.

    - Item 1
    - Item 2
    - Item 3

    Let's say Item 1 has children, so the function calls itself and fires the same query again to find all children.

    - Child 1
    - Child 2
    - Child 3

    Then, item 2 and 3 do not have children. But when it's done printing all the children of Item 1 in the htmlvariable, the queryvariable is partially overwritten. It does iterate over the right amount of records, but keeps on displaying the information of the child last printed. The result is like so:

    - Item 1
    - Child 1
    - Child 2
    - Child 3
    - Child 3
    - Child 3

    So when it should print Item 2 and Item 3, it just pastes old information of the last row of the last fired query...

    Hope I explained everything understandable. Please, could anyone help out?

    Thank you

    Best regards,
    Maarten
    (Netherlands)

    Code:
    <cffunction name="parentalSelect" access="public" output="true" returntype="string">
    	<cfargument name="idname" type="string" required="true" />
    	<cfargument name="table" type="string" required="true" />
    	<cfargument name="selectname" type="string" required="true" />
    	<cfargument name="parentid" type="numeric" required="false" default="0" />
    	<cfargument name="level" type="numeric" required="false" default="0" />
    	
    	<cfset data = '' /> <!--- to hold the query and to overwrite the old one when going recursive --->
    	<cfset haskids = '' /> <!--- to hold the query to check children --->
    	<cfset nr = 1 /> <!--- to check if the parental option row is odd or even --->
    	
    	<cfif arguments.level EQ 0>
    		<cfset variables.selecthtml = '' /> <!--- to hold the html of the select --->
    		<cfquery name="objData" datasource="#application.datasource#">
    			SELECT
    				[#arguments.idname#] AS id,
    				[parentID],
    				[name]
    			FROM
    				#arguments.table#;
    		</cfquery>
    	</cfif>
    	
    	<cfquery name="objgroup" dbtype="query">
    		SELECT
    			*
    		FROM
    			objData
    		WHERE
    			parentID = <cfqueryparam value="#arguments.parentID#" cfsqltype="cf_sql_integer" />;
    	</cfquery>
    	
    	<!--- setup html of the select --->
    	<cfif arguments.level EQ 0 AND variables.selecthtml EQ ''>
    		<cfset variables.selecthtml = '<select name="' & arguments.selectname & '" id="' & arguments.selectname & '" class="select">' />
    	</cfif>
    	
    	<!--- append data to html of select --->
    	<cfloop query="objgroup">
    		<cfif IsNumeric(objgroup.id)><!--- extra check if record is found because CF does not overwrite old variables --->
    			<cfset levelstring = '' />
    			<cfif arguments.level GT 0>
    				<cfloop index="x" from="1" to="#arguments.level#">
    					<cfset levelstring &= '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' />
    				</cfloop>
    				<cfset levelstring &= '.' />
    			</cfif>
    			
    			<cfif arguments.level EQ 0>
    				<cfif BitAnd( nr, 1 )>
    					<cfset optionclass = 'odd' />
    				<cfelse>
    					<cfset optionclass = 'even' />
    				</cfif>
    				
    				<cfset optionclass &= ' kid' />
    				<cfset nr++ />
    			</cfif>
    			
    			<cfset variables.selecthtml &= '<option class="' & optionclass & '" value="' & objgroup.id & '">' & levelstring & ' ' & objgroup.name & '</option>' />
    			
    			<!--- check if has kids --->
    			<cfquery name="haskids" dbtype="query">
    				SELECT
    					id
    				FROM
    					objData
    				WHERE
    					[parentID] = <cfqueryparam value="#objgroup.id#" cfsqltype="cf_sql_integer" />;
    			</cfquery>
    			
    			<cfif haskids.recordcount GT 0><!--- then it has kids and recursive starts --->
    				<cfset parentalSelect(arguments.idname, arguments.table, arguments.selectname, objgroup.id, arguments.level + 1) />
    			</cfif>
    		</cfif>
    	</cfloop>
    	<cfif arguments.level EQ 0>
    		<cfset variables.selecthtml &= '</select>' />
    	</cfif>
    	
    	<cfif arguments.level EQ 0>
    		<cfreturn variables.selecthtml />
    	</cfif>
    </cffunction>
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2006
    Posts
    13
    Rep Power
    0
    Think I found a solution. Using <cfset VAR variablename = 'blah' />.
    That way the variable is only available in the function itself. Or am I wrong. I am trying this out now. If it works I will post the solution here for others to see. If someone now already knows it'll not work, please respond.

    Thank you!
    Maarten
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2006
    Posts
    13
    Rep Power
    0
    Ok, it worked.

    Here's a basic explanation:

    First

    <cfset var data = '' />

    then

    <cfquery name="data"...

    This way the query is set in the local variable 'data'.

    The code:

    Code:
    <cffunction name="parentalSelect" access="public" output="true" returntype="string">
    	<cfargument name="idname" type="string" required="true" />
    	<cfargument name="table" type="string" required="true" />
    	<cfargument name="selectname" type="string" required="true" />
    	<cfargument name="parentid" type="numeric" required="false" default="0" />
    	<cfargument name="level" type="numeric" required="false" default="0" />
    	
    	<cfset var data = '' /> <!--- to hold the query and to overwrite the old one when going recursive --->
    	<cfset var haskids = '' /> <!--- to hold the query to check children --->
    	<cfset nr = 1 /> <!--- to check if the parental option row is odd or even --->
    	
    	<cfif arguments.level EQ 0>
    		<cfset variables.selecthtml = '' /> <!--- to hold the html of the select --->
    	</cfif>
    	
    	<cfquery name="data" datasource="#application.datasource#">
    		SELECT
    			[#arguments.idname#] AS id,
    			[name]
    		FROM
    			#arguments.table#
    		WHERE
    			[parentID] = <cfqueryparam value="#arguments.parentid#" cfsqltype="cf_sql_integer" />
    			<cfif arguments.parentid EQ 0>
    				OR
    				[parentID] IS NULL
    			</cfif>;
    	</cfquery>
    	
    	<cfif data.recordcount GT 0>
    		<!--- setup html of the select --->
    		<cfif arguments.level EQ 0 AND variables.selecthtml EQ ''>
    			<cfset variables.selecthtml = '<select name="' & arguments.selectname & '" id="' & arguments.selectname & '" class="select">' />
    		</cfif>
    		
    		<!--- append data to html of select --->
    		<cfloop query="data">
    			<cfif IsNumeric(data.id)><!--- extra check if record is found because CF does not overwrite old variables --->
    				<cfset levelstring = '' />
    				<cfif arguments.level GT 0>
    					<cfloop index="x" from="1" to="#arguments.level#">
    						<cfset levelstring &= '&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;' />
    					</cfloop>
    					<cfset levelstring &= '.' />
    				</cfif>
    				
    				<cfif arguments.level EQ 0>
    					<cfif BitAnd( nr, 1 )>
    						<cfset optionclass = 'odd' />
    					<cfelse>
    						<cfset optionclass = 'even' />
    					</cfif>
    					
    					<cfset optionclass &= ' kid' />
    					<cfset nr++ />
    				</cfif>
    				
    				<cfset variables.selecthtml &= '<option class="' & optionclass & '" value="' & data.id & '">' & levelstring & ' ' & data.name & '</option>' />
    				
    				<!--- check if has kids --->
    				<cfquery name="haskids" datasource="#application.datasource#">
    					SELECT
    						[#arguments.idname#]
    					FROM
    						#arguments.table#
    					WHERE
    						[parentID] = <cfqueryparam value="#data.id#" cfsqltype="cf_sql_integer" />;
    				</cfquery>
    				
    				<cfif haskids.recordcount GT 0><!--- then it has kids and recursive starts --->
    					<cfset parentalSelect(arguments.idname, arguments.table, arguments.selectname, data.id, arguments.level + 1) />
    				</cfif>
    			</cfif>
    		</cfloop>
    		<cfif arguments.level EQ 0>
    			<cfset variables.selecthtml &= '</select>' />
    		</cfif>
    	</cfif>
    	
    	<cfif arguments.level EQ 0>
    		<cfreturn variables.selecthtml />
    	</cfif>
    </cffunction>

IMN logo majestic logo threadwatch logo seochat tools logo