Infinite parent/child hierarchy problem [Answered]RSS

10 replies

Last post Feb 21, 2011 02:26 PM by HCamper

  • Infinite parent/child hierarchy problem

    Feb 21, 2011 04:46 AM|untiedshoes|LINK

    Hi, hoping someone can help please, been pulling my hair out with part of this. I can get the infinate parent/child relationships working and displaying fine, but for each record, I'm trying to pull out breadcrumbs for each entry, for instance:

    USA
        Florida (USA / Florida)
               Miami (USA / Florida / Miami)
          Illinois  etc
               Chicago etc
                   Rockford etc
          Texas
               Austin
               Dallas
    UK
        Tyne and Wear (UK / Tyne and Wear )
            Newcastle (UK / Tyne and Wear / Newcastle)
            Sunderland (UK / Tyne and Wear / Sunderland )
                Ashbrooke (UK / Tyne and Wear / Sunderland / Ashbrooke)

    At the moment, I can display the data as above (without the breadcrumbs) no problem. I've tried a few things, for instance storing the parent region ID in an array, then printing those, but it only half works, and carries on printing.

     DB Structure:

     

     

    RegionID

    RegionName

    Population

    ParentRegionID

    1

    USA

    276059000

     

    2

    Texas

    20044141

    1

    3

    Illinois

    12128370

    1

    4

    Florida

    15111244

    1

    5

    Austin

    1146050

    2

    6

    Dallas

    3280310

    2

    7

    Miami

    2175634

    4

    8

    Chicago

    8008507

    3

    9

    Rockford

    358640


    Code I'm using:

    CONST REGION_ID = 0
    CONST REGION_NAME = 1
    CONST POPULATION = 2
    CONST PARENT_REGION_ID = 3

    sSQL = "SELECT * FROM Region ORDER BY RegionName "

    oRS.Open sSQL

    Response.Write "<PRE>"
       
    g_arrRows = oRS.GetRows()
    g_nCount = oRS.RecordCount
    oRS.Close


    ' Find the top-level entities in the hierarchy For nIndex = 0 To g_nCount - 1 ' If the Parent is NULL, we have a top level
           If IsNull(g_arrRows(PARENT_REGION_ID, nIndex)) Then
            ' Start looking for children
                    PrintRow g_arrRows(REGION_ID, nIndex), g_arrRows(REGION_NAME, nIndex), g_arrRows( POPULATION, nIndex ), 0
           End If
    Next

    Sub PrintRow(nRegionID, sRegionName, nPopulation, nLevel ) DIM nIndex

             'Write out the current level
             Response.Write String(nLevel, " ") & sRegionName & " - " & nPopulation & vbCRLF
             For nIndex = 0 To g_nCount - 1
            ' IF the row we're looking at has a parent of the region passed in, then delve into its children.
                 If g_arrRows(PARENT_REGION_ID, nIndex) = nRegionID Then
                ' Call our PrintRow function to find the children of this record and so on.
                     PrintRow g_arrRows(REGION_ID, nIndex), g_arrRows(REGION_NAME, nIndex), g_arrRows( POPULATION, nIndex ), nLevel + 1
           End If
             Next

    End Sub

     

    Any help would be greatly appreciated; as said, I've been pulling my hair out on this for the last few days.

    Thanks,

    Craig

  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 05:06 AM|HCamper|LINK

    Hello,

    Two items:

    The first is the ASP.NET / SQL Server Forum is better for answering code questions.

    Please except this comment as lessons learned:

    The second is in your select  * wild card avoid this.

    You have set up a db structure please use it.





    select distinct RegionID,

    RegionName,

    Population,

    ParentRegionID from database where clause

               get the unique items and avoid a canonical results set which is all matches.

    You are wasting database time user time.

    For the reset of the code check the ASP.NET Forums.

    Thank You,

    Martin

    Windows and Linux work Together IT-Pros
    Community Member Award 2011
  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 05:40 AM|untiedshoes|LINK

    Hi, thanks for your reply.

    Yes, I'm aware of using *, but in this instance, as you can see from the rows pulled, I am using all of them, hence using *.

    Second, this is the classic ASP forum, and the question is regarding classic ASP, hence me thinking this is the best place to ask? I'll ask over at the .net forum though.

    Cheers,

    Craig

  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 06:08 AM|HCamper|LINK

    Hello,

    If you search at the Classic Asp Questions and replies in the Forum you will

     find that majority are referered to this site http://www.aspfaq.com/default.asp as a resolution look at the age of the site. 

    The site has a download section contains an off line copy of the web site code / reference as a zip or pdf.

    So if you want to save time just get the reference and check the coding.

    The reason for the refereral to Forurm at ASP.NET  it does more interactive coding.

    General  FYI.

    Thank You,

    Martin :)

    Windows and Linux work Together IT-Pros
    Community Member Award 2011
  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 06:33 AM|untiedshoes|LINK

    Hi Martin,

    Yeah I looked over there, but couldn't find anything really. Most of the stuff around is for 2 tier systems.

    I've been using 2 & 3 tier for years (datashaping), but for this project, it needs to be infinite and there's an extreme lack of resources for this type is system.

    Cheers,

    Craig :)

  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 08:39 AM|tomkmvp|LINK

    Hi Craig,

    You're in the right place.

    What you are trying to write is called a recursive function.  This might be helpful:

    http://www.google.com/search?q=breadcrumb+recursive+function

     

  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 11:21 AM|untiedshoes|LINK

     Hi Tom,

     I've manage to sort it, though not the best way of doing it I guess? Basically I have set up another Sub routine, based upon the Parent_Region_ID passed to it.

    Then I place a call to the database for the record matching that ID, and pass through the sub again. Unfortunately this returned the results in the wrong order, so I placed them into an Array, and then revered the array. At the moment it runs very quickly, though I only have a few records, guessing it's not going to be as efficient once the data grows?

    Here's what I've got.

    DIM oConn, sSQL
        DIM oRS
        DIM g_arrRows
        DIM g_nCount
        DIM nIndex
       
    CONST REGION_ID = 0
    CONST REGION_NAME = 1
    CONST POPULATION = 2
    CONST PARENT_REGION_ID = 3

    Set oRS = cwOpenQuery("SELECT * FROM Region ORDER BY RegionName",datasource)


    Response.Write "<PRE>"
       
    g_arrRows = oRS.GetRows()
    g_nCount = oRS.RecordCount
    oRS.Close
       
    '---------------------------------------------------------------------------
      
    ' Find the top-level entities in the hierarchy
    For nIndex = 0 To g_nCount - 1
    ' If the Parent is NULL, we have a top level
          If IsNull(g_arrRows(PARENT_REGION_ID, nIndex)) Then
            ' Start looking for children
                   PrintRow g_arrRows(REGION_ID, nIndex), g_arrRows(REGION_NAME, nIndex), g_arrRows( POPULATION, nIndex ), 0, 0
          End If
    Next

    '---------------------------------------------------------------------------
       
    Sub PrintRow(nRegionID, sRegionName, nPopulation, nLevel, sParentID )
    DIM nIndex

            'Write out the current level
            Response.Write String(nLevel, " ") & sRegionName & ": BreadCrumb > "
                Call ParentDetails(sParentID) 
            Response.Write("<br />")   
            For nIndex = 0 To g_nCount - 1
            ' IF the row we're looking at has a parent of the region passed in, then delve into its children.
                If g_arrRows(PARENT_REGION_ID, nIndex) = nRegionID Then
                ' Call our PrintRow function to find the children of this record and so on.
                    PrintRow g_arrRows(REGION_ID, nIndex), g_arrRows(REGION_NAME, nIndex), g_arrRows( POPULATION, nIndex ), nLevel + 1, g_arrRows(PARENT_REGION_ID, nIndex)
          End If
            Next

    End Sub

    'Here we call the Sub to produce the breadcrumbs
    Sub ParentDetails(nRegionID)
    Redim PRESERVE myAccts(i)
    If IsNull(nRegionID) Then
    Exit Sub
    Else

            Set oRS2 = cwOpenQuery("SELECT RegionName, ParentRegionID FROM Region Where RegionID = "& nRegionID &" ORDER BY RegionName",datasource)
               
                If Not oRS2.EOF then
                        myAccts(i) = ""& oRS2("RegionName") &"/"
                        Redim PRESERVE myAccts(i)
                        ParentDetails oRS2("ParentRegionID")
                       
                    End IF
            cwCloseRecordset(oRS2)
               
                Dim articles()
                    ubnd = UBound(myAccts)
                    Redim articles(ubnd)
                        for i = 0 to ubnd
                            articles(ubnd - i) = myAccts(i)
                        next
           
                    for x=0 to ubound(articles)
                        response.Write(articles(x))
                    next
           
    End If

    End Sub

     

    Ideally I shouldn't be re-calling the database, but ideally filtering the  g_arrRows, and using that instead, but not sure how?

    As said, it works, but not the ideal solution?

    Cheers,

    Craig

  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 11:27 AM|HCamper|LINK

    Hello,

    Just a suggestion for this results set " Unfortunately this returned the results in the wrong order"

    how about using an order by only when needed?

    Consider the Server do all the work in this case and not delay the page.

    Martin :)

     

     

    Windows and Linux work Together IT-Pros
    Community Member Award 2011
  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 11:28 AM|untiedshoes|LINK

    *Edit

    should have said "reversed". Sorry 

  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 11:33 AM|untiedshoes|LINK

    Hi Martin,

    It's basically because I'm itterating backwards.

    Say for instance I have this.

    UK
         Tyne and Wear
              Sunderland
                   Ashbrooke

    For each line, I pass it's parent ID to a function, then within that function, I get the parent ID of the record and so on (hence working backwards) to get the required results. But because I working backwards, the array was build up backwards.

    This is why I had to at the end of the function, when it couldn't go any further backwards i.e. it had got to the top level parent, it would reverse the order of the array, therefore formatting it correctly.

    Here's a url to an example: http://www.untiedshoes.co.uk/infinite.asp

  • Re: Infinite parent/child hierarchy problem

    Feb 21, 2011 02:26 PM|HCamper|LINK

    Hi,

    Ok. Thank for what is going on.

    Then from  the what I see you the information working.

    I did notice the images were missing.

    For curiosity sake what helped you resolve the coding.

    Thanks,

    Martin :)

     

    Windows and Linux work Together IT-Pros
    Community Member Award 2011