469,356 Members | 2,459 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,356 developers. It's quick & easy.

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

..:: 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!
Oct 21 '05 #1
2 2608
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/micro...ee952f05404f03

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.
Oct 21 '05 #2
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 :o)

--
Regards

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

Keeping it FREE!

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:u4**************@TK2MSFTNGP12.phx.gbl...
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/micro.../browse_frm/th
read/d3f5a7f39ed08d4f/b8eee952f05404f0?lnk=st&q=%22Bob+Barrows%22+recurs ive+
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.

Oct 21 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by | last post: by
7 posts views Thread by aurora | last post: by
9 posts views Thread by Bill Borg | last post: by
9 posts views Thread by Csaba Gabor | last post: by
41 posts views Thread by Harry | last post: by
27 posts views Thread by Mikhail Kovalev | last post: by
3 posts views Thread by from.future.import | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.