| Groups | Blog | Home |
inetserver asp db : Need help with recursively getting data from database
I'm trying to make a simple database that stores notes in categories. Notes can have multiple categories and categories can have other categories as subcategories. A web page will list a category and any subcategories and notes under it. Seems simple but when I try to implement it my head starts spinning... Here is my database structure: category id name expand note id text note_categories note_id category_id subcategories child_id parent_id Here's what I'm trying to do on a webpage: Find all of categories in category that have no parent in subcategories For each such category [LOOP1], list the name of the category if the category is has expand = true, list the name of subcategories [REPEAT LOOP1 FOR EACH, etc...] list notes matching that category in note_categories } } I got it working without the recursion part using nested loops, then I read aspfaq.com's #2241 and came up with the table structure above. I won't utter my crappy nested loop code here... how do I do it properly? Any help would be appreciated. dragonhunter
[quoted text, click to view]
"Dragonhunter" <dragonhunter97@yahoo.com> wrote in message news:407CB995.9DF001C0@yahoo.com... > Hello all, > > I'm trying to make a simple database that stores notes in categories. > Notes can have multiple categories and categories can have other > categories as subcategories. A web page will list a category and any > subcategories and notes under it. Seems simple but when I try to > implement it my head starts spinning... > > Here is my database structure: > > category > id > name > expand > note > id > text > note_categories > note_id > category_id > subcategories > child_id > parent_id > > Here's what I'm trying to do on a webpage: > > Find all of categories in category that have no parent in subcategories > > For each such category [LOOP1], > list the name of the category > if the category is has expand = true, > list the name of subcategories [REPEAT LOOP1 FOR EACH, etc...] > list notes matching that category in note_categories > } > } > > I got it working without the recursion part using nested loops, then I > read aspfaq.com's #2241 and came up with the table structure above. I > won't utter my crappy nested loop code here... how do I do it properly? > > Any help would be appreciated. > > dragonhunter http://groups.google.com/groups?threadm=ej7OlMSGEHA.3856%40TK2MSFTNGP12.phx.gbl
[quoted text, click to view]
Dragonhunter wrote: Can you provide a few rows of sample data for each table (in tabular form)> Hello all, > > I'm trying to make a simple database that stores notes in categories. > Notes can have multiple categories and categories can have other > categories as subcategories. A web page will list a category and any > subcategories and notes under it. Seems simple but when I try to > implement it my head starts spinning... > and the desired result? Descriptions can only go so far, plus it will help us get to the correct solution if we are discussing the data that you will be using. 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.
[quoted text, click to view]
"Bob Barrows [MVP]" wrote: > Can you provide a few rows of sample data for each table (in tabular form) > and the desired result? Descriptions can only go so far, plus it will help > us get to the correct solution if we are discussing the data that you will > be using. > > Bob Barrows Sure, here is my database and an example of the web page I want to create to output the data. I structured the database to normalize after being explained the concept on this group (thanks!). So, how do I do this recursive displaying of data properly using ASP? I think I'm pretty stuck on the SQL..., particularly when I try to do the "join" method instead of nested loops. Any help would be appreciated! Thanks, Dragonhunter category id--name------------------expand 1 Favorite soundtracks true 2 Favorite music true 3 Pets false 4 Favorite quotes true 5 Ordered soundtracks true note id--text 1 Star Wars 2 Indiana Jones 3 Fluffy the cat 4 Goldfish 1 5 "To make an omelette you have to break some eggs" 6 "Nested loops are the root of all evil" 7 Batman 8 Predator 9 "it takes one to know one!" 10 Les miserables note_categories note_id--category_id 1 1 1 5 <--this is a note in two categories 2 1 3 3 4 3 5 4 6 4 7 1 8 1 9 4 10 1 7 5 <--this is a note in two categories Subcategories child_id--parent_id 1 2 Webpage============================================== + is expandable to show things under it - means un-expand it (and store +/- state in a database) -Favorite music -Favorite soundtracks Star Wars Indiana Jones Batman Predator Les miserables +Pets <-- none show because "expand" field is false -Favorite quotes "To make an omelette you have to break some eggs" "Nested loops are the root of all evil" "it takes one to know one!" -Ordered soundtracks Star Wars Batman
See below:
[quoted text, click to view] Dragonhunter wrote: > "Bob Barrows [MVP]" wrote: > >> Can you provide a few rows of sample data for each table (in tabular >> form) and the desired result? Descriptions can only go so far, plus >> it will help us get to the correct solution if we are discussing the >> data that you will be using. >> >> Bob Barrows > > Sure, here is my database and an example of the web page I want to > create to output the data. I structured the database to normalize > after being explained the concept on this group (thanks!). So, how > do I do this recursive displaying of data properly using ASP? I > think I'm pretty stuck on the SQL..., particularly when I try to do > the "join" method instead of nested loops. Any help would be > appreciated! > > Thanks, > Dragonhunter > > I would probably have added a parent_id column to this table. The subcategories table is really not needed. The expand field is really not needed either, although it will make the subsequent coding easier. I'm not sure this coding ease is worth the headaches that guaranteeing this field will contain the correct value will cause. Like this [quoted text, click to view] > category > id--name--------------------parent_id > 1 Favorite soundtracks 2 > 2 Favorite music > 3 Pets > 4 Favorite quotes > 5 Ordered soundtracks However, I will assume you do not make this change [quoted text, click to view] > > note > id--text > 1 Star Wars > 2 Indiana Jones > 3 Fluffy the cat > 4 Goldfish 1 > 5 "To make an omelette you have to break some eggs" > 6 "Nested loops are the root of all evil" > 7 Batman > 8 Predator > 9 "it takes one to know one!" > 10 Les miserables > > note_categories > note_id--category_id > 1 1 > 1 5 <--this is a note in two categories > 2 1 > 3 3 > 4 3 > 5 4 > 6 4 > 7 1 > 8 1 > 9 4 > 10 1 > 7 5 <--this is a note in two categories > > Subcategories > child_id--parent_id > 1 2 > > > Webpage============================================== > + is expandable to show things under it > - means un-expand it (and store +/- state in a database) > > > -Favorite music > -Favorite soundtracks > Star Wars > Indiana Jones > Batman > Predator > Les miserables > +Pets <-- none show because "expand" field is false > -Favorite quotes > "To make an omelette you have to break some eggs" > "Nested loops are the root of all evil" > "it takes one to know one!" > -Ordered soundtracks > Star Wars > Batman OK. I will take care of showing you how to do the nesting. You will need to take care of the client-side coding needed to expand and collapse the nodes. You can ask about that in a client-side code newsgroup (newsgroups containing "dhtml" in their title or one of the .scripting newsgroups). I will put the + and - characters in spans whose onclick events can be used to expand or collapse a node. To start, let's order the categories by parent_id, then child id, so the master categories will be first: SELECT c.id, iif(sc.parent_id is null,0,sc.parent_id), c.name, c.expand FROM category AS c LEFT JOIN subcategories AS sc ON c.id = sc.child_id ORDER BY sc.parent_id, c.id; produces this resultset: id parent_id name expand 2 0 Favorite music -1 3 0 Pets 0 4 0 Favorite quotes -1 5 0 Ordered soundtracks -1 1 2 Favorite soundtracks -1 Now let's get the notes: SELECT n.id, c.id, n.text, null AS Expr1 FROM (category AS c INNER JOIN note_categories AS nc ON c.id = nc.category_id) INNER JOIN [note] AS n ON nc.note_id = n.id ORDER BY c.id, n.id; which gives us this result: n.id c.id text Expr1 1 1 Star Wars 2 1 Indiana Jones 7 1 Batman 8 1 Predator 10 1 Les miserables 3 3 Fluffy the cat 4 3 Goldfish 1 5 4 "To make an omelette you have to break some eggs" 6 4 "Nested loops are the root of all evil" 9 4 "it takes one to know one!" 1 5 Star Wars 7 5 Batman Let's union these results together: SELECT c.id, iif(sc.parent_id is null,0,sc.parent_id) as parent_id, c.name, c.expand, "cat" As Type FROM category AS c LEFT JOIN subcategories AS sc ON c.id = sc.child_id UNION ALL SELECT n.id, c.id, n.text, Null, "note" FROM (category AS c INNER JOIN note_categories AS nc ON c.id = nc.category_id) INNER JOIN [note] AS n ON nc.note_id = n.id ORDER BY parent_id, id; giving these results: qTree id parent_id name expand type 2 0 Favorite music True cat 3 0 Pets False cat 4 0 Favorite quotes True cat 5 0 Ordered soundtracks True cat 1 2 Favorite soundtracks True cat 1 1 Star Wars note 2 1 Indiana Jones note 7 1 Batman note 8 1 Predator note 10 1 Les miserables note 3 3 Fluffy the cat note 4 3 Goldfish 1 note 5 4 "To make an omelette you have to break some eggs" note 6 4 "Nested loops are the root of all evil" note 9 4 "it takes one to know one!" note 1 5 Star Wars note 7 5 Batman note Copy the above sql into the SQL View window of an Access Query Builder and save it as qTree after running it to verify the results are correct. In your asp page, do this (as a bonus, I included the client-side code to make the nodes expand and collapse): <% dim cn, rs, sSQL set cn=createobject("adodb.connection") cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Server.MapPath("db7.mdb") set rs=CreateObject("adodb.recordset") rs.CursorLocation = 3 'adUseClient rs.LockType = 1 'adLockReadOnly cn.qTree rs 'disconnect the recordset: set rs.ActiveConnection=nothing cn.Close: set cn = nothing if rs.EOF then Response.Write "no records returned" else 'stop WriteChildDivs rs.Clone,rs(1).Value,"cat",-10 rs.Close:set rs = nothing end if Sub WriteChildDivs(pRS,pID,byval pType,pIndent) dim bNextLvlVisible pRS.Filter="parent_id=" & pID & " and type='" & pType & "'" if pRS.eof then ptype = "note" pRS.Filter="parent_id=" & pID & " and type='" & pType & "'" end if do until pRS.EOF Response.Write "<div style=""margin-left:" & _ pIndent + 10 & "px"">" select case pRS(3).value case true Response.Write "<span onclick=""handleclick();""" & _ " style=""cursor:hand"">" Response.Write "-</span>" bNextLvlVisible=true case false
[quoted text, click to view]
Bob Barrows wrote: > See below: <snip> > In your asp page, do this (as a bonus, I included the client-side > code to make the nodes expand and collapse): > <% > dim cn, rs, sSQL > set cn=createobject("adodb.connection") > cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _ > "Data Source=" & Server.MapPath("db7.mdb") Of course, you would substitute your database's path and name here ... -- 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.
[quoted text, click to view] "Dragonhunter" <dragonhunter97@yahoo.com> wrote in message news:407CB995.9DF001C0@yahoo.com... > Hello all, > > I'm trying to make a simple database that stores notes in categories. > Notes can have multiple categories and categories can have other > categories as subcategories. A web page will list a category and any > subcategories and notes under it. Seems simple but when I try to > implement it my head starts spinning... Hiya, I know my post is 2 days after you had the problem, but in the event that you didn't find a solution, I'll post one here [note: I haven't tested this code, but it looks like it should work... the SQL might need a little tweaking though. You might also find it helpful to copy this to notepad or similar so you can take the word wrapping off] First, for the database structure: CATEGORY ID name expand relatesTO NOTES ID relatesTO NOTESTEXT ID TEXT Explanation: CATEGORY: id: this is an autonumber identifier name: this is the name of the category expand: your true/false, if this category is expanded relatesTO: for top level categories this is 0, for all other categories this is the ID for that category Example Data: 1,Top Level Category 1,true,0 2,Top Level Category 2,true,0 3,Sub Category A,false,1 4,Sub Category B,true,1 5,Sub Category i,false,4 6,Sub Category C,false,2 (you get the idea... the RELATESTO column tells it what category it belongs under) NOTES id: this is a NON-unique identifier relatesTO: what category.ID this note is displayed next to/under NOTESTEXT id: this ID links to the ID under notes (since the text can be repeated you aren't including the text in the NOTES table) text: this is the text for the ID Example Data: NOTES: 1,1 1,2 2,3 3,5 NOTESTEXT 1,This is a top level category 2,This is Sub Category A 3,This is Sub-Sub Category i For the code: (pseudocode at the bottom) Set oRs = Server.CreateObject("ADODB.Recordset") Set oConn = Server.CreateObject("ADODB.Connection") oConn.Open "-- your database connection string --" sub GetMoreCategories(vGMC) ' create database object ' this has to be created here for recursion to work dim objGMC set objGMC = Server.CreateObject("ADODB.Recordset") ' open database object, we are going to get the data for any items that relate to the current category objGMC.Open "SELECT cc.id,cc.name,cc.expand,cc.relatesTO,nt.text FROM category cc JOIN notes nn ON cc.id=nn.relatesTO JOIN notestext nt ON nn.id=nt.id WHERE relatesTO=" & vGMC, oConn ' loop through the recordset do while not objGMC.EOF response.write "<br>" & objGMC("name") & " NOTE: " & objGMC("text") ' check: do we need to if objGMC("expand")=true then ' expanding, so do the recursion thing... call GetMoreCategories(objGMC("id")) end if objGMC.movenext loop objGMC.close end sub ' main body: find all categories that relate to 0 oRs.Open "SELECT cc.id,cc.name,cc.expand,cc.relatesTO,nt.text FROM category cc JOIN notes nn ON cc.id=nn.relatesTO JOIN notestext nt ON nn.id=nt.id WHERE relatesTO=0", oConn ' loop through the recordset do while not oRs.EOF response.write "<br>" & oRs("name") & " NOTE: " & oRs("text") ' check: do we need to if oRs("expand")=true then ' expanding, so do the recursion thing... call GetMoreCategories(oRs("id")) end if oRs.movenext loop oRs.close Basically, in pseudocode, what we are doing: - create global vairables for oRs and oConn - LOOP - opening the category table and getting all items that relateTO 0 (top level categories) and any notes associated with that category - write the name of the category out - check: does "EXPAND" = true? If yes then call the subroutine "GetMoreCategories" and pass the category ID - REPEAT LOOP until there are no more categories that relateTO 0 SUBROUTINE: GetMoreCategories - accept the passed CategoryID and store it in the variable vGMC - create local variable for objGMC - LOOP - opening the category table and getting all items that relateTO the category ID passed to vGMC and any notes associated with that category - write the name of the category out - check: does "EXPAND" = true? If yes then call the subroutine "GetMoreCategories" and pass the category ID [RECURSION] - REPEAT LOOP until there are no more categories that relateTO the category ID passed to vGMC Hope thats some help Clint
Bob,
The code works perfectly! Thank you! Somewhat surprisingly, I was able to make the change you suggested, destroying the subcategories table and instead giving each category a parent (and getting it to work again!) I sent you an email btw... Dragonhunter [quoted text, click to view] Bob Barrows wrote:
> See below: > > Dragonhunter wrote: > > "Bob Barrows [MVP]" wrote: > > > >> Can you provide a few rows of sample data for each table (in tabular > >> form) and the desired result? Descriptions can only go so far, plus > >> it will help us get to the correct solution if we are discussing the > >> data that you will be using. > >> > >> Bob Barrows > > > > Sure, here is my database and an example of the web page I want to > > create to output the data. I structured the database to normalize > > after being explained the concept on this group (thanks!). So, how > > do I do this recursive displaying of data properly using ASP? I > > think I'm pretty stuck on the SQL..., particularly when I try to do > > the "join" method instead of nested loops. Any help would be > > appreciated! > > > > Thanks, > > Dragonhunter > > > > > > I would probably have added a parent_id column to this table. The > subcategories table is really not needed. The expand field is really not > needed either, although it will make the subsequent coding easier. I'm not > sure this coding ease is worth the headaches that guaranteeing this field > will contain the correct value will cause. > Like this > > > category > > id--name--------------------parent_id > > 1 Favorite soundtracks 2 > > 2 Favorite music > > 3 Pets > > 4 Favorite quotes > > 5 Ordered soundtracks > > However, I will assume you do not make this change > > > > > note > > id--text > > 1 Star Wars > > 2 Indiana Jones > > 3 Fluffy the cat > > 4 Goldfish 1 > > 5 "To make an omelette you have to break some eggs" > > 6 "Nested loops are the root of all evil" > > 7 Batman > > 8 Predator > > 9 "it takes one to know one!" > > 10 Les miserables > > > > note_categories > > note_id--category_id > > 1 1 > > 1 5 <--this is a note in two categories > > 2 1 > > 3 3 > > 4 3 > > 5 4 > > 6 4 > > 7 1 > > 8 1 > > 9 4 > > 10 1 > > 7 5 <--this is a note in two categories > > > > Subcategories > > child_id--parent_id > > 1 2 > > > > > > Webpage============================================== > > + is expandable to show things under it > > - means un-expand it (and store +/- state in a database) > > > > > > -Favorite music > > -Favorite soundtracks > > Star Wars > > Indiana Jones > > Batman > > Predator > > Les miserables > > +Pets <-- none show because "expand" field is false > > -Favorite quotes > > "To make an omelette you have to break some eggs" > > "Nested loops are the root of all evil" > > "it takes one to know one!" > > -Ordered soundtracks > > Star Wars > > Batman > > OK. I will take care of showing you how to do the nesting. You will need to > take care of the client-side coding needed to expand and collapse the nodes. > You can ask about that in a client-side code newsgroup (newsgroups > containing "dhtml" in their title or one of the .scripting newsgroups). I > will put the + and - characters in spans whose onclick events can be used to > expand or collapse a node. > > To start, let's order the categories by parent_id, then child id, so the > master categories will be first: > SELECT c.id, iif(sc.parent_id is null,0,sc.parent_id), c.name, c.expand > FROM category AS c LEFT JOIN subcategories AS sc ON c.id = sc.child_id > ORDER BY sc.parent_id, c.id; > produces this resultset: > > id parent_id name expand > 2 0 Favorite music -1 > 3 0 Pets 0 > 4 0 Favorite quotes -1 > 5 0 Ordered soundtracks -1 > 1 2 Favorite soundtracks -1 > > Now let's get the notes: > SELECT n.id, c.id, n.text, null AS Expr1 > FROM (category AS c > INNER JOIN note_categories AS nc ON c.id = nc.category_id) > INNER JOIN [note] AS n ON nc.note_id = n.id > ORDER BY c.id, n.id; > which gives us this result: > n.id c.id text > Expr1 > 1 1 Star Wars > 2 1 Indiana Jones > 7 1 Batman > 8 1 Predator > 10 1 Les miserables > 3 3 Fluffy the cat > 4 3 Goldfish 1 > 5 4 "To make an omelette you have to break some eggs" > 6 4 "Nested loops are the root of all evil" > 9 4 "it takes one to know one!" > 1 5 Star Wars > 7 5 Batman > > Let's union these results together: > > SELECT c.id, iif(sc.parent_id is null,0,sc.parent_id) as parent_id, > c.name, c.expand, "cat" As Type > FROM category AS c LEFT JOIN subcategories AS sc ON c.id = sc.child_id > UNION ALL SELECT n.id, c.id, n.text, Null, "note" > FROM (category AS c > INNER JOIN note_categories AS nc ON c.id = nc.category_id) > INNER JOIN [note] AS n ON nc.note_id = n.id > ORDER BY parent_id, id; > > giving these results: > qTree > id parent_id name > expand type > 2 0 Favorite music > True cat > 3 0 Pets > False cat > 4 0 Favorite quotes > True cat > 5 0 Ordered soundtracks > True cat > 1 2 Favorite soundtracks > True cat > 1 1 Star Wars > note > 2 1 Indiana Jones > note > 7 1 Batman > note > 8 1 Predator > note > 10 1 Les miserables > note > 3 3 Fluffy the cat > note > 4 3 Goldfish 1 > note > 5 4 "To make an omelette you have to break some eggs" > note > 6 4 "Nested loops are the root of all evil" > note > 9 4 "it takes one to know one!" > note > 1 5 Star Wars > note > 7 5 Batman > note > > Copy the above sql into the SQL View window of an Access Query Builder and > save it as qTree after running it to verify the results are correct. > > In your asp page, do this (as a bonus, I included the client-side code to > make the nodes expand and collapse): > <% > dim cn, rs, sSQL > set cn=createobject("adodb.connection") > cn.open "provider=Microsoft.Jet.OLEDB.4.0;" & _ > "Data Source=" & Server.MapPath("db7.mdb") > > set rs=CreateObject("adodb.recordset") > rs.CursorLocation = 3 'adUseClient > rs.LockType = 1 'adLockReadOnly > cn.qTree rs > 'disconnect the recordset: > set rs.ActiveConnection=nothing > cn.Close: set cn = nothing > > if rs.EOF then
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |