Database - recursive function calls - how would you do this?

Discussion in 'ASP General' started by Steven Burn, Oct 21, 2005.

  1. Steven Burn

    Steven Burn Guest

    ..:: The Specs:

    MS Access 2000 (host charges extra for SQL/MySQL)
    MS Windows Server 2003 (prod) / MS XP SP1 (dev)

    ..:: The setup:

    The database has been setup with two tables;

    tblDownloads
    tblCatagories

    Each "download" consists of a catagory field that corresponds to fldID in
    tblCatagories. Each catagory, where the catagory is a parent, has a parent
    field of value: 0 and each child, has a field with a value corresponding to
    the fldID of it's parent. So for example;

    **********************
    fldID / fldName / fldParent
    **********************

    1 Multimedia 0
    2 Audio 1
    3 Video 1

    Now, each child can have children of it's own (I've not put a drill level on
    it as the app I'm writing is for a friend and he's specifically asked for
    "no limit" on how low it can go), so for example (the following represents
    how tblCatagories is setup);

    **********************
    fldID / fldName / fldParent
    **********************

    1 Multimedia 0
    2 Audio 1
    3 Video 1
    4 MP3 Players 2
    5 Skinnable 4
    6 Add-ons 4
    7 Misc 6

    ..:: The problem:

    What I'm basically needing to do, is count the downloads in the current
    catagory, so for example, in the case where the selected catagory was MP3
    Players, it would run through "MP3 Players", "Skinnable", "Add-ons" and
    "Misc" and count each download from tblDownloads, that corresponds to one of
    those catagories. In the case where the catagory selected was null (i.e.
    default downloads page), it would show all downloads for Multimedia and it's
    children.

    The code I've written will get the PC (parent catagory) and it's children,
    but will not then progress to get the childrens children.

    I search several places for recursive queries (or alternate ways to do what
    I am needing) and unfortunately, I've thus far been unsuccessful in finding
    one that works for my situation (they all either assume to be working with
    SQL, or that there's a limit on how far down it can go, or uses INNER JOIN
    (assumes the values are in two different tables, which is not the case
    here)). Had this been VB, I could have simply used a GoTo [LABEL] where
    child catagories were present, unfortunately ASP does not support this.

    No error is returned to the server's logs or the event viewer, and no error
    is returned to the client browser (client debug is turned on and the browser
    is set to display the real error, rather than the horrid 500 error), the
    page (and subequently, the server) stops responding. I've been trying to
    figure this one out for just over 3 days now and am at my wits end (I know
    recursive queries work as I've used them before, so why isn't it in this
    case?).

    ..:: The code:

    The functions involved are;

    IsParentCatagory (returns boolean value)
    HasChildren (returns boolean value)
    GetChildren (returns long)
    GetParent (returns long)
    GetDownloadCount (returns long)

    The function thats called to get the children is below. What I tried doing
    was, looping through the children returned (after closing the existing rs of
    course), and re-calling the function for each child (to then get the
    childrens children and so on). Unfortunately, this does not apparently want
    to work.

    Sub CountEm(lCatID)
    '// Init call for current catagory
    GetChildren lCatID
    '// Debugging (checked that there was actually something to process)
    '// Response.Write "sKids: " & sKids: Response.End
    arrChildren = Split(sKids, ",")
    For x = LBound(arrChildren) To UBound(arrChildren)
    '// Tried without the Call aswell, just incase
    Call GetChildren(arrChildren(x))
    Next
    End Sub

    Function GetChildren(lID)
    Set oDB = Connect(DB_Downloads, 1)
    sSQL = "Select fldID, fldParent From tblCatagories Where fldParent = " &
    lID
    Set rRst = oDB.Execute(sSQL)
    If rRst.EOF Then rRst.Close: GetChildren = 0: Exit Function
    Do Until rRst.EOF
    '// The following If/End If is on one line,
    '// just re-wrote it here incase of linewrap
    If Len(sKids) = 0 Then
    sKids = rRst("fldID")
    Else
    sKids = sKids & "," & rRst("fldID")
    End If
    rRst.MoveNext
    Loop
    rRst.Close: Set rRst = Nothing
    '// Once returned, a Do/Loop is performed to run through
    '// the children and count their downloads using GetDownloadCount
    GetChildren = sKids
    End Function

    What I'm basically wanting to do is recurse to the lowest child catagories
    (where HasChildren = False), regardless of how far down that actually is,
    and return the download count for each child (upto and including the top
    level parent). phpBB does this with ease for their downloads catagory, so
    I'm almost positive this is relatively simple to do, but for the life of me,
    I can't figure out where I am going wrong (and can't understand PHP or would
    have took a look at their coding for hints). As an FYI, I also tried making
    the above function a sub (since sKids is a public string anyway) and that
    didn't help either.

    Does anyone have any suggestions please?

    --
    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!
     
    Steven Burn, Oct 21, 2005
    #1
    1. Advertising

  2. Steven Burn wrote:
    > .:: The Specs:
    >
    > MS Access 2000 (host charges extra for SQL/MySQL)
    > MS Windows Server 2003 (prod) / MS XP SP1 (dev)
    >
    > .:: The setup:
    >
    > The database has been setup with two tables;
    >
    > tblDownloads
    > tblCatagories
    >
    > Each "download" consists of a catagory field that corresponds to
    > fldID in tblCatagories. Each catagory, where the catagory is a
    > parent, has a parent field of value: 0 and each child, has a field
    > with a value corresponding to the fldID of it's parent. So for
    > example;
    >
    > **********************
    > fldID / fldName / fldParent
    > **********************
    >
    > 1 Multimedia 0
    > 2 Audio 1
    > 3 Video 1
    >
    > Now, each child can have children of it's own (I've not put a drill
    > level on it as the app I'm writing is for a friend and he's
    > specifically asked for "no limit" on how low it can go), so for
    > example (the following represents how tblCatagories is setup);


    http://groups.google.com/group/micr...ecursive query&rnum=1&hl=en#b8eee952f05404f03

    Nested Sets is your answer.

    Bob Barrows
    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
     
    Bob Barrows [MVP], Oct 21, 2005
    #2
    1. Advertising

  3. Steven Burn

    Steven Burn Guest

    Nice one Bob, thankyou ;o)

    Seeing how simple the change to the query required was, I am now going to
    proceed to kick myself ....

    New query:

    sSQL = "Select fldID, fldParent From tblCatagories Where fldParent = " &
    lID & " OR fldParent IN (Select fldID from tblCatagories Where fldParent = "
    & lID & ")"

    Works like a charm :eek:)

    --
    Regards

    Steven Burn
    Ur I.T. Mate Group
    www.it-mate.co.uk

    Keeping it FREE!

    "Bob Barrows [MVP]" <> wrote in message
    news:...
    > Steven Burn wrote:
    > > .:: The Specs:
    > >
    > > MS Access 2000 (host charges extra for SQL/MySQL)
    > > MS Windows Server 2003 (prod) / MS XP SP1 (dev)
    > >
    > > .:: The setup:
    > >
    > > The database has been setup with two tables;
    > >
    > > tblDownloads
    > > tblCatagories
    > >
    > > Each "download" consists of a catagory field that corresponds to
    > > fldID in tblCatagories. Each catagory, where the catagory is a
    > > parent, has a parent field of value: 0 and each child, has a field
    > > with a value corresponding to the fldID of it's parent. So for
    > > example;
    > >
    > > **********************
    > > fldID / fldName / fldParent
    > > **********************
    > >
    > > 1 Multimedia 0
    > > 2 Audio 1
    > > 3 Video 1
    > >
    > > Now, each child can have children of it's own (I've not put a drill
    > > level on it as the app I'm writing is for a friend and he's
    > > specifically asked for "no limit" on how low it can go), so for
    > > example (the following represents how tblCatagories is setup);

    >
    >

    http://groups.google.com/group/microsoft.public.access.queries/browse_frm/th
    read/d3f5a7f39ed08d4f/b8eee952f05404f0?lnk=st&q=%22Bob+Barrows%22+recursive+
    query&rnum=1&hl=en#b8eee952f05404f03
    >
    > Nested Sets is your answer.
    >
    > Bob Barrows
    > --
    > Microsoft MVP -- ASP/ASP.NET
    > Please reply to the newsgroup. The email account listed in my From
    > header is my spam trap, so I don't check it very often. You will get a
    > quicker response by posting to the newsgroup.
    >
    >
     
    Steven Burn, Oct 21, 2005
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. streamkid
    Replies:
    6
    Views:
    373
    streamkid
    Feb 24, 2007
  2. Replies:
    0
    Views:
    1,190
  3. n00m
    Replies:
    12
    Views:
    1,133
  4. Alexander
    Replies:
    620
    Views:
    8,633
    Seebs
    Nov 9, 2010
  5. Bob
    Replies:
    5
    Views:
    279
Loading...

Share This Page