On Feb 21, 1:57 pm, GArlington <garling...@xxxxxxxxxxxxx> wrote:
> 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#
> <cfloop query="children">
> <cfset result = removeThis(thisID=children.ID) />
</cfloop> <!--- this needs to go here --->
> <!--- And now you can safely delete thisID --->
> <cfquery ...>
> delete from yourTable
> where ID = #arguments.thisID#
> <cfretrun thisResult />