|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Displaying complex one to many relationship
I want to display a kind of two-tier one-to-many relationship, for which, in the olden days, I would have used a series of nested loops and multiple calls to the db. Here's the trimmed down code so far: <% sql = "SELECT StoryType.StoryType, Articles.ArticleID, Articles.Title, Authors.Initials, Authors.AuthorName FROM StoryType INNER JOIN (Authors INNER JOIN (Articles INNER JOIN ArticleAuthors ON Articles.ArticleID = ArticleAuthors.ArticleID) ON Authors.AuthorID = ArticleAuthors.AuthorID) ON StoryType.StoryTypeID = Articles.StoryTypeID" currentstorytype = "" currentid = "" s = "" set rs = objconnection.execute(sql,,4) arr = rs.getrows() rs.close : set rs = nothing for i = 0 to ubound(arr,2) if arr(0,i) <> currentstorytype then s = s & vbcrlf & vbcrlf & "<h5>" & arr(0,i) & "S</h5>" & vbcrlf currentstorytype = arr(0,i) end if if arr(1,i) <> currentid then s = s & "<p>" & arr(2,i) & "<br />" & vbcrlf currentid = arr(1,i)end if s = s & arr(3,i) & " " & arr(4,i) & ", " next response.write s %> This displays the db content as follows: STORYTYPE Article title Author 1, Author 2, Author 3, Article title Author 1, Author2, STORYTYPE Article title Author 1, Article Title Author 1, Author 2, Author 3, Author 4, This all works fine - except for one thing - I am having a real mental block when it comes to identifying where the author list for each title ends, so that I don't display a comma, and can close that section off with a "</p>". Suggestions, anyone? Cheers -- Mike Brind
Show quote
"Mike Brind" <paxton***@hotmail.com> wrote in message Mike,news:1142027963.255064.149830@j52g2000cwj.googlegroups.com... > <Mental block> > > I want to display a kind of two-tier one-to-many relationship, for > which, in the olden days, I would have used a series of nested loops > and multiple calls to the db. > > Here's the trimmed down code so far: > > <% > sql = "SELECT StoryType.StoryType, Articles.ArticleID, Articles.Title, > Authors.Initials, Authors.AuthorName FROM StoryType INNER JOIN (Authors > INNER JOIN (Articles INNER JOIN ArticleAuthors ON Articles.ArticleID = > ArticleAuthors.ArticleID) ON Authors.AuthorID = > ArticleAuthors.AuthorID) ON StoryType.StoryTypeID = > Articles.StoryTypeID" > > currentstorytype = "" > currentid = "" > s = "" > set rs = objconnection.execute(sql,,4) > arr = rs.getrows() > rs.close : set rs = nothing > for i = 0 to ubound(arr,2) > if arr(0,i) <> currentstorytype then > s = s & vbcrlf & vbcrlf & "<h5>" & arr(0,i) & "S</h5>" & vbcrlf > currentstorytype = arr(0,i) > end if > if arr(1,i) <> currentid then > s = s & "<p>" & arr(2,i) & "<br />" & vbcrlf > currentid = arr(1,i) > end if > s = s & arr(3,i) & " " & arr(4,i) & ", " > next > response.write s > %> > > This displays the db content as follows: > > STORYTYPE > Article title > Author 1, Author 2, Author 3, > > Article title > Author 1, Author2, > > STORYTYPE > Article title > Author 1, > > Article Title > Author 1, Author 2, Author 3, Author 4, > > This all works fine - except for one thing - I am having a real mental > block when it comes to identifying where the author list for each title > ends, so that I don't display a comma, and can close that section off > with a "</p>". > > Suggestions, anyone? > > Cheers > > -- > Mike Brind > Use a seperate string variable to build up the line of authors. When you detect the rowset has move on to the next article append the line to the string containing the html so far (or send directly to Response.Write (why aren't you doing that?)) using:- If sLine <> "" Then s = s & Left(sLine,Len(sLine) - 2) sLine = "" End If You could use a similar approach to building up the set of articles under storytypes (all this string concatenation isn't good for VBScripts health). Anthony. Anthony Jones wrote:
Show quote > "Mike Brind" <paxton***@hotmail.com> wrote in message Gotcha. That would be the simplest thing :-)> news:1142027963.255064.149830@j52g2000cwj.googlegroups.com... > > <Mental block> > > > > I want to display a kind of two-tier one-to-many relationship, for > > which, in the olden days, I would have used a series of nested loops > > and multiple calls to the db. > > > > Here's the trimmed down code so far: > > > > <% > > sql = "SELECT StoryType.StoryType, Articles.ArticleID, Articles.Title, > > Authors.Initials, Authors.AuthorName FROM StoryType INNER JOIN (Authors > > INNER JOIN (Articles INNER JOIN ArticleAuthors ON Articles.ArticleID = > > ArticleAuthors.ArticleID) ON Authors.AuthorID = > > ArticleAuthors.AuthorID) ON StoryType.StoryTypeID = > > Articles.StoryTypeID" > > > > currentstorytype = "" > > currentid = "" > > s = "" > > set rs = objconnection.execute(sql,,4) > > arr = rs.getrows() > > rs.close : set rs = nothing > > for i = 0 to ubound(arr,2) > > if arr(0,i) <> currentstorytype then > > s = s & vbcrlf & vbcrlf & "<h5>" & arr(0,i) & "S</h5>" & vbcrlf > > currentstorytype = arr(0,i) > > end if > > if arr(1,i) <> currentid then > > s = s & "<p>" & arr(2,i) & "<br />" & vbcrlf > > currentid = arr(1,i) > > end if > > s = s & arr(3,i) & " " & arr(4,i) & ", " > > next > > response.write s > > %> > > > > This displays the db content as follows: > > > > STORYTYPE > > Article title > > Author 1, Author 2, Author 3, > > > > Article title > > Author 1, Author2, > > > > STORYTYPE > > Article title > > Author 1, > > > > Article Title > > Author 1, Author 2, Author 3, Author 4, > > > > This all works fine - except for one thing - I am having a real mental > > block when it comes to identifying where the author list for each title > > ends, so that I don't display a comma, and can close that section off > > with a "</p>". > > > > Suggestions, anyone? > > > > Cheers > > > > -- > > Mike Brind > > > > Mike, > > Use a seperate string variable to build up the line of authors. When you > detect the rowset has move on to the next article append the line to the I would normally, but the code I didn't include (because it isn't> string containing the html so far (or send directly to Response.Write (why > aren't you doing that?)) germane to my headache) would show that this is part of a function that rebuilds an application level variable. I cache this because the value of it will only ever change about once every two or three months (or when I have to make the odd alteration in between). using:- > Yep - I see. Knock off the final comma and space.> If sLine <> "" Then > s = s & Left(sLine,Len(sLine) - 2) > sLine = "" > End If > > You could use a similar approach to building up the set of articles under Agreed. In most cases.> storytypes (all this string concatenation isn't good for VBScripts health). > Thank you -- Mike Brind  |
|||||||||||||||||||||||
Other interesting topics