Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts

    Who's your mamma.... Sorting question.

    I'm not sure I'm going about this in the right way so any suggestions would be appreciated.

    I've got a table that stores parent child relationships. The table looks like this:

    primaryID, parentID, Name

    I've set up a form that inserts a 0 in the parent ID if there is no parent, but if you've already created a parent you can choose the parent name and the parent's primary ID is placed in the Parent ID column. Think of it as a family tree.

    If the family tree was Adam as the first parent Cane as the first child of Adam and Able as the second child of Adam but we also had a first parent (not related to Bob, and a second first parent of John, not related to Adam or Bob and we added a first child of John named Dave the data would look like this:

    1, 0, Adam
    2, 1, Cane
    3, 1, Able
    3, 0, Bob
    4, 0, John
    5, 4, Dave

    Adding a child Jill to Cane would give us record ID 6 like this:
    6, 2, Jill

    What I'd like to do is to generate a sorted list from this data that looks like this:

    • Adam
      • Cane
        • Jill
      • Able
    • Bob
    • John
      • Dave


    If anyone has any ideas of how to create a sort that will accomplish this I'd love to see it. I've spent the last 2 hours trying to do this with 1 query. The only way I can make it happen is with 3 and it's a real mess.

  • #2
    New Coder
    Join Date
    Oct 2010
    Posts
    56
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I'm a newb, so feel free to ignore me but I think as you add data this will get more and more complex. My guess is a reference to the parent in the child's data set would make this easier.

    edit: something like this:

    1,0,Adam,0
    2,1,Cane,1
    3,1,Able,1
    4,0,Bob,0
    5,0,John,0
    6,1,Dave,5
    7,1,Jill,2

    edit2: I misread what you meant. You already have a reference to the parent. I thought you were saying you had an field for whether the entry was a child or not.
    Last edited by Prime8; 12-11-2012 at 12:04 AM.

  • #3
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    Ok, after poking around all the PHP resources I came up with this code that seems to be working.
    PHP Code:
    $parentid 0// assuming that 0 is the main category.
        
    get_sub_cats($parentid);
            function 
    get_sub_cats($parentid) {
            
            
    $sql "SELECT * FROM myTable WHERE parentID = ".$parentid.""
            
    $run mysql_query($sql);
            
            echo 
    '<ul>';
                while (
    $rec mysql_fetch_assoc($run)) { 
                echo 
    '<li />'$rec['Name']; 
                
    get_sub_cats($rec['id']);
                }
        echo 
    '</ul>';
            } 
    Now I'm wondering if this is the most efficient way to do this. This code is so simple that I'm afraid that I'm creating a repeating query that's going to slow down when the file count goes way up. The parent child list that I am building will grow to thousands of parent child lists that go 10 generations deep or more.

    If anyone has an idea of how to more efficiently do this I'd love to hear about it. For now this gets another page done and I'm one more day toward a paycheck.

  • #4
    Regular Coder
    Join Date
    Sep 2007
    Location
    Grahamstown, South Africa
    Posts
    237
    Thanks
    6
    Thanked 17 Times in 17 Posts
    Maybe a little complex. Absolutely no insult to you of course.

    But this link here may apply to what you are trying to do:

    http://mikehillyer.com/articles/mana...data-in-mysql/

  • #5
    Regular Coder
    Join Date
    Aug 2012
    Posts
    142
    Thanks
    39
    Thanked 3 Times in 3 Posts
    That's one of the sites I researched. Their techniques actually helped. My only concern with the script that I have is that it may slow down as the number of records increases. I can't quite figure out how the loop works. It generates beautiful lists. It seems to work very fast, but I only have about 50 records to test it with.

    Thanks again.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •