..:: 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!
