Displaying complex one to many relationship

[Mike Brind]

<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

 

[Anthony Jones]

<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,

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.

 

[Mike Brind]

Mike,

Use a seperate string variable to build up the line of authors.

Gotcha. That would be the simplest thing

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?))

I would normally, but the code I didn't include (because it isn't
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:-

If sLine <> "" Then
s = s & Left(sLine,Len(sLine) - 2)
sLine = ""
End If

Yep - I see. Knock off the final comma and space.

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).

Agreed. In most cases.

Thank you