|
|
On Feb 21, 1:08 pm, "Kapitaine" <webforumsu...@xxxxxxxxxxxxxx> wrote:
> Hi,
>
> I have a basic table setup with the very common "parent" and "ID"
> relationship
> scenario.
>
> What I want to be able to do is provide a CFC function/method a ID and remove
> all the items that have the ID I give it as their Parent ID.
>
> The thing is, doing this to one level is fine - I just delete all records
> where the Parent ID is equal to the ID I gave the function, but then I'm left
> with records associated with those records I just deleted - they are then of
> course redundant and need removing to clean things up - otherwise my database
> could become untidy and overblown (affecting other queries).
>
> Assuming my table columns look like the following - what's the best way to do
> this?
>
> Columns - ID, NAME, PARENT_ID
>
> Example Records:
>
> 1 Home 0
> 2 About 0
> 3 Company 2
> 4 Account 2
> 5 Login 4
> 6 Contact 0
> 7 History 3
>
> As you can see, if I want to remove ID 2 (About) then I need to remove the
> Children ID 3 (Company) and ID 4 (Account). Not only this, but I also need to
> remove their children, and their childrens children and so forth endlessly
> until we hit nothing.
>
> I know this would require some sort of function to do this that then uses
> itself moving up a level each time (called Recursion I guess) but I'm just not
> technical enough to acheive it just yet.
>
> Can anyone be willing to help with this? I want to become a better coder and
> think it would be best to learn from you guys rather than away at it
> not really knowing if what I'm doing is actually the best way to do it (I want
> the my code to always be the best it can be :-) )
>
> Thanks for reading and I hope you can help me.
>
> Thanks,
> Mickey.
>
> PS - I'm using CF8 with Access (will switch to MySQL for live dev).
<cffunction name="removeThis" >
<cfargument name="thisID" ... />
<cfset var thisResult = true />
<cfset var children = "" />
<cfquery name="children" ...>
select * from yourTable
where parentID = #arguments.thisID#
</cfquery>
<cfloop query="children">
<cfset result = removeThis(thisID=children.ID) />
<!--- And now you can safely delete thisID --->
<cfquery ...>
delete from yourTable
where ID = #arguments.thisID#
</cfquery>
<cfretrun thisResult />
</cffunction>
|
|