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

[rgEffects]

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.

[Prime8]

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.

[rgEffects]

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.

[idalatob]

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/

[rgEffects]

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.