Displaying complex one to many relationship

Discussion in 'ASP General' started by Mike Brind, Mar 10, 2006.

  1. Mike Brind

    Mike Brind Guest

    <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 Brind, Mar 10, 2006
    #1
    1. Advertising

  2. "Mike Brind" <> wrote in message
    news:...
    > <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
    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, Mar 10, 2006
    #2
    1. Advertising

  3. Mike Brind

    Mike Brind Guest

    Anthony Jones wrote:
    > "Mike Brind" <> wrote in message
    > news:...
    > > <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.


    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

    --
    Mike Brind
     
    Mike Brind, Mar 10, 2006
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Frank
    Replies:
    3
    Views:
    360
  2. Diane Yocom
    Replies:
    1
    Views:
    634
    =?Utf-8?B?UmljaA==?=
    May 12, 2004
  3. Marco Ippolito
    Replies:
    0
    Views:
    2,623
    Marco Ippolito
    Oct 11, 2004
  4. Mirko
    Replies:
    0
    Views:
    2,356
    Mirko
    Oct 12, 2006
  5. Jim Thomason
    Replies:
    3
    Views:
    177
    Topmind
    Oct 2, 2004
Loading...

Share This Page