Who's your Papa - Sorting question

[Rick Gerard]

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.

[Rick Gerard]

Ok, after poking around all the PHP resources I could look up in the last 3 hours I came up with this code that seems to be working.

<?php
$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 relationships that go 10 generations 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.

[bregent]

Rick, the only way to display hierarchical data like this is with a procedure language like php similar to what you are doing - a simple SQL solution won't work. As there is potential for a lot of recursion, this could lead to performance problems. Rather than using php to solve this, I would suggest using a stored procedure which would reduce all of the round trip traffic. Here's one example:

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

[Rick Gerard]

I looked at that page doing my research but couldn't get it to work for me as quickly as the solution I posted. Thanks for the link. There's so much more to learn that I wanted to get into but nothing out of the box does what I need to have done.